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.)

Grouping by conversationAs 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.)

Group byWith 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.
Grouping by cq

19 Comment(s)

JP said:

Jack,

I think you can use the CurrentView Property to programmatically change the current view. You could create another macro to change the view, when you're ready to view the messages.


HTH

cyborg said:

Hi,

Thanks for the script and it was very useful. When i ran this rule for the first time everything worked like a charm, but later when i added more message to a folder, they are not getting the "CQ---" column by default(not sorted) and CQnumber column has some numbers and some messages dont have any number. The newly added mails get a group like this "CQNumber:none (x item, x unread)

Why is it so and what am i doing wrong? Is that macro is that has to number each message is missing. Please help.

Thanks

Jack Vinson Author Profile Page said:

Cyborg-

You are doing nothing wrong. The issue is with the way rules work and whether you can run scripts when rules fire.

In the best world, you should be able to add another action to the rule that moves your messages into the special folder. (Under the "actions" settings there is an option to "run a script.") However, if Outlook is grabbing mail from an Exchange server, that script would have to be installed on the server -- and this is something that would have to be managed by your central IT organization. With the script on your local machine, the rule becomes a Local rule and can only run when your machine is on with Outlook running.

So, what I do is to schedule time with myself to check ClearQuest. The first thing I do is run the script, which adds the field to any new defects. After that, I am all set for the given session. It is annoying to have to do this each time, though.

cyborg said:

Thanks for your reply. I will link to a screenshot and you can see my situation and i still feel that i'm doing something wrong.

http://img525.imageshack.us/my.php?image=outlookrulevc8.jpg

Thanks for your reply. Please check the picture above and let me know
what i'm doing wrong and i still feel that there is some mistake done
by me. I'm a newbie for VBA programming.

Jack Vinson Author Profile Page said:

Cyborg -- Just re-run the rule. This looks exactly like my mailbox at the start of each "review defects" activity.

What do you think is going wrong?

cyborg said:

Thanks for your prompt reply. Whenever i re-run the nothing happens.At first it was fine.

The screenshot shows the current situation after re-running the script.

Why the numbers are missing for the messages? What should i do to get the numbers there.

Jack Vinson Author Profile Page said:

Ah... Now I understand.

Possible reasons:
* Macro security got reset somehow, so it cannot run.
* Script is pointing to the wrong folder.
* Format of new messages is slightly different and parsing out the RITM number isn't working
* ...

One thing I often do to test is use the MsgBox function to pop up a little message to tell me what's going on. For instance, after I get the "CQFolder," I can use the line


MsgBox CQFolder.Items.Count

to tell me how many messages are in the CQFolder.

You can do the same thing with a breakpoint, particularly if you need to see something within a loop. Select the line where you want to have the script stop and press F9 to turn on / off a breakpoint. Then when you run, the code will stop at that point and you can examine the value of the variables.

You might need to find a handy expert to look over your shoulder if it is much more complicated than this.

cyborg said:

Thank you. I will try to re-run the rule and see what happens.

Thanks again.

cyborg said:

Thanks for your reply. It works fine now.

/Stefano said:

I am using this technique, it works perectly. However in this way I am forced to get alphabetically sorted groups. I'd like the groups to be sorted by descending date, as it happens with "conversation grouping".

It seems to me that only the conversation grouping let me have conversations sorted by time: every other grouping gets sorted alphabetically. Any workaround?

Thanks,

/Stefano

Jack Vinson Author Profile Page said:

Stefano- With this particular example, sorting by the CQNumber was the same as sorting by date, as the numbers are assigned sequentially. (At least date of the original issue.)

I think you can tell the Conversation view to sort the conversations by date. You may have to go into the "Customize Current View" dialog box and explicitly tell it to sort by date, rather than by the Grouping. (I don't have Outlook running currently to check.)

Stefano said:

OK, this is clear. But I don't want to group by conversation and sort by date (so that the more recent conversation goes to the top). This works, it's good, it's the way I use the mail till now.

What I'd like to do is to group by a custom field (as you do) AND to order this threads by date. I tried, but I can only get an alphabetical order of this new threads.

I'll tell you also what I'm trying to do: I'd like to solve the old problem of non-outlook clients breaking the conversations, using non-standard subject prefix like Rif:, I: In: etc.

I wanted to use you technique to create a custom field like "normalized-subject" that contains the real normalized subject (I eliminate ALL strange prefixes within the vba code). Then, grouping by this new "custom" subject, I have real converasion grouped.

It's like to rewrite the outlook subject-normalization-routine, to make a more robust and cusomizable subject normalization.

Back to the problem: all works ok, except for the date sort issue...

Sorry for my english, it's quite hard to explain this thing :-)

/Stefano

Jack Vinson Author Profile Page said:

I know exactly what you are saying, so it isn't your english...

Can't you go into View-> .. Customize Current View and tell it to sort by something other than the current grouping?

Stefano Giacometti said:

Waiting to make it programmatically, I solved my issue installing "outlook spy". It let me change (force) the conversation topic (not the subject) in a way that I can put whatever mail I receive in whatever conversation I want.

In this way I have 2 big benefit:

1.
when, say, a lotus notes replies me, the reply create a new conversation (because of the strange prefix it uses). Now I can force this email into joining the correct outlook conversation/thread.

2.
It's useful to force an email to a conversation that maybe has a completely different "subject", but deals with the same argument.

My inbox is going to be very organized... :-)

Stefano Giacometti said:

Hi, just an update... I found a solution (using Redemption) to create a macro run by a rule on incoming messages that "cleans" up the "conversation topic" on the fly, letting outlook feature "group by conversations" work much better...

Notes clients now cannot brake my conversations anymore.

Ask for details, if anyone is interested, here or on skype (user cettolox).

/Stefano

Tido said:

Hi Jack,

This is great. It works. But I cant seem to use this for outlook's "search folders". Also in regular folder's I can't seem to get the email sorted by "received". It just sorts the email by the custom field, but I would like to see the email which was most recently received at the top.

Great work Jack,
Tido

Tido- I've been frustrated with Search folders too. They should work just like any other email folder, right? As far as sorting by "received," you should be able to sort within the grouping by the received date - I think this may be the default behavior. If you want to sort the whole folder by received, that removes the grouping that I talk about here, but it should work. Just click on the Received header?

Tank Author Profile Page said:

Hi.

Great solution, thanks!

Is there a way to go one step further and SORT by count(distinct CQNumber)
in other words, I want to see the groups with the most items in them at the top.

Outook already provides this next the the grouping in brackets, but one cannot sort.

I'm not a guru in VBA so I don't even know if it's possible to do with code.

Yikes. It's been a long while since I've thought of this. If you can get the Count number into a column, it should be possible.

Leave a comment


Previous entry: Global MAKE winners for 2008

Next entry: The stories of our times

About this Entry

This entry was published on December 15, 2008 12:17 AM and has 19 comment(s).

Related Entries

Find recent content on the main index, explore the full tag cloud, or look in the archives to find all content.

Syndication Options

Subscribe to Entry

Powered by MT-Notifier

Powered by Movable Type 4.35-en
Picture a steaming coffee cup. Better yet, grab one and have a read!

KJolt Memberships

Follow jackvinson on Twitter

View Jack Vinson's profile on LinkedIn