• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

Excel VBA to verify if cell value is an email address

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?
0
BrdgBldr
Asked:
BrdgBldr
  • 3
  • 2
2 Solutions
 
scifo_dkCommented:
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.

//Scifo_dk

validate-email.xlsm.txt
0
 
AlanConsultantCommented:
Hi,

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.

Alan.
0
 
BrdgBldrAuthor Commented:
@scifo_dk

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?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
scifo_dkCommented:
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?
0
 
BrdgBldrAuthor Commented:
Very helpful and efficient. Thanks a lot.
0
 
scifo_dkCommented:
Your welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now