ian-scott.net

Outlook Application_Startup() Failure and Workaround

This post is partly for my own memory and partly in case it helps anybody else googling. There follows a bit of technical discussion about some VBA in Outlook.

At work each project we’re working on is assigned a four digit job number. Within our standard email signature is a line beginning “File Number:”. On sending an email we add the job number to the end of this line1 before sending, so we can file it away in appropriate folder. The other day someone said something along the lines of Wouldn’t it be nice if the emails just went to the right folder. As they said it within earshot I couldn’t help but think, hey, can’t be too hard, right2?

It turns out to be quite possible. A bit of VBA written with a load of Google found tabs open in my browser and I had something of a solution. I’ll leave out the details but basically whenever a message is sent a VBA routine is run which compares the body of the email to a regular expression3. If it finds a file number it then traverses the Outlook folder tree4. Once found it selects the subfolder if necessary and finally moves the message.

In my initial testing everything went fine, well enough that I deployed the solution to a second machine to check it worked well there. A few days later though I noticed a problem5.

After a bit of back and forth I realised that when Outlook was starting up on login the emailing filing wasn’t working. The start of my code looks like:

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
Set Items = Session.GetDefaultFolder(olFolderSentMail).Items
End Sub

Private Sub Items_ItemAdd(ByVal Item As Object)
'Snip exciting stuff
End Sub

Debugging (i.e. adding msgbox("Hello World") everywhere 😉 ) showed that Application_StartUp simply wasn’t being called on, well, application startup. The weird thing is that exiting Outlook and then starting does call it and all is well.

I tried to find why this is but to be honest I have no idea. I tried forcing it by adding a /autorun switch to the shortcut in the startup folder6 but that didn’t help. Neither did starting via a scheduled task on login rather than via startup folder.

The Workaround

In the end I found a solution by starting Outlook with a scheduled task on login with a 30 second delay. It seems obvious that Outlook must either have something already running, or conflicting on start, or in a race condition, or something. As I said, I don’t really know what’s going on, just what’s working as a sort of fix.

Notes

  1. It’s actually a little more complicated as there are subfolders for whichever discipline (architect, structural engineer, etc) the email is going to, so a full line looks something like 1234-5
  2. A back of the envelope calculation by one of our directors showed moving sent email costs a few thousand pounds worth of time per year so a few hours of mine would be worthwhile
  3. This involves having included Microsoft VBScript Regular Expressions 5.5 under Tools->References in the VBA editor. The regexp itself is simply /File No:\s*(\d{4})(-\d){0,1}/
  4. So far as I can tell you can’t get a folder directly by just knowing its name (and our folders are named along the line of “1234 Job Name” anyway so it wouldn’t really help) so I adapted a bit of code I found.
  5. This is all Outlook 2010 on Windows 7 for the record
  6. As per this advice.

Comments and Pings

There are no responses

Leave a response

At least a name and email address are required. Email address is never displayed. Required fields are marked *

You may use the following markup: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <var> <del datetime=""> <dd> <dl> <dt> <em> <i> <li> <ol> <q cite=""> <span title=""> <strike> <strong> <sub> <sup> <ul> Comments policy