The Long and Winding Road: a tale of grouping my mails using a user-defined field in Outlook

First and foremost an apology to all regular readers: I fully realize GridShore is not intended to deal with Microsoft technology (or whatever detritus is collected under that name). However, this one is such a beauty of having to go the long way to get things done that I don’t want to hold back.

As with many a long and winding trail, this one started this morning with what seemed like a short hike across a small and uncomplicated footpath. You see, of late I have found myself making more and more use of the different features of Outlook (something to do with becoming more senior in my company…). Among others, I’ve found that I’ve started using the little flags to mark mails that I have to get back to. But I receive so many mails nowadays that even in a single day the flags get snowed under and pushed off the list. Of course I could just sort by flag status, but I prefer to have today’s mails at the top (rather than the flags and ticks from two weeks ago). So I had myself a bright idea this morning: I’ll just change the grouping of my mails to group by received date and then flag status within the date groups. Walk in the park, right?

Oh mother….

 

First strike: the very simple idea

Okay, to recap, I wanted to end up with the mails in my inbox grouped first by date of receipt, then by flag status. Now, as it happens, if you do nothing to your Inbox (or any mail folder), Outlook 2007 sorts mails by date by default. You can even see this: just right-click the column headers in Outlook and select Group Box. You’ll see a tree display of grouping fields.

So, simple idea: just drag the Flag Status column into this Group Box to add it as a grouping to the Date. Unfortunately though, Date is an implicit rather than real property that Outlook groups by if there is nothing else and you cannot add any sub grouping to it. You can replace Date with anything, but you cannot add to it. Scratch that idea.

Second strike: a slightly less simple idea

So, the implicit Date as main grouping is out. No biggie, because the Receipt time is included in each mail as a property by Outlook. You even see it as a column in the default layout of every mailbox. Quick job to select that instead of the Date, then subgroup by Flag Status.

The result is, unfortunately, unusable. You see, the Receipt column includes the time as well as the date, even if you format the column to include only the date (the column format is really just for display). Almost no two mails arrive within the same minute. Group on Receipt time and you’ll end up with a million groups consisting of just one mail (maybe two every now and then). So that was that idea up the spout as well.

Third strike: some formula magic

Okay, so we cannot sort on Receipt time because it is too detailed. And there is no date-only column. But Outlook has a neat little party trick: user-defined fields (UDFs). You can define mail items to have custom properties and you can even associate a VB formula with those properties. So here’s our solution: create a custom property which will contain the Receipt time with the time part stripped off. Then use that as the main group selector.

Creating a UDF in Outlook 2007 is easy: right-click the column headers and select Field Chooser. From the drop-down, select “User-defined fields in Inbox”. Then hit the New button and select Formula as type. Fill in a name for the field and in the Formula box fill in

DateValue( [Received] )

Now right-click the column headers again and select “Customize current view”. Hit the “Group by” button. Uncheck the checkbox and select “User-defined fields in Inbox” from the drop-down at the bottom. Now, in the first drop-down for grouping field, select your new field and…. snag. You cannot select the field.

You see, you cannot group by formula UDFs in Outlook. Probably because VB is typeless in principle, so Outlook cannot predict the type of your field and so cannot compare the values reliably. So that idea is out as well.

The long way around

So that means there’s only one way to do this: add a custom property to each mail in the Inbox with the date part of the receipt time. And since it has to be typed field, it has to be added programmatically. So the long road means delving into the guts of VBA for Outlook 2007.

Since we’re going into programming mode here, it is necessary to think a little about what must be done. We want to add a custom date property to each mail in the inbox. But there are two kinds of mails in the inbox:

0. Mails that are already there, which require a one-time loop to add the property.
1. Mails which will come in later and must have the property added as they arrive; this will require event handling.

In both cases we will have to perform the following, general steps:

0. Get hold of the mail M to be augmented.
1. Get M’s receipt time R.
2. Figure out the date part D_R of R.
3. Create a custom property EmailDate for M, with value D_R.
4. Save M with its new property EmailDate.

Visual Basic for Applications in Outlook

Let’s start with some basics about Outlook Basic (ha, ha, I make a funny). Outlook VBA all lives in one large VBA project file called VBAProject.OTM, which contains one project called Project1. Everything has to be added there. Project1 itself consists of a sort of main module called ThisOutlookSession. This is the place to put simple macro’s and the entry points to larger programs.

VBA for Outlook is essentially the same VBA you find in any Microsoft application: a VBA editor (hit Alt+F11 in Outlook) which supports a subset of Visual Basic with some built-in functions and an application-specific object model. In the case of VBA for Outlook this model models things like Folders, MailItems, CalendarItems and so on.

Like all VBA languages, VBA for Outlook allows you to create special variables which can hold object instances that are aware of application events. For example, if you declare a variable like so:

Dim WithEvents inboxItems As Outlook.Items

and then instantiate inboxItems to be the list of items in the inbox, then inboxItems will be aware of any events that occur in the inbox. You can declare event handlers for any interesting events you want to respond to.

Dealing with existing messages

Let’s start simply with the existing messages in the Inbox. To deal with those we need a run-once loop and no event handling. In order to carry out the process outlined above, we will have to get hold of the folder object that represents the inbox in Outlook. We can then loop over the contents of this folder.

While setting the process for existing messages, we will be dealing with mail messages, which are Outlook.MailItem objects. We will be adding a custom property, which is an Outlook.UserProperty object. And we have to deal with the inbox, which is a mail folder — an Outlook.MAPIFolder object.

Getting hold of the Inbox folder is done by getting the default inbox folder from Outlook’s MAPI namespace — this is an object that we can get from the Application object. The Application object is present in every VBA language and represents the application (in this case, Outlook). The MAPI namespace has a getDefaultFolder method which takes a convenient constant to indicate we want the Inbox. After that we can loop over the objects in the Inboxes Items list to set the properties. The result looks like this:

Private Sub SetInitialProperties()
  Dim ns As Outlook.NameSpace
  Dim fld As Outlook.MAPIFolder
  Dim objUnknown As Object
  Dim mail As Outlook.MailItem
  Dim prop As Outlook.UserProperty

  Set ns = Application.GetNamespace("MAPI")
  Set fld = ns.GetDefaultFolder(olFolderInbox)
  
  For Each objUnknown In fld.Items
    If TypeOf objUnknown Is MailItem Then
      Set mail = objUnknown
      Set prop = mail.UserProperties.Add("EmailDate", olDateTime, True)
      prop.Value = DateValue(mail.ReceivedTime)
      mail.Save
      Set prop = Nothing
      Set mail = Nothing
    End If
  Next
  
  Set objUnknown = Nothing
  Set fld = Nothing
  Set ns = Nothing
End Sub

As you can see, adding a custom property is pretty straightforward; there are constants to indicate the type (which is why we are using VBA, of course). Getting the date is a matter of manipulating the existing ReceivedTime property of each mail a little. It is worth noting in the loop that there is a check to see if each item is really a MailItem — the Inbox may also contain sub folders, you see.

Next, hit the Run button to run the routine. Then check in Outlook (right-click the column headers, select the Field Chooser and select the UDF’s from the drop-down; drag the new field onto the Inbox columns). The new field contains the date of the mails, but the time midnight (so no more minutes to screw up the grouping). Try grouping by the new column — this time it works, since the new column has a type.

Dealing with incoming mail

In order to set the same property on incoming mail, we need an event handler. As explained above, we can create a special variable that will be made aware of events on any specific object (like the list of items in the Inbox). In fact, that list is particularly useful to monitor since it broadcasts an event every time an item is added. With that information, the actual event handler is pretty straightforward:

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
  Dim prop As Outlook.UserProperty
  Dim mail As Outlook.MailItem
  
  If TypeOf Item Is MailItem Then
    Set mail = Item
    Set prop = mail.UserProperties.Add("EmailDate", olDateTime, True)
    prop.Value = DateValue(mail.ReceivedTime)
    mail.Save
    Set prop = Nothing
    Set mail = Nothing
  End If
End Sub

Again, note the check for whether an item is a MailItem and not a new sub folder.

As you can see, the VBA convention is to have the name of the event handler be <variable>_<event>(<parameters>). This is also how the VBA interpreter links events to methods (or subroutines in this case).

Now, the event handler is pretty simple. But it does depend on the variable inboxItems being initialized to refer to the list of items in the inbox. So how is this accomplished?

Initializing the inbox variable

Obviously, since Outlook does not provide a database for its VBA macros, there is no such thing as a persistent variable. We will have to redefine the inboxItems variable each time Outlook starts. You guessed it: we need another event handler, written the same way as the earlier one (a subroutine referring to a variable). But isn’t that a chicken-and-the-egg problem? How do we initialize that variable? Luckily, as remarked before, there is one variable which is always implicitly available: the Application variable. And that represents Outlook which, lucky for us, is exactly what we need (we want to listen for application starts of Outlook). So our primal event handler will look like this:

Private Sub Application_Startup()
  Dim ns As Outlook.NameSpace
  Dim fld As Outlook.MAPIFolder
  
  Set ns = Application.GetNamespace("MAPI")
  Set fld = ns.GetDefaultFolder(olFolderInbox)
  Set inboxItems = fld.Items

  Set ns = Nothing
  Set fld = Nothing
End Sub

Startup is, of course, the event fired when Outlook starts.

 

Testing the event handler

The simple way to test the event is to run the Application_Startup() subroutine using the Outlook VBA tool to set the system up. Hit Alt-F11, open the ThisOutlookSession module (which contains the code described above), put the cursor in the Application_Startup() routine and hit the Run button. Then go back to Outlook and send yourself a mail. Once it arrives, it will have the new property automatically set.

 

Just when you think you’re there…

If you’ve been following along, there’s another test you can do (and which I did): close Outlook and start it up again. Then send yourself another mail. If you’re running Outlook 2003 or 2007, the new mail probably will not have the new UDF set.

So what went wrong? We created the event handler, the Outlook startup event handler to set everything up and tested it to see it working? Well, some debugging will tell you almost immediately that the Outlook startup event handler is not called. And the reason, it eventually occurred to me, is macro security. You see, by default Outlook will not run unsigned macros. Even macros defined in its own macro module.

You can get around this easily by going to the security settings for macros (in Outlook 2007 it’s called the TrustCenter) and selecting to run unsigned macros with a warning. But a better idea is to create a self-signed certificate and use that to sign your macro module.

To create the certificate, you need a tool called SelfCert.exe, which is installed with Office in the Office install directory (try c:\program files\Microsoft office\office or c:\program files\Microsoft office\office12). Run the program, fill in a name for your certificate and click OK. Then go to the Outlook VBA editor and select Digital Signature from the Tools menu. Click Choose and select your certificate. Click Ok and then save the module.

Now restart Outlook. It will give you a warning about macros, but if you select “Enable macros” everything will work. You can elect to trust your own certificate in the same dialog, which will eliminate the warning.

Finally…

So, finally, after all that, my Inbox is now grouped by date and then flag status. That took some doing…..

But perhaps someone else will find the experience useful.

 

Sources

The following forum archive provided me some valuable insights into how to get all of this working: www.eggheadcafe.com/forumarchives/outlook/Oct2005/post24967825.asp