{"id":1570,"date":"2009-12-14T22:01:22","date_gmt":"2009-12-14T22:01:22","guid":{"rendered":"http:\/\/www.markwilson.co.uk\/blog\/2009\/12\/writing-a-macro-to-e-mail-a-worksheet-from-an-excel-workbook.htm"},"modified":"2016-10-19T08:49:17","modified_gmt":"2016-10-19T07:49:17","slug":"writing-a-macro-to-e-mail-a-worksheet-from-an-excel-workbook","status":"publish","type":"post","link":"https:\/\/www.markwilson.co.uk\/blog\/2009\/12\/writing-a-macro-to-e-mail-a-worksheet-from-an-excel-workbook.htm","title":{"rendered":"Writing a macro to e-mail a worksheet from an Excel workbook"},"content":{"rendered":"

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).\u00a0 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).\u00a0 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.<\/p>\n

You can probably tell by the tone of this post that I’m no fan of this process.\u00a0 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.\u00a0 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…<\/p>\n

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.\u00a0 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):<\/p>\n

Sub SendToManager()
\n'
\n' SendToManager Macro
\n' Macro to send completed expense worksheet to one's Manager
\n'
\n' Keyboard Shortcut: Ctrl+Shift+M
\n'<\/code><\/p>\n

'Create a new Workbook Containing 1 Sheet (right most) and sends as attachment.<\/code><\/p>\n

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Copy<\/code><\/p>\n

With ActiveWorkbook<\/code><\/p>\n

.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\")<\/code><\/p>\n

.Close SaveChanges:=False<\/code><\/p>\n

End With<\/code><\/p>\n

End Sub<\/code><\/p>\n

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.\u00a0 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<\/em>, employee number<\/em>, claim date<\/em>, claim value<\/em>” before sending the mail.\u00a0 Simple really.<\/p>\n

Here are a few links that helped me out in doing this:<\/p>\n