?
Solved

Sending multiple emails using CDO gives error

Posted on 2008-11-19
3
Medium Priority
?
798 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:maartendierckxsens
2 Comments
 
LVL 37

Accepted Solution

by:
meverest earned 900 total points
ID: 23051954
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
 

Author Closing Comment

by:maartendierckxsens
ID: 31518179
Thank you for your help
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question