I searched for email validate and after seeing how long some of the source vba codes were, I found a regular expression that was
well rated for most things at
http://blog.trojanhunter.com/2012/09...-email-address
And based on some previous work with reg expressions, I created this function to validate email based on the pattern I found.
The function takes in a string - the email address to be tested. It returns True if pattern matches; it returns False if it does not.
You could use this to see if an email has proper format. For your logic if you find multiple emails in one field, you will have to parse
each email address and test it for valid or not.
NOTE: You must set a reference to Microsoft VBScript Regular Expressions 5.5
You could set up the function and test various emails to get some feel for the "completeness" of this pattern.
I hope it helps.
Code:
'---------------------------------------------------------------------------------------
' Procedure : IsValidEmail
' Author : Jack
' Date : 28/01/2014
' Purpose : To create a function to validate an email syntax.
'
' ---------------------------------------
'*** Requires a reference to the Microsoft VBScript Regular Expressions library
' _______________________________________
'
' Found "best regex pattern for email validation" at
' http://blog.trojanhunter.com/2012/09/26/the-best-regex-to-validate-an-email-address
'
' Pattern is [-0-9a-zA-Z.+_]+@[-0-9a-zA-Z.+_]+\.[a-zA-Z]{2,4}
'How it works
' Function takes in an email address as string
' Using regex pattern matching
' If string matches valid email pattern -->return True
' If string fails valid email pattern -->return False
'---------------------------------------------------------------------------------------
'
Function IsValidEmail(T As String) As Boolean
10 On Error GoTo IsValidEmail_Error
20 IsValidEmail = False 'preset to False
Dim re As New Regexp
'set the pattern
30 re.Pattern = "[-0-9a-zA-Z.+_]+@[-0-9a-zA-Z.+_]+\.[a-zA-Z]{2,4}"
40 re.Global = True
50 If (re.Test(T) = True) Then
60 IsValidEmail = True
70 Else
80 IsValidEmail = False
90 End If
100 On Error GoTo 0
110 Exit Function
IsValidEmail_Error:
120 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure IsValidEmail of Module Module1"
End Function
Here is some test output in immediate window
?IsValidEmail("jackpat.d@gmail.net.au")
True
?IsValidEmail("jackpat.d?gmail.net.au")
False
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
Originally Posted by orange
