In this article you will learn how to validate emails using Excel. At the end of the post you will see a sample Excel file so that you can use Excel email validation in your projects.
Excel is the most popular spreadsheet in the world, every day millions of people use it to make their calculations, organize data, exploit them… emails are an important part of data processing, so checking them in Excel makes the work much easier.
Why we should validate our emails
Email validation is key to improving the deliverability of email marketing campaigns. Using a validated mailing list reduces the number of failed deliveries by removing from the list obsolete email addresses that are no longer valid or have become spam trap mails.
How syntax validation works in Excel
Syntactic validation is the first step in validating an email address. It consists of checking that the email address is well formed according to the international RFC.
Roughly speaking, syntactic validation checks that the email has a prefix in front of the @ containing alphanumeric characters or ! # $ % & ‘ * + – / = ? ^ _ ` . { | } ~ with a maximum length of 64 characters and the domain name must not exceed 63 characters.
Limitations of email syntactic validation
The syntax validation of email addresses, although it is a good start to know if an email address is valid, only performs the syntax check, it does not tell us if the domain is active. Whether the remote server accepts mail or it is a spam trap mail.
In order to validate an email address we need to use a professional email verification service such as verificaremails.com.
Verify Emails allows you to validate email addresses using CSV and Excel files, uploading them to the Web platform and launching the verification process. You have more details about the service in the article How to verify an email using a file.
How to create a free VerifyEmails account to validate emails using Excel
In order to use the email verification service, you must create an account at verificaremails.com. Registration is free and you have 100 free email verifications to test the service.
Validations can be performed using the Web application by uploading the files or by using the real-time email verification API.
How to obtain email validation API credentials
In order to validate emails using Excel you must have the API credentials. For each email you have in your Excel file, we will make a call to the API, where we will pass the email address we want to validate along with the API token.
Once validated on the email verification platform, you must go to the “API” menu section. Select “New API” to create the API. Enter the API name and select “Create”.
Below you will see the API section, the calls made so far and most importantly the credentials under the “API Key” section. Code examples are available in different languages such as Python, Ruby, PHP or C#.
How to use an email verification service to validate emails using Excel
You can use Excel and the “Excel Email Verification” file to perform Excel verification of your emails. Once you have obtained the API key, you must place it in the “Verify Emails API KEY” box. It is important that this key is correct and that you have credits on your VerifyEmails platform. You can see the available credits at the bottom right of the screen once you have logged in.
To verify the emails we must copy the email addresses to be verified in column “A” of the Excel file. Column “B” performs a syntactic check while column “C” performs the check with VerifyEmails.
To perform the syntax check you must select the “Free syntax check” button. Remember that this verification is free of charge, but only verifies that the emails are syntactically well formed. To perform an in-depth verification of your emails, you must select “Verify Emails Professional – verificaremails.com”.
We can compare between the 2 verification methods. The one in column “B” only performs syntactic validation or the one in column “C” using the verificaemails.com service.
To interpret the results we attach the following email validation table. We remind you that the only secure addresses are “OK”.
Status | Description |
---|---|
ok | Everything is OK. The address is correct and ready to receive e-mails |
error | The server indicates that delivery has failed, but there is no information about the mail. |
smtp_error | The SMTP has responded from the server as invalid or the destination server has reported an internal error. |
smtp_protocol | The destination server has allowed the connection but the SMTP has closed the session before verifying the mail. |
unknown_email | The server replied that the message delivery failed and that the email address does not exist. |
attempt_rejected | The delivery has failed; the reason is similar to that of “rejected”. |
relay_error | Delivery failed due to relay failure |
antispam_system | Some kind of anti-spam filter is blocking the verification process. |
email_disabled | The email account is suspended, disabled, or limited, and you are unable to receive email. |
domain_error | There is no mail server for this domain. No mail can be delivered |
ok_for_all | The mail server responds that it can accept any email address associated with the domain. |
dead_server | The mail server is dead and no connection can be established. |
syntax_error | There is a syntactic error in the email address |
unknown | Mail delivery failed but no reason given |
accept_all | The server is configured to accept any mail, the domain accepts any associated mail. |
disposable | The mail server is configured to accept mails only for a certain period of time. |
spam_traps | The email address is maintained by an IPS, which does not select or open emails. |
The prefix “t” means “temporary”, “p_” means “permanent”. The prefixes are useful because they provide additional information situation: “p_email_disabled” – means that the server responds that the email is permanently disabled “t_attempt_rejected” can happen if temporarily the mail server is overloaded, and rejects checks “p_error” – no information about the email, but no possibility to deliver the mail in the future “terror” – no information about the existence of the mail, but there is a possibility that it is active.
Conclusions on how to validate emails using excel
Analyzing the results, you will clearly see the difference between the 2 methods. Syntactic validation allows us to know if an email address is well formed, but if we need a professional email verification system and to eliminate spam traps we need to use the API of verificaremails.com.
For those of you who do not want to upload your email addresses to an external service and use our web application, please note that our service for validating emails using Excel is GDPR ready.
The validation of emails using Excel is done in real time so we do not store any email address, you can rest assured about the total privacy of your data and no transfer to any external server.