Custom Search

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,3})$"

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, "" and "" are all valid email addresses.

Email address pattern ^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$ was taken 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!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 3)
14.How to return multiple values from a VBA function (Part 2)
15.How to return multiple values from a VBA function (Part 1)
16.Three ways to programmatically duplicate a table in MS Access by VBA
17.Create a DLL by CSharp or VB.Net for VBA
18.How to correctly reference and call a DLL
19.How to register a C# or VB.Net DLL
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy