Excel VBA to verify if cell value is an email address

Posted on 2011-05-01
Last Modified: 2012-06-27
Is there an easy code to check if the value in a cell, for example A1 is an email address, if yes proceed with the code, if no display a MsgBox("No valid email in A1") and exit?
Question by:BrdgBldr
    LVL 6

    Accepted Solution

    Yes, take a look at the code in this sheet.

    It creates a function, that you can use in the sheet to verify if a string is an email or not.


    LVL 12

    Expert Comment


    You could use VBA, but why not just use data validation?  It is already built in and easier to understand for most users.

    Just go to A1, and bring up the data validation options (Data - Data Validation), and enforce that the cell must contain an '@' sign.

    You could get more complex than that, but most email address checking, just checks for that symbol.


    Author Comment


    your solution is exactly what I was looking for.

    small problem:

    when I add a Sub to you module like this, it works:
    Sub ValidateEmailAddressInMailTo()
    If ValidateEmailAddress(Range("A2").Value) = False Then
        MsgBox ("Invalid Email. Exit now.")
        Exit Sub
    End If
    End Sub

    Open in new window

    However, if I try to use the function from a Sub/Code in another module in the same sheet with this code (Module and Function were both renamed to ValidateEmailAddress):

       Dim MailToAddress As String
        MailToAddress = Range("MailTo").Value
       Dim MailCcAddress As String
        MailCcAddress = Range("MailCc").Value
       Dim MailSubject As String
        MailSubject = Range("MailSubject").Value
       Dim Attachment1 As String
        Attachment1 = Range("AttachmentPdfPath").Value
       'validate MailTo Email Address
       If ValidateEmailAddress.ValidateEmailAddress(MailToAddress) = False Then
        MsgBox ("Invalid MailTo Email address. Code will exit now without sending email.")
        Exit Sub
       End If

    Open in new window

    I get the error message:

    Compile error:
    User-defined type not defined

    objRegExp As New RegExp is selected blue

    after hitting "OK" on the error msg box

    Public Function ValidateEmailAddress(ByVal strEmailAddress As String) As Boolean

    is selected yellow.

    Any idea how to fix it to suit my purpose?
    LVL 6

    Assisted Solution

    You need to add a reference to regular expressions, did you do that? if not do this:

    In the Visual Basic windows choose Tools -> References
    Then set a mark in "Microsoft VBScript Regular Expressions 5.5".

    Does that help?

    Author Closing Comment

    Very helpful and efficient. Thanks a lot.
    LVL 6

    Expert Comment

    Your welcome :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now