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:
Option Explicit Dim myMailItemTrapper As clsMailItemTrapper Private Sub Application_Startup() Set myMailItemTrapper = New clsMailItemTrapper End Sub Private Sub Application_Quit() set myMailItemTrapper = Nothing End Sub
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:
Option Explicit Dim WithEvents objInspectors As Outlook.Inspectors Dim WithEvents objOpenInspector As Outlook.Inspector Dim WithEvents objMailItem As Outlook.MailItem
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:
Private Sub Class_Initialize() Set objInspectors = Application.Inspectors End Sub Private Sub Class_Terminate() Set objOpenInspector = Nothing Set objInspectors = Nothing Set objMailItem = Nothing End Sub
Anyway, the NewInspector event is fired whenever an Outlook item is opened:
Private Sub objInspectors_NewInspector(ByVal Inspector As Inspector) If Inspector.CurrentItem.Class = olMail Then Set objMailItem = Inspector.CurrentItem Set objOpenInspector = Inspector End If End Sub
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:
Private Sub objOpenInspector_Close() MsgBox "Inspector is closing..." Set objMailItem = Nothing End Sub
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.
Private Sub objMailItem_AttachmentAdd (ByVal Attachment As Attachment)MsgBox "The '" & Attachment.DisplayName & "' attachment has been added." End Sub Private Sub objMailItem_AttachmentRead (ByVal Attachment As Attachment) MsgBox "The '" & Attachment.DisplayName & "' attachment is being opened..." End Sub Private Sub objMailItem_BeforeAttachmentSave (ByVal Attachment As Attachment, Cancel As Boolean) If MsgBox("Are you sure you want to add the '" & Attachment.DisplayName & "'?" _ , vbYesNo + vbQuestion, "Confirm Attachment Insert") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_BeforeDelete (ByVal Item As Object, Cancel As Boolean) If MsgBox("Are you sure you want to delete this message?" _ , vbYesNo + vbQuestion, "Confirm Delete") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_Close(Cancel As Boolean) MsgBox "MailItem is closing..." End Sub Private Sub objMailItem_Forward (ByVal Forward As Object, Cancel As Boolean) If MsgBox("Are you sure you want to forward this message?" _ , vbYesNo + vbQuestion, "Confirm Forward") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_Open(Cancel As Boolean) MsgBox "MailItem is opened..." End Sub Private Sub objMailItem_PropertyChange(ByVal Name As String) MsgBox "The '" & Name & "' property has changed..." End Sub Private Sub objMailItem_Read() MsgBox "Existing MailItem is being read..." End Sub Private Sub objMailItem_Reply (ByVal Response As Object, Cancel As Boolean) If MsgBox("Are you sure you want to reply to this message?" _ , vbYesNo + vbQuestion, "Confirm Reply") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_ReplyAll (ByVal Response As Object, Cancel As Boolean) If MsgBox("Are you sure you want to reply to all of the senders of this message?" _ , vbYesNo + vbQuestion, "Confirm Reply To All") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_Send(Cancel As Boolean) If MsgBox("Are you sure you want to send this message?" _ , vbYesNo + vbQuestion, "Confirm Send") = vbNo Then Cancel = True End If End Sub Private Sub objMailItem_Write(Cancel As Boolean) MsgBox "Message has been saved..." End Sub