Validate E-Mail Address...

[doran_doran]

I found following article from this forum. But where would I plug the code in my existing code.

http://www.access-programmers.co.uk/forums/showthread.php?t=30974&highlight=validate+e-mail+address

=== My Code ===
Private Sub cmdSendMail_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Recipients As String

Set db = currentdb()
'Set rst = db.OpenRecordset("tbltmpRecipients", dbOpenDynaset)
Set rst = db.OpenRecordset("Select * from tbltmpRecipients Where TotalRecipients Is Not Null", dbOpenDynaset)

If rst.EOF Then
MsgBox "You must populate at lease one e-mail address"
Exit Sub
Else
rst.MoveFirst
Recipients = rst.Fields("TotalRecipients")
rst.MoveNext

Do While Not rst.EOF
Recipients = Recipients & ", " & rst.Fields("totalrecipients")
rst.MoveNext
Loop
End If

Me.txtTotalRecipients = Recipients
rst.close
Set dbs = Nothing
Call SendNotesMailCOMVersion

End Sub

 

[doran_doran]

Jack Rules.... Yeahhhhhh

Re: Validate E-Mail Address... [Re: doran_doran]

Posted on 08/11/04 01:03 PM
Posted by babrandt - Utter Access Addict
Posts: 134 - Loc: N. Virginia • Edit

As I alluded to in your previous post, you might want to use the Like operator to attempt to validate email addresses. I think this task is actually more complex than it sounds at first. If you search the Internet on the topic, you'll find a number of pages on the subject--here's one.

Unfortunately, I don't know of any way to analyze regular expressions in VBA like you can in some programming languages. However, if you have a variable email that is a string and hopefully contains an email address, you might try
If Not email Like "?*@?*.?*" Then
'Give a message that it's a bad email address
End If

While the number of invalid email addresses you can get by this operation is still infinite, at least it's a "smaller infinite" than then the InStr(1, email, "@") = 0 you suggest

As for where to put it, your original question, well, as usual there is more than one way to skin the cat. Perhaps the most direct way is to create a validation rule in the table itself. That is, open the table in design view and select the email field. At the bottom, you should see Validation Rule and Validation Message. If you put Like "?*@?*.?*" in the Validation Rule and a pertinent text message in Validation Message, then that message should appear every time a user tries to enter a bad email into the record.

If you want to perform the validation in code, then you'll need to put it in an event that would fire after entering an email address, such as the AfterUpdate or Exit events of a text box.

Remember, originally we took the approach that it was okay if someone entered a Null email address b/c we just filtered it out when you opened the recordset. So, if you want to continue with that method of doing business, there's no need to check email addresses as they are entered. Instead, try

Set rst = db.OpenRecordset("Select * from tbltmpRecipients Where TotalRecipients Is Not Null AND TotalRecipients Like '?*@?*.?*'", dbOpenDynaset)

Finally, I stress that Like "?*@?*.?*" is not going to catch every invalid email address. So, you can always try to come up with a better solution, but in the end I can enter lkjads23423jdsfjkASDJ23sd@lkjsdjkfsdfsjkldsj.com and it's theoretically a valid email address, but I bet if I try it it'll get returned to me as not existing So, there's a point of diminishing returns, that's all I'm saying

Sorry this was so long winded!!


--------------------
"If you ever fall off the Sears Tower, just go real limp, because maybe you'll look like a dummy and people will try to catch you because, hey, free dummy."
-Jack Handy

 

[Kodo]

REGULAR EXPRESSIONS
Just checking for @ symbol is not any where NEAR the solution.

I'm just going to post my function... just use it.. trust me.

Function CheckMail(strmail as string) as boolean
set objRegExp= New RegExp

objRegExp.IgnoreCase=true
objRegExp.global=true

objRegExp.pattern= "\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

CheckMail=objRegExp.test(strmail)

set objRegExp=nothing
end function

usage:

if CheckMail(MyEmailVar)=False then
   'this is an invalid email addy
else
   'this is valid. Please proceed
end if

you will need to include Regular Expressions for VBScript 5.5 in your references.