Invalid Email Id

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Oct 17, 2009

• #2

How about

Code:

Function ValidateEmail(Email As String) As String
    Dim oRegExp As Object
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True
        .Pattern = "^[A-Z0-9._-]+@[A-Z0-9.-]+\.[A-Z]{3}$"
        If .test(Email) Then
            ValidateEmail = "Valid Email"
        Else
            ValidateEmail = "Invalid Email"
        End If
    End With
End Function

image removed

Cell Formulas
Range	Formula
B2	=ValidateEmail(UPPER(A2))

 

Last edited by a moderator: Oct 19, 2009

Upvote 0

hardeep.kanwar

Well-known Member

Joined

Aug 13, 2008

Messages

693

• Oct 18, 2009

• #3

Hi!

Thanks for Reply

I Tried your Code, but in Many Cases it Shows Wrong Result

See Below

image removed

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=ValidateEmail(UPPER(D2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


These All are VALID E-MAIL ID'S

 

Last edited by a moderator: Oct 19, 2009

Upvote 0

hardeep.kanwar

Well-known Member

Joined

Aug 13, 2008

Messages

693

• Oct 18, 2009

• #4

Nobody

Bump!!!!!!!!

 

Upvote 0

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Oct 18, 2009

• #5

Yup... the current code expects only a single "." after the "@"... Lets see if I can modify it to include 2 periods...

 

Upvote 0

sanrv1f

MrExcel MVP

Joined

Jan 1, 2009

Messages

3,474

Office Version

1. 2016

Platform

1. Windows

• Oct 19, 2009

• #6

building on Sandeeps' solution,

Code:

Function ValidateEmail(ByVal Email As String) As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "^[\w-\.]+@\w+\.[a-z]+(\.[a-z]+)?$"
    If .test(LCase(Email)) Then
        ValidateEmail = "Valid Email"
    Else
        ValidateEmail = "Invalid Email"
    End If
End With
End Function

this allows, any alphanumeric, period (.), hypen and underscore in both the user name and the domain name, the suffix of domain name (.com,.co.in) can contain only alphabets

 

Upvote 0

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Oct 19, 2009

• #7

Good one Sankar...

I was tryin out a few combinations... any idea why this didn't work??

Code:

.Pattern = "^[A-Z0-9._-]+@[A-Z0-9.-]+\.[A-Z]{2,3}+\.[A-Z]{3}$"

 

Upvote 0

sanrv1f

MrExcel MVP

Joined

Jan 1, 2009

Messages

3,474

Office Version

1. 2016

Platform

1. Windows

• Oct 19, 2009

• #8

sandeep.warrier said:

Good one Sankar...

I was tryin out a few combinations... any idea why this didn't work??

Code:

.Pattern = "^[A-Z0-9._-]+@[A-Z0-9.-]+\.[A-Z]{2,3}+\.[A-Z]{3}$"

Click to expand...

not very sure, let me try and lets see if any of the masters have a look into this,

while evaluating the pattern in parts,

Code:

[A-Z]{2,3}+

the above gave an error, this is because, the numbers in the curly braces denote the min and max length of the word to be matched, the + sign next would result in an error, as it means, one or more of the previous character,

we can rewrite that part in two ways,

Code:

([A-Z]{2,3})+

this would result in removing the last part of the pattern as it is redundant, the whole pattern would be

Code:

.Pattern = "^[A-Z0-9._-]+@[A-Z0-9-]+(\.[A-Z]{2,3})+$"

the above pattern would match email address removed

if we want to restrict the domain name to have maximum of 3 parts then,

Code:

.Pattern = "^[A-Z0-9._-]+@[A-Z0-9-]+(\.[A-Z]{2,3}){1,2}$"

would do

 

Last edited by a moderator: Oct 19, 2009

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

74,813

Office Version

1. 365

Platform

1. Windows

• Oct 19, 2009

• #9

Hardeep,

Are these "real" emails addresses? If so, we probably want to remove them. Spammer and Spambots routinely patrol public forums like these looking for email addresses. People might become real upset with you if they start getting Spam because you posted their email address!

If so, let me know and I can remove all the images that contain the email addresses.

The Moderators

 

Upvote 0

hardeep.kanwar

Well-known Member

Joined

Aug 13, 2008

Messages

693

• Oct 19, 2009

• #10

Joe4 said:

Hardeep,

Are these "real" emails addresses? If so, we probably want to remove them. Spammer and Spambots routinely patrol public forums like these looking for email addresses. People might become real upset with you if they start getting Spam because you posted their email address!

If so, let me know and I can remove all the images that contain the email addresses.

The Moderators

Click to expand...

Hi! Sir,

Some have Real and Some Don't have.

Could you Pls Remove all the Images for me

I will be Very Thankful to You.

 

Upvote 0