[SOLVED] How to Verify that the contents of the cell is an Email Addy

Hello Scott,

I have included 2 different e-mail validations. The first is very basic. It simply checks that the address contains one or more characters, an "@" symbol, one or more characters followed by a period "." and one or more characters after that.

The second method is based on Function validates an email address as defined in RFC 5321 and RFC 5322 standards using Regular Expressions. The email address must pass six tests before it is considered valid.

However, e-mail systems may also impose further syntax checking. This may lead to an e-mail address being invalid even though it is considered valid by the Internet Standards.

Basic E-mail Address Test

    'Copy the contents of the current cell as the TO address. Needs to be in [email protected] format
    ToAddy = ActiveCell.Value

    'EXAMPLE 1
    If Not ToAddy Like Like "?*@?*.?*" Then
           MsgBox "You entered and Invalid e-mail Address"
           Exit Sub
        End If

    'EXAMPLE 2
    If Not IsEmailAddress(ToAddy) Then
           MsgBox "You entered and Invalid e-mail Address"
           Exit Sub
        End If

UDF to Validate E-mail Address Based on Internet Standards
The email address must pass six tests to be considered valid. Copy this code into a Standard VBA module in your workbook.

'Written: November 09, 2010
'Author:  Leith Ross
'Summary: Function validates an email address as defined in RFC 5321 and
'         RFC 5322 standards using Regular Expressions. The email address
'         must pass six tests before it is considered valid.

Function IsEmailAddress(ByVal Email_Address As String) As Boolean

 'Check if the string conforms to the basic internet Email address standards.
 'The string is an Email address if it has the form of <local>@<domain.name>
 'and both parts have no invalid characters. Each Email system will decide if
 'the address syntax is acceptable or not based on its own internal standards.
 
  Dim DomainName As String
  Dim EmailError As Boolean
  Dim LocalPart As String
  Dim RegExp As Object
  
    Set RegExp = CreateObject("VBScript.RegExp")
      
     'Check if the @ sign missing?
      RegExp.Pattern = "^(.+)\@(.+)$"
        EmailError = Not RegExp.Test(Email_Address)
      
     'Extract the Local part and the Domain Name.
      LocalPart = RegExp.Replace(Email_Address, "$1")
      DomainName = RegExp.Replace(Email_Address, "$2")
            
     'Validate Local part characters.
      RegExp.Pattern = "([^\!\\\""\#\$\%\&\'\*\+\-\/\=\?\^\_\`\~\{\}\.0-9\@A-Za-z]+)"
        EmailError = EmailError Or RegExp.Test(LocalPart)
      
     'Check the Local part doesn't start or end with a period.
      RegExp.Pattern = "^(\..)|(.\.)$"
        EmailError = EmailError Or RegExp.Test(LocalPart)
      
     'Validate Domain name characters.
      RegExp.Pattern = "([^\.\-0-9a-z]+)"
        EmailError = EmailError Or RegExp.Test(DomainName)
      
     'Check the Domain name doesn't start or end with a hyphen.
      RegExp.Pattern = "^(\-.)|(.\-)$"
        EmailError = EmailError Or RegExp.Test(DomainName)
     
    IsEmailAddress = Not EmailError
    
End Function