Andre,

This should do what you requested.

Option Explicit
Sub CheckEmailValidity()
Dim MyRange As Range, eaddy As Range
Dim flag As Boolean, dltemail As Variant
Dim updatenow As String
Set MyRange = Selection
For Each eaddy In MyRange
rerun:
If eaddy = "" Then
flag = False
ElseIf InStr(1, eaddy, "@") = 0 Then flag = True
ElseIf InStr(1, eaddy, ".") = 0 Then flag = True
ElseIf InStr(1, eaddy, "@") < 2 Then flag = True
ElseIf InStrRev(eaddy, ".") < 2 Then flag = True
ElseIf InStrRev(eaddy, "@") = InStrRev(eaddy, ".") - 1 Then flag = True
ElseIf InStrRev(eaddy, ".") = Len(eaddy) Then flag = True
Else: flag = False
End If
If flag Then
dltemail = MsgBox("The email address " & eaddy & " is invalid. Would
you like to modify it?", _
vbYesNoCancel, "Invalid Email Address")
If dltemail = vbYes Then
updatenow = MsgBox("Would you like to update this address? No will
clear the email address", vbYesNo, "Update Email")
If updatenow = vbYes Then
eaddy.Value = InputBox("Please enter a valid email address", "Update
Email", eaddy.Value)
GoTo rerun
End If
eaddy.ClearContents
End If
If dltemail = vbCancel Then Exit Sub
End If
Next eaddy
End Sub


Mike

"atr000" wrote:


Mike,

This code works perfectly. I can't tell you how many folks in
research/sales will be happy when we send out emails to large numbers
(I don't work for a spammer). I am wondering if these two things could
be added to the working code:

1) If the entry is blank (we have many w/o emails) can it just be
skipped?
2) Can there be an option to Delete, like there is, and and add in an
option to "Go to that Cell", like an edit option?

Thanks,
Andre


--
atr000
------------------------------------------------------------------------
atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
View this thread: http://www.excelforum.com/showthread...hreadid=562542