This is an outline I'm going to use to fix up a mass email script for Outlook that I'm working on. It was kinda elegant and explains how to do some cooperative multitasking with Visual Basic for Applications.
As you might imagine, there are a lot of mass email tools for Outlook. See Mass Mail Tools for Outlook, but this first version had some special needs (it was a huge list), and the second one I'd like to give to others, so a simplified, stripped down tool is best.
What I learned in writing the first version was that a script that runs for several hours really can ruin your productivity. Multitasking is required. The problem with VBA is that it doesn't really multitask too well. You have to really regulate the program execution on your side so it calls DoEvents (the call to yield control to the system) many times.
The real solution (meaning the next version's method) is to use VB.net or C#.net, and run the program in a separate process, interacting with Outlook via the primary interop assemblies. But that has its own problems with deployment and execution.
I'm not going to release the full code. The mass mailer has been temporarily shelved due to time crunch.
EmailAddress Class Dim email Dim fname Dim lnam MassMail Class Dim addresses() as EmailAddress Dim immediate as Boolean Dim msg as Outlook.Item Dim running Dim shutdown Sub Load(message, list, immediate) Sub Run() Sub Pause() Sub Resume() Sub Exit() Sub Init() Sub UIInitChooser() Sub UIInitProgress() Sub UIUpdateProgress(text) Sub UIChoose(message,list,immediate) Sub MMSend(email,fname)
Starting creates an instance, then calls Init(). It fires up the Chooser, which is used to select a message and list. Completing the dialog messages UIChoose() and that will call Load() to load up our data. Then the Chooser is closed, properties set, and Run() is called.
Run() loops over addresses() merging each element with msg, and sending it or displaying it. If it’s sending, it has a 5 second delay and displays progress in the Progress UserForm.
Just to be safe, don’t allow it to display more than 50 merged messages. Instead, warn the user with a MsgBox. On returning, call MassMailShutdown() to dispose of this instance.
Each time through the loop, check that running is true. If it’s false, don’t do anything - because we’re paused. If shutdown is true, then exit the loop and call MassMailShutdown() which will dispose of this instance.
Pause() and Resume() change the value of running. These can be called from the Progress UserForm, which should have a button that toggles modes.
Clicking the close box on the progress UserForm should call Exit(), which will unload the Chooser, set shutdown to true, and exit.
The overall pattern here is that all the UserForms should not contain much code. Rather, each element can access a global MassMail object (singleton) that will set properties, and those properties will be polled by the main loop, Run().
This way, the UI UserForms are decoupled from the main loop, Run().
Chooser will call UIChoose(). That then does some UI work, and calls Load() which will load up data. Load() then calls Run(). This is kind of cheesy - there should really be a controller that loops and interacts with the UI, but we won’t do that here.
Anyway, that’s a digression. The point is, the main loop will not only send or make messages, but will also call DoEvents(), the call to yield control to the system. This will allow the end user to continue to use the application while this little program runs. This is cooperative multitasking.
By containing our work in a loop, rather than scattered across the forms (which is the normal VBA style), we can regulate or at least minimize the use of DoEvents. By sending UI messages via object properties, we avoid dispatching code on events. And we want to avoid that because that code, if it runs long will halt the UI; if it does something to the data, it can break Run(). Better that all the UI can do is toggle some properties, and let Run() poll these properties and alter its execution.