Post a Comment On: A Nod to Nothing

In case there are any gurus out there who are significantly smarter about this issue than me, I thought I'd throw it out there and see if any commentary trickles my way.

I have a series of emails that I receive that are automated alert emails forwarded from another user. They all come to my inbox with the same subject line. So to alert myself to which emails actually pertain to me, I wrote a script to dig through the body of the email, find a particular bit of text that's always the same, and parse out the bit between that bit and the following comma, moving the resulting significant name value into the subject line. So, in Outlook - Tools, Macros, VB Editor, then this code...

Sub CollectionToSubject(MyMail As MailItem)
Dim strID As String
Dim olNS As Outlook.NameSpace
Dim olMail As Outlook.MailItem

strID = MyMail.EntryID
Set olNS = Application.GetNamespace("MAPI")
Set olMail = olNS.GetItemFromID(strID)
If InStr(olMail.Body, "SOMEVALUE") Then
startpos = InStr(olMail.Body, "SOMEVALUE") + Len("SOMEVALUE")
endpos = InStr(startpos, olMail.Body, ",")
olMail.Subject = Mid(olMail.Body, startpos, endpos - startpos)
olMail.Save

End If
Set olMail = Nothing
Set olNS = Nothing
End Sub

Easy peasy. But you have to make it fire somehow, so in the interest of expediency, I attached the code to an Outlook rule. Via the menu, tools, rules and alerts, create a new rule that says to apply the rule when a new message arrives with "xyz" in the subject line and run script x (above).

It still won't be happy at that point. Microsoft is sort of twitchy about security on scripts. So if necessary, run selfcert.exe (comes with MS Office, but you may have to run the Office install [control panel, add remove programs, Office - check "advanced", then select it from the resulting treeview under office shared features, digital certificate for VBA Projects] to pick up selfcert, it doesn't install automatically) from C:\Program Files\Microsoft Office\OFFICE11. The install, if you needed to use it, looks something like this.



[Image]Use Selfcert.exe to create a certificate. Call it something like "My Certificate Internal", just so you don't accidentially send it off as production-worthy verification.



Go back to the VB code in Outlook (the editing window via the macros menu item). While the code is visible, select Tools > Digital Signature. Pick the certificate you created.

Shut down and reopen Outlook (save .otm if prompted) so that everything is acceptable. If necessary, you may have to change "security" (same area as "macros") so that scripts will run if signed.

For example, you may see this message when you re-enter Outlook. If you get this, check "always trust" and Enable Macros and you're good to go.


[Image]If you give the script to someone else, you may want to just have them install your certificate instead of creating their own. There are rules for that here.

So, what's the problem after all of that, you might ask? The problem is that my company has set Outlook to do a bit of local caching. All the email is stored out on big mail servers, and then sent to my desktop where I have a cached copy of it (so it's not fetching it all the time, but stores it somewhere where spilling coffee on my machine won't destroy a year's worth of email). Mail is not forwarded from my desktop to my Blackberry, but goes to both machines simultaneously, or almost simultaneously. They each get their own copies. So, as near as I can tell, and this is where I'd be interested to actually know the mechanism/process, when I run the script above to update an email, the following process happens:

1. Email sent to my desktop machine cache.
2. Script runs, updates it.
3. Email sent to Blackberry. Maybe a few seconds later.
4. Desktop syncs with email server - hey, someone changed it.
5. Email server somehow knows there are multiple versions of the email because the Blackberry didn't update it's copy. Maybe it doesn't like the fact that a copy was sent with a timestamp later than the cache update timestamp. Without access to the email server (and there's no way I'm getting that), I really couldn't tell you.
6. Conflict error raised and the conflicts box/cache in Outlook starts to fill up with lots of messages about how I have two emails and it's not sure which one is valid, so it's going to keep copies in case I need to resolve the issue later.
7. The Blackberry, of course, never gets the email with the improved subject line because it gets an unchanged version, the original version, and the desktop cache doesn't sync with the email server telling it to sync with all devices. Or maybe it does, and that's what generates the error.

I can turn off the copy to the Blackberry, and when I do the conflicts that are filling up my server space go away, but that sort of defeats the purpose of knowing which ones I need to pay attention to on the device that gives me the best access to my email (the Blackberry).

How would you resolve this (and don't say something stupid, like use email program alternate#2 - if I could pick email programs for my company, I'd just have some programmer fix it for me because it's cheaper than blogging about it)? Give the script to the person autogenerating the emails in the first place? I thought about that, but it's obviously a rather convoluted process (for the first script you install), and I don't like things that use up other people's time. Perhaps add a line to the above script telling it to delete the item in question and remail a copy with the new subject line so there's no sync issue? Will that force my copy of Outlook into prompt mode all the time (Outlook likes scripts that .send email as they reek of viru-tude). I haven't tried that, and it would probably work, but it gives me extra items in my sent box that I have to deal with. I could run some follow up code to delete those items as well, but by now I'm getting into the classic "I have to keep adding code to take care of the issues being raised by the code" scenario, and I prefer simplicity.

Hopefully you didn't follow a search here looking for answers yourself.