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 -->
References…”
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

NOTE:

                 

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:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

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

Comments

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

wunjoo

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

www.ex-parrot.com/~pdw/Mail-RFC822-Address.html

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.

Robert

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

medicine

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

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

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

Pingback from endyear2012.com

final test pingback from «  End Year 2012

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

trackback

online slots casino

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

pingback

Pingback from jpsoftwaretech.com

Email validation in VBA - JP Software Technologies

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

pingback

Pingback from siteduct.com

Script Installation Service

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

pingback

Pingback from followliker.com

twitter automation software

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

pingback

Pingback from alexatrafficracer.org

Wine Lovers Guide

Comments are closed