Here is a method for how to send Emails using a VBA macro in Excel. 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.