Validate Email Address – VBA Code – Excel

Verify Email Address Format in VBA

Code in this page will verify if a email id is in proper format.

This code uses regex pattern matching. To do this, first You need to define a regular expression pattern that will match a valid email id.

A email id has a name, domain name & gTLD (like: .com , .org, .net etc.,)

Example: abc@officetricks.com

Here is the vba code that does the validation of email address.

Function IsValidEmail(sEmailAddress As String) As Boolean
    'Code from Officetricks
    'Define variables
    Dim sEmailPattern As String
    Dim oRegEx As Object
    Dim bReturn As Boolean
    
    'Use the below regular expressions
    sEmailPattern = "^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$" 'or
    sEmailPattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
    
    'Create Regular Expression Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    oRegEx.Global = True
    oRegEx.IgnoreCase = True
    oRegEx.Pattern = sEmailPattern
    bReturn = False
    
    'Check if Email match regex pattern
    If oRegEx.Test(sEmailAddress) Then
        'Debug.Print "Valid Email ('" & sEmailAddress & "')"
        bReturn = True
    Else
        'Debug.Print "Invalid Email('" & sEmailAddress & "')"
        bReturn = False
    End If

    'Return validation result
    IsValidEmail = bReturn
End Function

The above function can also be used a UDF (User Defined Function). Which means that you can use this inside the Excel worksheet as well.

Example: Place this formula in a Worksheet.

• =IsValidEmail(“kumarapush123@gmail.com”) will return True
• =IsValidEmail(“@gmail.com”) will return False

There are 2 regular expression patterns mentioned in the code. You can use one of the 2. or even check if a email address passes both validation to get concrete results.

External Reference:

1. geeksforgeeks.org on email address validation using python programming

2. geeksengine.com for another vba email validation code with regex