Getting a Handle on Your E-mails with VBA

One of the trickiest things to get used to for developers new to Outlook programming, and even those with some experience, is figuring out how to get programmatic access to various items at various times.  There are built-in methods to get this access, but sometimes the problem lies in determining which is the proper object to automate.

Let’s say you want to perform some custom actions in code with an e-mail message.  Is there something like GetMessage that will return the open e-mail?  Not really, but close – you can easily write code in an ad-hoc macro to get this item and work with it like this:

Dim objMailItem As Outlook.MailItem
Set objMailItem = Application.ActiveInspector.CurrentItem

But what if you ALWAYS want this action to occur?  It is not very productive to manually click a custom toolbar button to fire you macro that will run your code every time you need access to an item.  The secret is to use a mixture of events, collections and item objects that get created and properly bound every time Outlook starts, so that your code will always run.

(Before I go any further, I just want to briefly cover the basics of working with macros in Outlook in case you are new to this.  There is also a comprehensive summary of Outlook programming at the Developer Learning Center for Microsoft Outlook:

http://www.outlookcode.com/d/index.htm…)

The key to this is the ThisOutlookSession module.  This always lives within the Outlook Visual Basic Editor or IDE (Integrated Development Environment).  You can open this with ALT+F11 or Tools|Macro|Visual Basic Editor.  This special module, along with other modules, classes, and custom user forms that you can create are listed in the Project Explorer in the IDE (CTRL+R, or View|Project Explorer).

The ThisOutlookSession module needs only a handful of code to be written that’ll ensure your code runs at the right time.  The most important thing that needs to be added is the Startup event:

The Startup procedure is an event exposed by the intrinsic Outlook Application object – the big daddy object for Outlook development.  Every other Outlook object is derived from the Application object.  In the above example, I am instantiating a variable declared in the module to load my code that is stored in a custom clsMailItemTrapper class that I’ve added to the VBA Project (make sure to choose Insert|Class Module in the IDE to create and name this class correctly; this is where all of the code below needs to be stored).  The Startup event ensures that any events I’ve declared in that class which is tied to a particular Outlook item is trapped so that I can perform operations on that item with my code.  I also make sure to set this class to Nothing when Outlook’s Application_Quit event fires so that I can release the memory I’ve claimed with any object references that are being used.

Now we come to the meat of the solution – the clsMailItemTrapper class.  This class is meant to get programmatic access to any e-mail message that is created or opened.  To do that, I need to declare three objects within the class to setup the proper hooks:

The first one in the chain is the Inspectors collection.  This is another intrinsic object collection exposed by the Outlook Application object.  When I declare this object WithEvents, it exposes the one and only event you can work with in that collection – the NewInspector event.

We also need to work with two class events to set these objects and clean up when the class is disposed of in the ThisOutlookSession.Application_Quit event:

Anyway, the NewInspector event is fired whenever an Outlook item is opened:

As you can see, the event passes the Inspector object in the arguments list so that we can work with it.  However, in order to get rich access to a specific Outlook item’s properties, we need get that item’s specific object via the Inspector.CurrentItem property.  In this case, we are inspecting the Class property to make sure that we only set the objMailItem variable when the Inspector is an e-mail message.  If we didn’t evaluate for olMail as the Class value, we would get an error if, for example, the Inspector was for a Contact item.  We’d need a variable declared as a ContactItem to do that properly, but for our purposes we are just going to work with MailItem objects.

We are also setting the reference to the passed Inspector object to a declared objOpenInspector object, to get at specific Inspector events.  There is only one that is of any use:

Just a quick note about Inspector objects: An Inspector object is a simple interface object that all Outlook items share – MailItems, ContactItems, TaskItems, etc.  This object is primarily an access point to an item window, with properties for the CommandBars collection and window elements (size, position, etc.), among other things.

The remainder of the code is the real juicy stuff – events exposed by the MailItem object.  There are a lot of spots here where you can put in code to do all kinds of cool things – validation of business rules, read data from other Outlook items, write to a database, execute toolbar buttons, etc.  The world is your oyster!

For illustration, I’ve inserted a bunch of prompts to show you when these events get fired.  After you implement this code, restart Outlook (or just put the cursor inside the Application_Startup event and hit F5 to instantiate the class), and then try opening existing e-mails or create new ones to see what is going on.  It might give you some ideas about where and when is the best spot to work some of the magic that you are planning using the power of Outlook VBA.

Enjoy!

 

Eric Legault

Microsoft Outlook MVP (15 yrs). Author: Microsoft Office 2013 Pro Step By Step. Consultant/speaker/Outlook add-in builder/SharePoint pioneer. Guitarist: Maiden Canada (Iron Maiden tribute band)

You may also like...

%d bloggers like this: