VBA – Validate E-mail Address

One option is to systematically check for the various dos and don’t using a series of if statements such as that in the function below:

Public Function isValidEmail(inEmailAddress As String) As Boolean 
' Author: Unknown

If (Len(inEmailAddress) = 0) Then 
    MsgBox "Please enter your email address." 
    isValidEmail = False 
    Exit Function 
End If 
If (InStr(1, inEmailAddress, "@") = 0) Then 
    MsgBox "The '@' is missing from your e-mail address." 
    isValidEmail = False 
    Exit Function 
End If 
If (InStr(1, inEmailAddress, ".") = 0) Then 
    MsgBox "The '.' is missing from your e-mail address." 
    isValidEmail = False 
    Exit Function 
End If 

If (InStr(inEmailAddress, "@.") > 0) Then 
    MsgBox "There is nothing between '@' and '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If ((InStr(inEmailAddress, ".")) = ((Len(inEmailAddress)))) Then 
    MsgBox "There has to be something after the '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If ((Len(inEmailAddress)) < (InStr(inEmailAddress, ".") + 2)) Then 
    MsgBox "There should be two letters after the '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If (InStr(1, inEmailAddress, "@") = 1) Then 
    MsgBox "You have to have something before the '@'" 
    isValidEmail = False 
    Exit Function 
End If 

isValidEmail = True 
End Function

based on some comments below, I modified the above slightly.

'---------------------------------------------------------------------------------------
' Procedure : isValidEmail
' Author    : Unknown
' Modified by: Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Validate an e-mail address against some basic formatting rules
'              returns True/False indicating if the e-mail address is properly formatted
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sEmailAddress : E-mail address to validate
'
' Usage:
' ~~~~~~
' isValidEmail("@.") -> False with error message
' isValidEmail("chantal@hotmail.com") -> True
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1
' 2         2017-01-26              Added Error Handler
'                                   Concatenation of error messages into a single msg
'---------------------------------------------------------------------------------------
Public Function isValidEmail(sEmailAddress As String) As Boolean
' Author: Unknown
' Modified by: Daniel Pineault
    On Error GoTo Error_Handler
    Dim sMsg                  As String

    isValidEmail = True

    If (Len(sEmailAddress) = 0) Then
        sMsg = "Please enter your email address."
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, "@") = 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "The '@' is missing from your e-mail address."
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, ".") = 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "The '.' is missing from your e-mail address."
        isValidEmail = False
    End If

    If (InStr(sEmailAddress, "@.") > 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There is nothing between '@' and '.'"
        isValidEmail = False
    End If

    If ((InStr(sEmailAddress, ".")) = ((Len(sEmailAddress)))) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There has to be something after the '.'"
        isValidEmail = False
    End If

    If ((Len(sEmailAddress)) < (InStr(sEmailAddress, ".") + 2)) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There should be two letters after the '.'"
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, "@") = 1) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "You have to have something before the '@'"
        isValidEmail = False
    End If

    'Display the error message if applicable
    If isValidEmail = False Then
        Call MsgBox(sMsg, vbCritical + vbOKOnly, "E-mail Address Syntax Error")
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: isValidEmail" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

A Second better approach, like with many web programming languages, is to use regular expressions to validate certain type of strings. Now Access' VBA does not allow RegEx persey, but you can very easily access such functionality by adding 2 simple lines of code. You then end up with a total of a three lines of code to validate almost any string, including an e-mail address. For all the details, and a link to where you can get a multitude of the RegEx so you don't have to reinvent the wheel simply check out my post VBA – Using Regular Expressions (RegEx) and Validate E-mail Addresses.

8 responses on “VBA – Validate E-mail Address”

    1. Daniel Pineault Post author

      Add an InStr check for a space or better yet use the regular expression check to validate e-mail addresses.

  2. Soumya

    It checks every time a character is typed. how to check the complete string at once and not at every individual input of character?

  3. azer

    thanks i then added this
    If (InStr(1, inEmailAddress, ” “) > 1) Then
    MsgBox “Check for the space in the email”
    isValidEmail = False
    Exit Function
    End If
    maybe the bonus would be to test that the space in not at the end of the email or before, since it might still be a valid email 🙂

  4. azer

    also in the way that the procedure is done with independant IF test , if there are more than one error , the message box will be displayed for the first error , but there will not be message for the other errors exemple ! azer @gmail.c (2 errors , only one displayed)