Excel VBA to verify if cell value is an email address

Posted on 2011-05-01
Medium Priority
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
  • 3
  • 2

Accepted Solution

scifo_dk earned 1000 total points
ID: 35500622
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 21

Expert Comment

ID: 35500629

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

ID: 35500741

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?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Assisted Solution

scifo_dk earned 1000 total points
ID: 35500766
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

ID: 35500837
Very helpful and efficient. Thanks a lot.

Expert Comment

ID: 35500848
Your welcome :)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

850 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