Automate Email Validation in Excel using VBA and MailTester.Ninja API

Are you tired of manually verifying email addresses in your Excel spreadsheets? You’re not alone. Many professionals struggle with this time-consuming task, but there’s a better way. In this article, we’ll show you how to automate email validation using VBA (Visual Basic for Applications) and the MailTester.Ninja API.

Why Manual Email Verification is Time-Consuming

Email verification is crucial to maintain clean and accurate contact lists. However, manually checking each email address is not only tedious but also prone to human error.

CelTools automates many repetitive Excel tasks like this, making it a powerful alternative for frequent users: Learn more about CelTools

Step-by-Step Solution: Automating Email Verification in Excel

Let’s walk through the process of setting up an automated email verification system using VBA and MailTester.Ninja API.

1. Set Up Your Excel Worksheet

Create a worksheet with two columns: one for your email addresses and another to display the validation results.

2. Get Your API Key from MailTester.Ninja

Sign up on MailTester.Ninja, get an API key, and keep it handy.

3. Write the VBA Code to Connect with MailTester.Ninja API

Open Excel’s Visual Basic for Applications editor (press `Alt + F11`), insert a new module, and paste this code:

Function ValidateEmail(email As String) As String
    Dim http As Object
    Dim url As String
    Dim apiKey As String

    ' Your MailTester.Ninja API key
    apiKey = "YOUR_API_KEY_HERE"

    ' Construct the URL for the API request
    url = "https://api.mailtester.ninja/validate?email=" & email & "&apikey=" & apiKey

    ' Create HTTP object to make the request
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.send

    ' Parse the JSON response (assuming it's in a simple format)
    If InStr(http.responseText, "\"status\":\"valid\"") Then
        ValidateEmail = "Valid"
    ElseIf InStr(http.responseText, "\"status\":\"invalid\"") Then
        ValidateEmail = "Invalid"
    Else
        ValidateEmail = "Error"
    End If

End Function

4. Use the VBA Function in Your Worksheet

Now you can use this function directly in your Excel cells:

=ValidateEmail(A2)

This formula will check if the email in cell A2 is valid or invalid based on the MailTester.Ninja API response.

5. Automate the Process for Entire Column

If you have a list of emails, use this VBA snippet to validate all of them:

Sub ValidateAllEmails()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Set worksheet and find the last row with data in column A
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Loop through each email address and validate it
    For i = 2 To lastRow
        ws.Cells(i, 2).Value = ValidateEmail(ws.Cells(i, 1).Value)
    Next i
End Sub

Run this macro to populate the second column with validation results for all emails in the first column.

6. Advanced: Handling JSON Responses with VBA

For more complex API responses (like nested JSON), you might need a library like VBA-JSON. Here’s how to parse the response:

Function ValidateEmailWithJSON(email As String) As String
    Dim http As Object
    Dim url As String
    Dim apiKey As String

    ' Your MailTester.Ninja API key
    apiKey = "YOUR_API_KEY_HERE"

    ' Construct the URL for the API request
    url = "https://api.mailtester.ninja/validate?email=" & email & "&apikey=" & apiKey

    ' Create HTTP object to make the request
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.send

    Dim json As Object
    Set json = JsonConverter.ParseJson(http.responseText)

    If Not json Is Nothing Then
        Select Case LCase(json("status"))
            Case "valid"
                ValidateEmailWithJSON = "Valid"
            Case "invalid"
                ValidateEmailWithJSON = "Invalid"
            Case Else
                ValidateEmailWithJSON = "Error: " & json("message")
        End Select
    Else
        ValidateEmailWithJSON = "Error: Invalid response"
    End If

End Function

7. Using CelTools for Enhanced Capabilities

CelTools adds advanced features to Excel, making tasks like email validation even simpler and more powerful.

Common Mistakes When Validating Emails in Excel

• Not using an API key: Always ensure you have your MailTester.Ninja API key set up correctly.
• Ignoring JSON parsing errors: Make sure to handle potential errors when working with complex JSON responses.
• Overlooking rate limits: Be aware of the API’s request limits and avoid making too many calls in a short period.

Technical Summary: Combining Manual Techniques with Specialized Tools

The combination of manual VBA coding techniques and specialized tools like MailTester.Ninja creates an efficient, automated email validation system. By writing custom functions in Excel’s VBA environment, you can connect to external APIs for real-time data validation.

While CelTools offers enhanced features for repetitive tasks, mastering the core techniques of API integration and automation gives you control over your workflows.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical