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 line¹ 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, right²?

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 expression³. If it finds a file number it then traverses the Outlook folder tree⁴. 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 problem⁵.

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 folder⁶ 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.