Check for proper email address format using VBA? 1

[thecowboy47]

Does anyone have a code snippet that checks an email address entered as a string for proper format "@" and "." as two examples?

 

[SQLBI]

cowboy,

I've played about with this in the past using InStr() to check for various characters like "@" and more importantly in my experience, making sure ther are no spaces in the string.

If InStr(1, strEmail, "@") = 0 Then

MsgBox "Incorrect Email Address"

Else If InStr(1, strEmail, &quot; &quot;) <> 0 Then

MsgBox &quot;Incorrect Email Address&quot;

End If

The only problem with this is it's a little simple and whereas it will check for the existance of "." and return its position, it wont tell you that its in the correct position.

Hope his helps steer you in the right direction.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[thecowboy47]

Leigh -
Thanks for the fast response. Since Posting this question I found a good solution at another website. Here it is in case anyone can use it.

Option Explicit
Public Emsg As String
'********************************************************************************
'Validates an email address and returns either True if OK or False if failed.
'If failed, call the public variable Emsg to see a description of the error generated
'********************************************************************************
Public Function ValidateEmail(ByVal strEmail As String) As Boolean
Dim strTmp As String, n As Long
ValidateEmail = True 'Assume true on init
If strEmail = "" Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>You did not enter an email address!&quot;
ElseIf InStr(1, strEmail, &quot;@&quot = 0 Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>Your email address does not contain an @ sign.&quot;
ElseIf InStr(1, strEmail, &quot;@&quot = 1 Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>Your @ sign can not be the first character in your email address!&quot;
ElseIf InStr(1, strEmail, &quot;@&quot = Len(strEmail) Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>Your @sign can not be the last character in your email address!&quot;
ElseIf Right(strEmail, 4) <> &quot;.com&quot; And Right(strEmail, 4) <> &quot;.net&quot; And _
Right(strEmail, 4) <> &quot;.gov&quot; And Right(strEmail, 4) <> &quot;.org&quot; And _
Right(strEmail, 3) <> &quot;.us&quot; And Right(strEmail, 3) <> &quot;.tv&quot; And _
Right(strEmail, 4) <> &quot;.biz&quot; And Right(strEmail, 4) <> &quot;.edu&quot; Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>Your email address is not carrying a valid ending!&quot;
Emsg = Emsg & &quot;<BR>It must be one of the following...&quot;
Emsg = Emsg & &quot;<BR>.com, .net, .gov, .org, .edu, .biz, .tv Or .us&quot;
ElseIf Len(strEmail) < 6 Then
ValidateEmail = False
Emsg = Emsg & &quot;<BR>Your email address is shorter than 6 characters which is impossible.&quot;
End If
strTmp = strEmail
Do While InStr(1, strTmp, &quot;@&quot <> 0
n = 1
strTmp = Right(strTmp, Len(strTmp) - InStr(1, strTmp, &quot;@&quot)
Loop
If n > 1 Then
ValidateEmail = False 'found more than one @ sign
Emsg = Emsg & &quot;<BR>You have more than 1 @ sign in your email address&quot;
End If
End Function


- Bob

 

[SQLBI]

Bob,

Thanks for that, very useful.

Even though it's your own post, have a star!!

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk