SP for CDO Bulk Mail Fails - Help!!
-
I was tasked to write a system to deliver stock emails to all our company's employees. I figured the easiest way to do this was to write an SP that uses CDOSYS, construct the message then loop through the table changing the TO field and SENDing the message. I tested with about 1,000 staff in our division with no problems. Every time I try and send with the full list of 27,000+ it fails randomly between 400 and 1,200 SENDs. The SP is chugging along fine sending emails (I log each email address sent to) and then suddenly it exits to the calling WSH script. I check in the script for a bad return code from the SP but it returns 0 and doesn't throw any kind of other error.
Before everyone asks, I'm required to send one individual email to each employee. I'd love to be able to batch them 1,000 at a time and send just 27, but there's personalization involved...
I'm lost. Does anyone use an SP for this kind of processing? Are there limits to this kind of processing in CDOSYS, i.e. do I need to destroy and rebuild the message for each SEND, even if all I'm changing is the TO field?
I've been through the MS docs on CDO, but that's pretty much oriented toward sending ONE message, and coding for a web page...
Any and all help will be appreciated.
-
CDO can be quite flaky at times.
Have you tried using Sequels own xp_sendmail instead?
e.g:
exec master.dbo.xp_stopmail
exec master.dbo.xp_startmail
exec master.dbo.xp_sendmail @recipients = @varRecipients, @copy_recipients = @varCopyTo, @message = @varMessage, @subject = @varSubject, @attachments = @varPath
May be worth a try. As may http://www.cdolive.com/ if you stick with CDO.
Have fun
Steve
We need men who can dream of things that never were.
-
Unfortunately the mail body is an HTML page so it appears xp_sendmail won't work. As far as I can tell from BOL xp_sendmail only sends text messages...
I'll look at CDOLive. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply