Sending multiple emails using CDO gives error

We have a system that send emails through an automated system to warn users. The system first collects all the emails that have to be sent out. Then it goes through a loop and send out the emails. In this loop there is an error handling. This error handling includes inserting the email, which email and the error message into a table in our database when it couldn't be send. The error number is -2147220977 and the description is 'the server rejected one or more recipient addresses.The server response was: 550'.

When the system comes at an email that couldn't be send (due to it doesn't exist) it will keep on trying to send the email untill the connection timeout is expired (60 seconds). This error also reflects to the rest of the emails that has to be sent out that comes after the wrong email in the loop. These correct emails are also inserted in a table in the database but the recipients recieves them.

How can we avoid that when the system comes at a wrong email it doesn't try to send it more than ones? So if it is a wrong email it just inserts it into the table and doesn't try to send it again.


greetings
Maarten
set rsApprover = objConn.Execute(mysqlstatement)
 
if not rsApprover.eof then
  'select email to be sent to performer in next flow (Brand Director)
  set rsMailBody = objConn.Execute("SELECT * FROM tblMailTemplates WHERE ([MailTemplate-ID] = 72)")
 
  do while not rsApprover.eof
    error = 0
    set rsEmployees = objConn.execute(sqlstatementemail)
 
    'send to Brand Director
    Set ObjMail = CreateObject("CDO.Message")
    Set ObjMailCon = CreateObject("CDO.Configuration")
    'Out going SMTP server
    ObjMailCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = application("smtpserver") 
    ObjMailCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = application("smtpserverport")  
    ObjMailCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = application("sendusing") 
    ObjMailCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = application("smtpconnectiontimeout") 
    ObjMailCon.Fields.Update 
    'Update the CDOSYS Configuration 
    Set ObjMail.Configuration = ObjMailCon 
 
    ObjMail.From = email@from.com
    ObjMail.To = rsEmployees("Email")
    ObjMail.Subject = rsMailBody("SubjectEN")
    strBody = rsMailBody("BodyEN")
    ObjMail.TextBody = strBody
    ObjMail.Send
    Set ObjMail = Nothing
 
    'check for error and create emailbody
    if Err.number <> 0 then
      error = 1
      strBody = strBody & "<tr>"
      strBody = strBody & "<td>2</td>"
      strBody = strBody & "<td></td>"
      strBody = strBody & "<td>" & rsApprover("PerformerStudent-ID") & "</td>"
      strBody = strBody & "<td>" & rsEmployees("Lastname") & "</td>"
      strBody = strBody & "<td>" & rsEmployees("Firstname") & "</td>"
      strBody = strBody & "<td>" & rsEmployees("Email") & "</td>"
      strBody = strBody & "<td>" & rsEmployees("Name") & "</td>"
      strBody = strBody & "</tr>"
      'function to trap the error
      TrapError strBody
   end if
 
    if (error = 1) then
      set rsUpdate = objConn.Execute("INSERT INTO tblMailNotSend (...)" &_
		"VALUES (...)")
    else
      set rsUpdate = objConn.Execute("INSERT INTO tblMailSend (...)" &_
		"VALUES (...)")				
      rsApprover.movenext
    loop
 
    'send email to admin if invitation(s) couldn't be send
    ProcessErrors
end if

Open in new window

maartendierckxsensAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

meverestCommented:
not really sure what you are asking -1) you want to prevent smpt server from attemtping to send an email more than once? or 2) do you want to prevent your script from sending to smtp server in the first place?

I'll try to answer both questions:

1. set the 'expiration timeout' to 1 minute.  that way smtp server should give up on retry before the first attempt.

2. harder to solve - you need to test each email inside your script.  If the address is invalid (ie not fully qualified domain etc) then it is easy to detect. if the address /looks/ ok but the domain is non existent, then you can use DNS to try to lookup whether that domain exists (google for asp dns).  It is even harder to detect whether an email address on a valid domain is ok, but there are plenty of reasons why you should not even bother to do it inside your script.

these sort of tests inside your scrip will slow it down ENORMOUSLY - it takes maybe up to a couple of seconds to try to resolve a valid domain, and at least a couple of seconds to determine if it is invalid.  can you really afford to let your script wait that long for every email address you send to?

it is better to let smpt server take care of all that for you.  the ideal way to fine tune your mailer is to make nda messages go to a known mail account, then run a script over those reports and remove those addresses from your mailing list.

Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maartendierckxsensAuthor Commented:
Thank you for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Email Protocols

From novice to tech pro — start learning today.