web 2.0

How to: VBA email validation

This is a follow up to my post on sending email messages using Windows Vista. You can read the previous post here: http://www.msofficegurus.com/post/How-to-Windows-Vista-SMTP-Server-Using-CDOMessage.aspx

In this post, I discuss email validation using VBA. VBA email validation is a very simple process that does not require much, but you will need to install the Microsoft VBScript Regular Expressions 5.5.

In order to install the reference, follow these steps:

1. Open VBE (Alt+F11)
2. Go to “Tools -->
3. Search in the reference list for Microsoft VBScript Regular Expressions 5.5, select it and click OK to continue.

The figure below shows the reference installed:

Figure 1: Microsoft VBScript Regular Expressions 5.5 reference installed



Only today, after receiving a comment on this post, I noticed that I was using Excel in Portuguese and not English. Since I am always changing the language settings, I sometimes forget to change back to the language I am writing on. I will leave the image as it is, as I am sure everyone can get the picture (if you will forgive me the pun).

Added: July 3rd, 2009

Now, you can create a boolean user-defined function to test an email string to check whether it is a “regular expression”. In my case, I check for the following pattern:


There are a number of patterns that you can you and you should be able to find my patterns, especially in sites that teach java or php (check RFC 2822 for info on pattern). You can then borrow one of those patterns and use in your code.

The user-defined function will look like this:

Function ValidateEmail(ByVal sEmail As String) As Boolean
    Dim oRegularExpression     As RegExp
'   Sets the regular expression object
    Set oRegularExpression = New RegExp

    With oRegularExpression
'   Sets the regular expression pattern
        .Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
'   Ignores case
        .IgnoreCase = True

'       Test email string
        ValidateEmail = .Test(sEmail)
    End With
End Function

You can now test the user-defined function with a simple call:

Sub Test_ValidateEmail()
    MsgBox ValidateEmail("my.email@hotmail.com")
    MsgBox ValidateEmail("your.EMAIL@hotmail,com")
End Sub

Tags: , , , ,

Microsoft Excel - VBA | Microsoft Office - VBA | Vista


wunjoo United States, on 7/3/2009 5:12:39 AM Said:


Your expression is imho much too simple. Take a look at this almost complete expression:


Robert Martim United Kingdom, on 7/3/2009 5:22:43 AM Said:

Robert Martim

Man, mine is waaaaaaaaaaaaaay too simple, but that one is waaaaaaaaaaaaaaaaaaay too complex! Smile

However, it is certainly welcome. I will try it later today when I come back home.

Thanks for the comment.


medicine United States, on 7/6/2009 7:58:42 AM Said:


I have never used VBA, but it seems to me that this is a very useful service, because every day I get a lot of mail.

codeforexcelandoutlook.com , on 2/22/2010 7:02:08 AM Said:


Pingback from codeforexcelandoutlook.com

Email validation in VBA » Code For Excel And Outlook

foursquareinnovations.co.uk , on 3/5/2010 11:09:37 AM Said:


Pingback from foursquareinnovations.co.uk

Email validation in VBA «  Microsoft Lessons from the Web

endyear2012.com , on 5/6/2010 1:55:55 AM Said:


Pingback from endyear2012.com

final test pingback from «  End Year 2012

online slots casino , on 3/1/2011 1:42:24 PM Said:


online slots casino

jpsoftwaretech.com , on 1/13/2012 8:16:21 PM Said:


Pingback from jpsoftwaretech.com

Email validation in VBA - JP Software Technologies

siteduct.com , on 12/20/2012 10:40:34 AM Said:


Pingback from siteduct.com

Script Installation Service

followliker.com , on 7/30/2013 6:24:13 AM Said:


Pingback from followliker.com

twitter automation software

alexatrafficracer.org , on 10/5/2013 1:01:48 PM Said:


Pingback from alexatrafficracer.org

Wine Lovers Guide

Comments are closed