A key piece of using VBA to automate the sending of emails using Outlooks is a little .dll called Outlook Redemption
Outlook Redemption works around limitations imposed by the Outlook Security Patch and Service Pack 2 of MS Office 98/2000 and Office 2002 and 2003 (which include Security Patch) plus provides a number of functions to work with properties and functionality not exposed through the Outlook object model. What's this mean? You don't get those little "A program is trying to send an email on your behalf..." messages or the 5 second waiting period.
With Outlook Redemption you can...
- Make your VB and VBA code run unaffected by the Security Patch.
- Access properties not exposed by the Outlook Object Model (internet message headers, sender e-mail address and hundreds more properties)
- Directly access RTF body of any Outlook item
- Import MSG, EML (RFC822) and TNEF files
- Export messages to MSG, EML, TXT, HTML, TNEF and vCard formats.
- Directly access message attachments as strings or as arrays without saving them as files first
- Display Address Book
- Force immediate Send/Receive (Tools | Send/Receive in Outlook)
- Track new e-mail events with (unlike Outlook) new e-mail item passed to your handler.
Redemption supports Outlook 98, 2000, 2002 and 2003 (Outlook 97 is not supported).
I say...check it out if you haven't already.
One thing I'm unsure how to do (either using Outlook Redemption coding or standard VBA for Outlook) is how to specify a particular email account from which to send. Right now I've got two email accounts running through Outlook -- my business email and my personal Gmail POP account. Once in a great while an email I automatically generate and send using my code sends using the Gmail account...
I've emailed Dmitry Streblechenko (Outlook Redemption creator), asking if this can be specified using Outlook Redemption, and I'll post what I find out.