Valid email addresses

I have an MS Access database and need to find an accurate way of establishing if an email address is technically correct.  I have found several snippets of code online, with my favourite one is here:

Public Function ValidEmail(pAddress As String) As Boolean 
     '-----------------------------------------------------------------
    Dim oRegEx As Object 
    Set oRegEx = CreateObject("VBScript.RegExp") 
    With oRegEx 
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$" 
        ValidEmail = .Test(pAddress) 
    End With 
    Set oRegEx = Nothing 
End Function 
 
Sub Test() 
    If ValidEmail("me@excel-it.com") Then 
        MsgBox "OK" 
    Else: MsgBox "Check email address" 
    End If 
End Sub 

Open in new window


However, this doesn't take into account anything with four characters in the suffix (.info etc.).  Also, if there are two email addresses within the string it will also return as false (j can get over this last issue easily enough).

Ideally, I just want to find the most up to date/reliable way to return a true/false value.  Or, figure out how to tweak this one to take that into account.

Any suggestions would be greatly appreciated.
Andy BrownDeveloperAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi

to change to 4 for the suffix

pls try
Public Function ValidEmail(pAddress As String) As Boolean 
     '-----------------------------------------------------------------
    Dim oRegEx As Object 
    Set oRegEx = CreateObject("VBScript.RegExp") 
    With oRegEx 
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,4}$" 
        ValidEmail = .Test(pAddress) 
    End With 
    Set oRegEx = Nothing 
End Function 
 
Sub Test() 
    If ValidEmail("me@excel-it.com") Then 
        MsgBox "OK" 
    Else: MsgBox "Check email address" 
    End If 
End Sub 

Open in new window

Regards
0
 
Andy BrownDeveloperAuthor Commented:
Fantastic - it does the trick perfectly.

Thank you.
0
 
Rgonzo1971Commented:
As a commentary nowadays the number is no more limited to 4 but I think 255. you could have .university or .international
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Andy BrownDeveloperAuthor Commented:
You are right.

I have also found this pattern that seems to be quite good:

.Pattern = "^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$"

Ignore case needs to be set to true, but it seems pretty reliable.
0
 
Dale FyeCommented:
You might also consider parsing the pAddress argument using the split command and then loop through the array to check individual elements of the array.  This would allow you to pass multiple values in a single string.  Or alternatively, use a parameter array as the functions argument, so that you can pass in multiple values, rather than concatenating multiple emails into a single string and then passing that value.
1
 
Andy BrownDeveloperAuthor Commented:
Great suggestion - thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.