Solved: Email validation

[ganesh3084]

Im doing a macro in excel where i have a column which consists of email id.
Using the below code i validated whether the given Email id is valid one.

With CreateObject("vbscript.regexp")
' .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
' .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
If .test(Range("C" & Transaction).Value) Then

Else
Range("C" & Transaction).Value = "N.A"
Cancel = True
End If
End With

How ever its working working in the following email id
abc@gmail.com.com.

what needs to be done for the above mentioned issue. Plz assist ASAP.

[hansup]

I modified your code as below and ran it in Access. It replied "found match". So I don't understand your question.

If this operates differently under Excel, you should post it on the Excel forum.

Public Sub foo20110609a()
Const cstrEmail As String = "abc@gmail.com.com"
With CreateObject("vbscript.regexp")
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
If .test(cstrEmail) Then
Debug.Print "found match"
Else
Debug.Print "no match"
End If
End With
End Sub

[HiTechCoach]

I modified your code as below and ran it in Access. It replied "found match". So I don't understand your question.

If this operates differently under Excel, you should post it on the Excel forum.

Public Sub foo20110609a()
Const cstrEmail As String = "abc@gmail.com.com"
With CreateObject("vbscript.regexp")
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
If .test(cstrEmail) Then
Debug.Print "found match"
Else
Debug.Print "no match"
End If
End With
End Sub

hansup,

The issue is the email is not valid since it ends with .com.com so the desired result would be "no match".

[HiTechCoach]

Here is how you can do it in Access

Validation with Regular Expressions (http://access.mvps.org/access/modules/mdl0063.htm)

[hansup]

The issue is the email is not valid since it ends with .com.com so the desired result would be "no match".

Please say more about this point, Coach.

com.com is registered to CBS Interactive, Inc. They also own cnet.com, and the registered name servers for com.com are on cnet.com

I don't understand why com.com is not valid in an email address.

Regards,
Hans

[HiTechCoach]

ganesh3084 will have to answer why they think it is not valid.

Is gmail.com.com (abc@gmail.com.com). valid?