Email address validation by Regular Expressions using VBA



If your application in MS Access or Excel needs to deal with email addresses, you may need a way to validate them. These email addresses can be input either by users of these applications or by import from other sources.

The best way to validate email addresses is to use Regular Expressions because it matches patterns for valid email address format.

To use Regular Expressions in VBA, you first need to add a reference in the VBA code editor.

Open your Access or Excel file, then open VBA editor. Depends on your version of Microsoft Office, there are different ways to open VBA code editor.

When VBA code editor is opened, on the menu bar across the top, click Tools -> References...

Then in the list of Available References, find Microsoft VBScript Regular Expressions 5.5 and then check the box on the left.

Microsoft VBScript Regular Expressions is part of the core set of DLLs that make up Microsoft Office VBA environment.

Below is the email address validation function I used in a mass mailer application I created in MS Access. So far it has been used to validate more than 200,000 email addresses and worked really well.

Option Explicit

Const MODULE_NAME As String = "modMail"

'' Validate email address
Public Function ValidateEmailAddress(ByVal strEmailAddress As String) As Boolean
    On Error GoTo Catch
    
    Dim objRegExp As New RegExp
    Dim blnIsValidEmail As Boolean
    
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    objRegExp.Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,20})$"
    
    blnIsValidEmail = objRegExp.Test(strEmailAddress)
    ValidateEmailAddress = blnIsValidEmail
      
    Exit Function
    
Catch:
    ValidateEmailAddress = False
    MsgBox "Module: " & MODULE_NAME & " - ValidateEmailAddress function" & vbCrLf & vbCrLf _
		& "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

In the code above, we first create an object objRegExp by using Regular Expressions library - RegExp. We then defined some properties for this object. We set IgnoreCase = True because for validation purpose, uppercase or lowercase does not any difference for email addresses. Even though the local-part of an email address can be case-sensitive (according to RFC 5321), it's only for email delivering purpose. For email validation purpose, "jsmith@example.com" and "JSmith@example.com" are all valid email addresses.

Email address pattern ^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,20})$ was modified from this page. There are a lot of this kind of patterns on Regular Expression Library website, or you may search the net to find a pattern that you think is most suitable to your situation.

Happy Coding!


Copyright© GeeksEngine.com

Other Recent Articles from the MS Access category: 1. Examples of MS Access DateDiff function used in query and VBA code 2. MS Access DateDiff function 3. How to find out your computer name and username by VBA 4. Examples of MS Access DatePart function 5. MS Access DatePart function 6. Examples of MS Access DateAdd function 7. MS Access DateAdd function 8. IIF function basics - the CASE statement of MS Access 9. MS Access Date Expression 10. Solved: MS Access error "The text is too long to be edited" 11. Create MS Access Combo Box essential properties by VBA code 12. Create MS Access Combo Box essential properties manually 13. How to do text search in MS Access programmatically 14. Solved - the size of the Access query result is larger than the maximum size of a database (2 GB) 15. How to easily get a list of field names in MS Access 16. How to count distinct records in MS Access 17. How to do transaction based processing in MS Access 18. How to open a document (local/network file or web page) from MS Access 19. How to use ADOX to create unique composite index - the VBA approach 20. How to do cross-table update queries in MS Access - the right way 21. Three efficient ways to get the number of records by using VBA 22. How to create a composite unique index (not as a primary key) in MS Access 23. Use VBA to get the correct number of records in a Recordset object 24. Disable Access Prompt when a record is changed, table deleted, or action queries run 25. How to hide and unhide a MS Access object 26. How to return multiple values from a VBA function (Part 3) 27. How to return multiple values from a VBA function (Part 2) 28. How to return multiple values from a VBA function (Part 1) 29. Three ways to programmatically duplicate a table in MS Access by VBA 30. Create a DLL by CSharp or VB.Net for VBA 31. How to correctly reference and call a DLL 32. How to register a C# or VB.Net DLL 33. Fix MS Access error: Query must have at least one destination field 34. How to unselect radio buttons in MS Access after it has been selected 35. How to Change Query Timeout Value for MS Access SQL Queries 36. What is Northwind Traders database