pros & cons of various techniques for programmatically sending email
Posted on 2011-02-12
Over the years I've written several VB6 programs that send emails to people, usually run by a Scheduled Task. The programs read data from a SQL database, then send emails based on what's returned by a SQL Query and usually update the database to the effect that the email was sent.
I've used CDO messaging, Outlook VBA automation and possibly some other technique(s). I know CDO is being retired. Outlook VBA I find easy to use but once in a while Outlook on the server gets corrupted and emails stop being sent until I log on to the server, start Outlook in safe mode, close Outlook, etc. So that can be a bit of a maintenance problem.
Over the past two months or so, one of my CDO based programs has started duplicating messages sent to users. For example, Friday 25 emails were sent out by the program, 3 of them were sent multiple times. One of the 3 was sent 15 times. I've put a 60 second sleep statement in the program, so I can see the message wasn't sent repeatedly by my program -- all the messages have one minute intervals on their Sent date/time stamp. But the ones the were duplicated show as having been sent multiple times AT THE SAME TIME. So something's gotten fouled up with sending a message via CDO that causes it to be sent multiple times.
I've rebooted the server a couple of times, the problem persists. I've tried sending the same email repeatedly as a test, about once every 10-20 times it duplicates itself. So there's really something funny going on.
I'm thinking that rather than try to debug an old CDO related problem, I should switch the email delivery technique to something more modern.
What would anyone recommend as a best programming technique for this environment?