Verify valid email address syntax (2007)

Topic

New Reply

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:

[indent]

---

Sub Check()
For Each cell In Sheets(“Sheet1”).Range(“B2:B200”).Cells.SpecialCells(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

---

[/indent]

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.

Reply | Quote

Replies

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

Reply | Quote

Try the code in the attached text file.

For a more comprehensive check, see VBA Express : Excel – Validating an email.

Reply | Quote

Thanks for the code. It works fabulously.

Reply | Quote