In this post, I won’t go through a step by step guide on how sending emails using Excel is possible using macros and VBA options in Microsoft Excel.
This is because the spreadsheet template I would like to share was created for a very specific purpose, and is unlikely to be useful to the average blog reader.
What I’d like to do instead is give you an example of why and how I needed to send out emails (with attachments) using Excel. This could perhaps be an inspiration for you to make some process with your workflow or colleagues more efficient.
I learnt the basic formula from the template from this YouTube video below.
I’ll explain how I used the template detailed in the video above, and used it to accomplish the task I needed to automate.
What I needed the macro to do for me
What I needed to do was the following.
From a database, I could download separate sales reports for each country. I can also download the data (which contains website visitors, leads and sales) into one table in Excel.
Below is the table with the data (the data currently in there is all random numbers generated by the formula =RANDBETWEEN()).
I needed a way to copy and paste the table into an Excel sheet, which would then automatically create unique emails for 23 different Sales Offices.
Each unique email would need to summarize the performance of the Sales Office and have a unique email attachment.
I ended up with an email template below.
I then created a VBA macro in Excel. The Macro text is below.
Sub Send_email_fromexcel() Dim edress As String Dim subj As String Dim message As String Dim filename As String Dim outlookapp As Object Dim outlookmailitem As Object Dim myAttachments As Object Dim path As String Dim lastcol As Integer Dim attachment As String Dim x As Integer x = 2 Do While Sheet7.Cells(2, x) <> "" Set outlookapp = CreateObject("Outlook.Application") Set outlookmailitem = outlookapp.createitem(0) Set myAttachments = outlookmailitem.Attachments path = Sheet7.Cells(5, x) edress = Sheet7.Cells(2, x) cc = Sheet7.Cells(3, x) subj = Sheet7.Cells(4, x) filename = Sheet7.Cells(6, x) attachment = path + filename outlookmailitem.to = edress outlookmailitem.cc = cc outlookmailitem.bcc = "" outlookmailitem.Subject = subj outlookmailitem.body = Sheet7.Cells(7, x) myAttachments.Add (attachment) outlookmailitem.display outlookmailitem.send lastcol = lastcol + 1 edress = "" x = x + 1 Loop Set outlookapp = Nothing Set outlookmailitem = Nothing End Sub
I’ve provided the Excel file template if you’d like to experiment and practice using the file.
Unfortunately I’ve had to remove the macro from the file, as I’m unable to upload a .xlsm file to this WordPress site.
Download Excel file template
Download the template file by clicking on the link below:
Hope this post was useful for those interested in automating some tasks involving Excel and Outlook!
My Other Blog Posts
If you enjoyed this post you might enjoy these other blog posts I’ve written.