Solved

Valid email addresses

Posted on 2016-11-10
6
21 Views
Last Modified: 2016-11-10
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
Comment
Question by:Andy Brown
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41882012
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
 

Author Closing Comment

by:Andy Brown
ID: 41882013
Fantastic - it does the trick perfectly.

Thank you.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41882019
As a commentary nowadays the number is no more limited to 4 but I think 255. you could have .university or .international
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Andy Brown
ID: 41882024
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41882192
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
 

Author Comment

by:Andy Brown
ID: 41882257
Great suggestion - thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now