Andre,

Had some time to readup on the RegExp object. Thank you Nick for opening my
eyes to this object, I can see it being very usefull.

I integrated Nicks code into mine so that it will work the same way. I also
modified his pattern a bit to be a little more flexible with the email
address matches. To get a better idea of the patterns out there for email
visit http://regexlib.com/ and type "email" in the Keyword search.

As Nick noted, to utilize this code you will need to include a reference to
"Microsoft VBScript Regular Expressions 5.5" in the VB editor. To do this,
click Tools-References. Scroll down the list and put a checkmark in the box
next to the reference name.

Here's the code:

Sub Check2()
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:
flag = RegExpTest(eaddy)
If Not 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
eaddy.Offset(0, 4) = flag
End If
Next eaddy
End Sub
Function RegExpTest(sEmail)
RegExpTest = False
Dim regEx, retVal
Set regEx = New RegExp
' Create regular expression:
regEx.Pattern =
"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z]*[-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,3})$"
' Set pattern:
regEx.IgnoreCase = True
' Set case sensitivity.
retVal = regEx.Test(sEmail)
' Execute the search test.
If Not retVal Then
Exit Function
End If
RegExpTest = True
End Function

I think this is a cleaner version and will accept some off the wall emails,
but catch some glitches that my earlier code would not have.

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