Find the bad e-mail address

[mafhobb]

Hello.

I use the following code to automatically send e-mails from a database using the smtp method:

Public Function SendsmtpEmail(txtRecipient As String, txtSender As String, txtsubject As String, txtbody As String) As String

On Error GoTo errorhandler

Dim mail
Dim sName As String

Set mail = Nothing

' Send by connecting to port 25 of the SMTP server.
Dim iMsg
Dim iConf
Const cdoSendUsingPort = 2
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

'   Set the CDOSYS configuration fields to use port 25 on the SMTP server.
With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xx.xx.x.xx"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
    .Update
End With

' Apply the settings to the message.
With iMsg
    Set .Configuration = iConf
    .to = txtRecipient
    .From = txtSender
    .Subject = txtsubject
    .HTMLBody = txtbody
DoEvents
    .Send
End With

' Clean up variables.
Set iMsg = Nothing
Set iConf = Nothing

Exit Function

The e-mail variable is in this form: maf@myemail.com; bfe@myemail.com; tyr@myemail.com

This code works really well with one exception; when one of the e-mails is not recognized by the server it returns an error and it does not send the e-mail.

Error: "The server rejected one or more recipient addresses. The server response was: 550 No such recipient "

What I'd like to do is for the code to be able to figure out which e-mail address is creating the problem and resend the message to all but that e-mail. A message box needs to be displayed to warn the user about that e-mail not working.

What do you suggest?

mafhobb

 

[Estuardo]

G'd Afternoon Mafhobb,
The Regex library will do that for you. This is the way i do it

Private Const mPattern As String = "^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"

Public Function IsValidMail(strEAddress As String) As Boolean
'.Purpose      : To check if for valid e-mail address. Library: VBScript Regular Expressions 5.5 [Regex]
'.Author       : Estuardo Sierra

    Dim Regex As RegExp

    Set Regex = New RegExp
    With Regex
        .Pattern = mPattern
        .IgnoreCase = True
        .Global = True
    End With
    If (Regex.Test(strEAddress) = True) Then
        IsValidMail = True
    Else
        IsValidMail = False
    End If


End Function

G'd luck

 

[jdraw]

Just a comment. Just because the format of an email address is correct, does not mean this is an actual email address.

eg. JohnAndHisWife.TheBrowns@gmail.com would pass the format/syntax check, but there is no guarantee that this is someone's email address.

I tried this and sent it via GMail
Here is part of the response

Delivery to the following recipient failed permanently:

JohnAndHisWife.TheBrowns@gmail.com

Technical details of permanent failure:
The email account that you tried to reach does not exist. Please try double-checking the recipient's email address for typos or unnecessary spaces. Learn more at http://support.google.com/mail/bin/answer.py?answer=6596

 

[Estuardo]

Thanks for your comment Jdraw, you're right the code doesn't check the address existence just if it has a valid structure.
In fact, there is no code to check for the existence of a mailbox before hand, unless you have your own mail server and the mailbox belongs to you. There is no public list of mailboxes. In the best case we can check if that specific domain exist, but once again, its existence does not guarantee that a given mailbox exist.
The bottom line (for me) is that we can do our best to check for valid addresses and be aware of changes in RFC5321

 

[mafhobb]

Yes, the problem is not that the e-mail is formatted correctly or not. It is that the e-mail address does not exist because of a typo when adding it to the db or that our IT admin has deleted it from the server because the user is not with us anymore.

I really think my only solution is to figure out what error I am getting and then run a sub to resend the e-mail and warn the user. However, how do I know what is the e-mail that is causing the problem?

mafhobb

 

[Estuardo]

Are you sending multiple address? if so, you can try to loop through them and every time you catch the error you can use a table, string, immediate window or anything to store the offending address.
Or do it manually from your own mailbox and wait for reject address.

 

[mafhobb]

I am sending one single e-mail to multiple addresses. How do I know which one is the bad one?

When I get the error it does not tell me which e-mail is causing the problem. That is where I am stuck at.

sportjet

 

[mafhobb]

OK, how about code that would, when an e-mail address error is found:

1) Find out how many e-mail addresses is the e-mail being sent to.
2) Resend the e-mail to all the e-mail addresses but one.
3) If the same error happens, add the e-mail address that was removed earlier and remove different one
4) Resend.
5) Go on until the e-mail goes through without errors or all e-mail addresses have been tested but the same error still occurs, which would indicate that two e-mail addresses are wrong.

Thoughts?

mafhobb

 

[gemma-the-husky]

