I spent most of today trying to catch up with my expenses (in order to plug the rather large hole in my bank balance before Christmas).  I work for a large IT company, and to say that our systems are antiquated would be an understatement: I understand that my expense claims are entered onto a computer system prior to payment but, before that, I have to complete an Excel spreadsheet and mail a hard copy, with receipts attached, to a team that processes them (which is corporate-speak for wasting my time, their time, and my manager’s time to quibble over minor infringements).  Recently a new level of bureaucracy was added to the process and, before snail-mailing the hard copy to be processed, I also have to e-mail a soft copy to my manager for approval, using a pre-defined format for the mail subject header.

You can probably tell by the tone of this post that I’m no fan of this process.  I understand from contacts at Microsoft, for example, that their system is entirely electronic, although paper receipts do also need to be submitted for audit purposes and I can’t see why we couldn’t do something similar.  Still, it could be worse: when I worked for a major fashion design, marketing and retail organisation a few years back, they insisted that I staple each receipt to a sheet of A4 paper first…

Anyway, after messing up the process a couple of times today and recalling messages with incorrectly formatted subjects, I decided that it’s something that should be automated.  I’ve never written any Visual Basic for Applications (VBA) before, but, armed with a few code snippets from the web, I managed to write a macro (the whole thing took me about 30 minutes to pull together and debug):

Sub SendToManager()
'
' SendToManager Macro
' Macro to send completed expense worksheet to one's Manager
'
' Keyboard Shortcut: Ctrl+Shift+M
'

'Create a new Workbook Containing 1 Sheet (right most) and sends as attachment.

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Copy

With ActiveWorkbook

.SendMail Recipients:="mail@domain.com", Subject:="Expenses for approval: " " & Range("C8").Value & ", " & Range("O8").Value & ", " & Format(Range("O9").Value, "Long Date") & ", " & Format(Range("Q48").Value, "Currency")

.Close SaveChanges:=False

End With

End Sub

The code is here for anyone that might find something similar useful… I’m sure that it will need modification to suit someone else’s requirements but the basic idea is here.  Basically, we create a copy of the right-most worksheet in our Excel workbook (I create a new sheet for each claim, and work left to right…), then we take that and send it to the specified recipient (one change might be to prompt for a user name) and format the subject with data from the sheet that eventually reads “Expenses for approval: name, employee number, claim date, claim value” before sending the mail.  Simple really.

Here are a few links that helped me out in doing this:

• Macros in Excel 2007 (I used the 2010 beta but it’s the same process to create a Macro – I created an empty one and then edited it to do what I wanted).
• The forums at ozgrid.com have loads of advice on Excel macros.
• This article helped me to format the strings.