Solved

Valid email addresses

Posted on 2016-11-10
6
29 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 49

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 49

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

27 Experts available now in Live!

Get 1:1 Help Now