OK, how about code that would, when an e-mail address error is found:

1) Find out how many e-mail addresses is the e-mail being sent to.
2) Resend the e-mail to all the e-mail addresses but one.
3) If the same error happens, add the e-mail address that was removed earlier and remove another one
4) Resend.
5) Go on until the e-mail goes through or all e-mail addresses have been tested but the same error still occurs, which would indicate that two e-mail addresses are wrong.

Thoughts?

mafhobb

i don't think the real recipients would like that - getting stacks of spam!

if you cannot detect which address failed within your email system, then why not send separate emails to each recipient?

surely there must be something in the error message though to determine which address failed?

 

[mafhobb]

Actually, when this error happens, no e-mail goes out at all, to any of the address.

All I get is this" Error: "The server rejected one or more recipient addresses. The server response was: 550 No such recipient ""

No details at all.

mafhobb

 

[Estuardo]

G'd morning,
I think that you should change your approach (like i suggested before)
Instead of sending to all recipients at once do it one by one.
Otherwise you'll never be able to know which is/are the offending address(es).
Why to send them all together?

 

[mafhobb]

I send them all together because that way the recipients can "reply to all" and keep everyone in the loop. This is important because many times the db users do not know who is working on the same project at different levels.

In any case, it just occurred to me that perhaps what I can do is to test the e-mail addresses separately before sending the group e-mail. How would I go about doing that? Is there any way to test an e-mail address without sending an e-mail?

mafhobb

 

[gemma-the-husky]

tbh, i just can't believe that outlook (say) doesn't tell you which recipient could not be delivered.

that's where to look, I think.

 

[mafhobb]

uhm...unfortunately we do not use outlook. We use Novell groupwise. That is why I cannot use certain methods to send e-mail and I have to go smtp.

mafhobb

 

[Estuardo]

After all i was partially right... i'd said that there is no code to check for the existence of a mailbox beforehand, and that's not true. In fact there are handful of scripts to do it.
The problem (putting aside if can be implemented from access or not) is that not always work... for many many reasons. the most common i'd found are:
Depending on how is configured the server, it may
a Just ignore/reject the connection.
b Respond that no user/address belong to that domain when in fact it does
c Takes ages to verify a mail address.
d Can't connect at all

Any how, i also found that testing from the command line is easier and "faster". The two commands are NSLookUp and Telnet.
This is what i have in .bat file: (Please note that each line is an enter keystroke)

nslookup
set type=mx
gmail.com
exit
telnet alt1.gmail-smtp-in.l.google.com 25
helo
mail from:senderemail@domain.com
rcpt to:thisaddressisfake@gmail.com
quit

the nslookup is to find out if the domain for the given address exist. In this case gmail. If the domain exist you should get a response like this:

gmail.com MX preference = 40, mail exchange = alt4.gmail-smtp-in.l.google.com
gmail.com MX preference = 20, mail exchange = alt2.gmail-smtp-in.l.google.com
gmail.com MX preference = 30, mail exchange = alt3.gmail-smtp-in.l.google.com
gmail.com MX preference = 5, mail exchange =  gmail-smtp-in.l.google.com
gmail.com MX preference = 10, mail exchange = alt1.gmail-smtp-in.l.google.com

At this point we get all the mail servers (mx) for gmail. to not get in details let's choose the server with preference 10 (the normal/standard)
Connect to that server via telnet on port 25 (SMTP port)
The first word you type when the session is open should be HELO
Then set the sender address
Wait for response. If everything goes fine your email address is ok and you can type the address you want to test, with the command rcpt to:

If this can be done within Access.. i guess so, i just haven't tried.
I hope it helps.

 

[mafhobb]

OK. What if the access db is used in an intranet situation and all the e-mails it tries to send are for addresses in our own domain? We have our own e-mail server.

Does that change anything?

mafhobb

 

[mafhobb]

Well. This has been dead for a little while, but I still do not have an easy solution...Anyone has a suggestion?

mafhobb

 

[mafhobb]

I am still hoping that somebody has a suggestion...

mafhobb

 

[EssexRich]

If this was an outlook/ exchange server environment, id setup distribution groups in Outlook or on the server. Then you only need to send to one address and let the server worry about it.

 

[mafhobb]

Well, due to the lack of a simpler solution I think I will just run some code when the error is detected to remove one of the e-mail addresses and try again, if it fails, remove another one and try again. Do this until the e-mail gets through, in which point I will know which e-mail is bad.

This will only work when only one e-mail is bad, but at least it is a start.

mafhobb