"Need help with e-mail validation"

We have a system at my job that "plows through" about 45,000 records, and based on a set of criteria, will automatically e-mail to those lucky selected folks that meet the criteria.

For the e-mail addresses, we pass them through some rudimentary format checking, which works in most cases, but not all. Does someone have, or know of a location where there is, some code that validates the format of e-mail addresses? Or maybe a query of some kind?

We are not concerned with if the e-mail address is truly an address, only if its format is correct - i.e., no spaces, no weird characters like "/", no double "@@" or double "..". In other words, if the format is invalid, we don't want that address going on to the "next step."

Thanks to all of you for your help and wisdom. This forum is wonderful!!

Not the best; but a start...

E-mail validation

Wow! That was sure a fast response!! Question..... will the code you posted handle invalid addresses such as:

Joe Blow @ nowhere.com or

Joe/Blow@.nowhere com

and so on and so on?

I will definitely be trying the code you sent.... and I appreciate your assistance SOooooooo MUCH!!!!!

Not yet. But as I said, it's a start.

It checks that the email address contains an @ and that there is at least one . after the @ to represent the domain.

E-mail validation

Gotcha...... our code does that also...... however, our code (which I inherited) is just awkward / cumberson for checking other kinds of validity. Here's a copy of the source, so you can see the problem I'm encountering:

Function bCom(psEmail As String) As Boolean

Dim sEmail As String
Dim sAddress As String
Dim sUser As String

bCom = True

sEmail = Trim(psEmail)

If sEmail Like "REMOVE*" Then
bCom = False
Exit Function
End If

If sEmail = "" Then
bCom = False
Exit Function
End If

If CBool(Val(InStr(sEmail, " "))) Then
bCom = False
Exit Function
End If

If InStr(sEmail, " ") > 0 Then
bCom = False
Exit Function
End If

'Check for a "." or "@" at the end of email
If Right(sEmail, 1) = "." Or Right(sEmail, 1) = "@" Then
bCom = False
Exit Function
End If

'Check for a "." or "@" at the end of email
If Left(sEmail, 1) = "." Or Left(sEmail, 1) = "@" Then
bCom = False
Exit Function
End If

'sHead is an in-house routine for splitting strings, based on finding a specified character

'split email address at the "@" sign
sUser = sHead(sEmail, "@")

If InStr(sUser, "/") Then
bCom = False
Exit Function
End If

If InStr(sUser, "+") Then
bCom = False
Exit Function
End If

If InStr(sUser, "&") Then
bCom = False
Exit Function
End If

If InStr(sUser, ";") Then
bCom = False
Exit Function
End If

'Not sure if the next piece of code does anything or not !!!!

If Chr$("95") <> "_" Then
bCom = False
Exit Function
End If

'If Asc(sUser) < 48 And Asc(sUser) > 90 Then
' bCom = False
' Exit Function
'End If

'check the first character for only "_"
'If (Asc(sUser) > 65 And Asc(sUser) < 122) Then
' bCom = False
' Exit Function
'End If

'sTail is the reverse of sHead...... starting at the right of a string variable, it splits the variable based on the search character indicated.

sAddress = sTail(sEmail, "@")

If InStr(sAddress, ".") < 1 Then
bCom = False
Exit Function
End If

If InStr(sAddress, "@") > 1 Then
bCom = False
Exit Function
End If

End Function


'*************** End of Code ***************

If you would be so kind, I'd love to hear your opinion / critique of the above code ...... I think it is somewhat weak, myself. Thanks again for your interest and assistance!!!!!!

How's this?

E-mail validation

??? Your last post appears to be empty...... or...... was that your opinion of my inherited code????

It's not empty anymore.

Are you running exchange server? If so email addresses are resolved via the end users NT login "jblow" = jblow@gm.com.

Also shouldnt these e-mail addresses be stored directly in the database? If that were the case you wouldnt have to do much checking. The checking would be some sort of admin functionality as to who is entering these e-mail addresses in.

Just some thoughts...

E-mail validation

Thanks for your response! Yes, we are running an Exchange Server. Our "plight" in this project is / was that the email addresses we have obtained (given to us by our customers) were not in-house, so to speak.

What we have been working on is a way of generating e-mails to our customers, using their e-mail addresses. In some cases, we have received some interesting addresses...... such as:
Joe Blow @@.com, Mary/Jane.nowhere, etc. and our data entry staff enters the addresses exactly as received. Being aware that some of the e-mail addresses are invalid, we've been in the process of basically validating the format only of the addresses. At this pooint, we only want to confirm the format of these addresses...... as far as whether the address are legitimate or not, is not our concern, since once an address is e-mailed out, provided its format is OK, we don't have much control of the address.

But, again, thank you for responding...... I appreciate it!!

Mile-o-Phile e-mail validation

Mile-O-Phile,

Thanks millions! I'll dig into what you sent and "play" with it. Keep up your great work and again the speed of your replies is great!