Writing a macro to e-mail a worksheet from an Excel workbook

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:

2 thoughts on “Writing a macro to e-mail a worksheet from an Excel workbook


  1. Well, you made that look easy! And have just added a tool to the ‘automation makes your life easier’ toolbox when anyone ever asks the question “But why would I waste time automating stuff?”

    And, added bonus, since you have the tool already, you can spend time beating them around the head with it so that they understand *properly* :)


  2. Ahh…excel VBA – it basically started my career!

    I still lean on it quite heavily, I’ve got an Excel “application” that automates our purchase orders and I’m currently putting the finishing touches to an Excel app that will manage our quoting process

    Both of these could probably be done by off the shelf apps but I much prefer the fact I know that it’s crafted specifically for our company!

Leave a Reply