Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Verify valid email address syntax (2007)

    Hello there,

    I’m looking for a way to verify if a cell containing more than one email address contains the proper characters.

    Some cells will contain only one email address while others contain more than one. The ones that do contain more than one require a semicolon to separate the email addresses. So what I’m trying to do is look at the cells containing more than one email address, and verifying that a semicolon is present.

    I can get it to partially work with the following code:

    <hr>Sub Check()
    For Each cell In Sheets("Sheet1").Range("B2:B200").Cells.SpecialCel ls(xlCellTypeConstants)
    If cell.value Like "*@*@*" And cell.value Like "*;*" Then
    Else
    MsgBox ("Invalid Email Address in cell ") & cell.Address(False, False) & vbCrLf & "Cells with multiple Email addresses must contain a semicolon to be valid."
    Range(cell.Address).Select
    Exit Sub
    End If
    Next cell
    Exit Sub <hr>


    What trips me up is when there is a cell containing only one email address.

    Is this possible? Am I on the right track?

    What I’d like to do is be able to count the number of email addresses by counting the @ and then making sure there is one less semicolon than email addresses.

    Thanks in advance for the pointers.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Verify valid email address syntax (2007)

    Attached is a Non VBA solution to your problem.
    It assumes you have either one or two e-mails per line. if there could be more you would need to modify the formulas.
    Hope this helps.

    Regards,
    Tom Duthie
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Verify valid email address syntax (2007)

    Try the code in the attached text file.

    For a more comprehensive check, see VBA Express : Excel - Validating an email.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Verify valid email address syntax (2007)

    Thanks for the code. It works fabulously.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •