How to automatically sort and group complex email in Outlook

The problem:  I get too much automated email from one of our internal bug tracking system.  Yet, I still need to keep up on what's happening with my products.

Of course, this gets filtered via rules into an appropriate folder for parsing outside of the normal flow of email.  In this particular system, messages come every time a new entry is submitted AND every time new information is added to the entry (such as notes or status change).  During busy times, this can add up to a lot of email and a lot of repeated email when what I really want to see is the most recent message about a given entry.  Normally, this would be easy to solve with the Outlook view by conversation feature.  The problem, though, is that when the status changes, the email message comes through with a different subject line.  This breaks any sorting by "conversation" in Outlook.  (The bug tracking system doesn't add threading information to the email messages.)

As you can see in this snippet of a grouped-by-conversation message list, the highlighted item labeled CQ00354159 has had a status change.  Under lots of activity, the status change might be a page above (or below) other messages about this particular entry.  I want them all grouped together.  And the obvious grouping factor is the defect number, CQ00354159.  But how come up with a way to tell each message that it should have a new property that has this number in it?  And how to parse the CQ number out of the message?

I haven't found any other way than to sit down with a scripting language, and Outlook (most versions) comes with Visual Basic for Applications, which I know something about.  Here is what that looks like.  Feel free to copy it for your own purposes.  (If you don't know that Alt-F11 gets you to VBA from all Office applications, you may not want to do this yourself.)

Sub SetCQforItem(e As Outlook.MailItem)
    If InStr(e.Subject, "CQ") > 0 Then
        CQ = Mid(e.Subject, InStr(e.Subject, "CQ"), 10)
        e.UserProperties.Add "CQNumber", olText, True
        e.UserProperties("CQNumber") = CQ
        e.Save
    End If
End Sub

This will take an email message, check the subject line for the special string "CQ" and then create a UserProperty in that message and set its value to the 10-digit string from the subject line.

How to apply this script to the messages filtered into the defects folder?  Outlook provides a mechanism to fire scripts within rules, so one could fire this script every time the filtering rule is run.  (Here is some useful info on this feature from Microsoft: How to create a script for the Rules Wizard in Outlook.)  There are a few drawbacks with this: First, user scripts are held on the local copy of Outlook, so Outlook must be running for the script to work.  Second, scripts are a security issue and many companies don't like people running them automatically.  (My setup won't let scripts run, even under "low" macro security.  Nor can I set up my own digital signature via selfcert.exe.) 

I have a not-quite-ideal solution that still uses the above macro.  Since I tend to review these defects once a day, it is okay to run this macro by hand.  I was already in Rules mode, so I set up a separate rule that I could manually run (in Outlook's language): 

Apply this rule after the message arrives / on this machines only / run Project1.SetCQforItem

I can then "Run Rules Now" from within my defects folder, pick this rule and have the macro add the number to all messages sitting there.  It takes from a few seconds to a minute, depending on how many messages are sitting there.  (Also, I have written a script that loops across all the messages in the folder.  They both do the same thing.)

With the CQ number in each message, it is a matter of customizing the current view to Group By the CQNumber property I've created.  Get to this dialog by View -> Arrange -> Custom.  And then click on the Group By button.  Be sure to select "User-defined fields in folder" from the list at the bottom of the dialog.  Then select CQNumber in the first Group.  And you should be golden.

Here is the result, for me.  Everything nicely grouped.