• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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.
0
Andy Brown
Asked:
Andy Brown
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
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 BrownAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Andy BrownAuthor 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 BrownAuthor Commented:
Great suggestion - thank you.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now