Send Emails Automatically - Uses Excel Macro and Task Scheduler

Sick of emailing a report every day? Automate it using the following Macro, which emails a specific range! 

Be sure to check out our Macro Scheduling Tutorial beforehand, if you want to automatically schedule the macro to run. Else it is as simple as a button click!

 

Steps

1. Copy In VBA Code

2. Adjust Range & Send To

3. Done

 

 

1. Setup

This macro simply copies a specified range over to Outlook, then hits the send button. Automating what you would normally do, if you wanted to send a table of data. 
I originally used a macro that took screen shots, and attached that to the email. However I had problems where the images didn't display correctly, due to security settings on our companies server. The technique below is easier, and gets around that issue. 

Just make sure you have Outlook working and set up. If you were hoping to use Gmail, fear not as I have also got this working using Google Sheets. I will provide a tutorial on this shortly. 

 

2. Building The Sheet

1. Create Excel Workbook
Call it "emailtest" without the quotes. I avoid spaces for simplicity when updating the code. Macro Enabled of course. 

Now add in a small table to test out the macro, here is my artwork:

Auto_Email_Data_Table.png

 

Note how I turned off Gridlines - simply to help with my design. You don't need to do this.
To follow along, just make sure your design is in D2:F12.

 

3. Adding In The Code

1. Open up VBA and Insert a Module

insert_module.png

 

2. Copy In The Following RangetoHTML Code:

Function RangetoHTML(rng As Range)
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

This converts your range into HTML, which keeps the pretty formatting and ensures formulas don't mess up on the email . 

3. Copy In The Following AutoEmail Code:

Same Module, directly under the "End Sub" of the previous code. 

Sub AutoEmail()
' You need to use this module with the RangetoHTML subroutine.
' Working in Office 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    ' Only send the visible cells in the selection.
    Set rng = Sheets("Sheet1").Range("D2:F12").SpecialCells(xlCellTypeVisible) ' Change this

    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "youremailhere@gmail.com" ' Change this to the email addresses you want to send to
        .CC = ""
        .BCC = ""
        .Subject = "Daily Budget Update" ' Add in a subject
        .HTMLBody = RangetoHTML(rng)
        ' In place of the following statement, you can use ".Display" to
        ' display the e-mail message.
        .Send
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Three things you need to change. 

Set rng = Sheets("Sheet1").Range("D2:F12").SpecialCells(xlCellTypeVisible)
.To = "youremailhere@gmail.com"
.Subject = "Daily Budget Update"

Update those where appropriate. 

 

4. Ready To Send!

Easy as that. You are now ready to test it out. 

1. Save your workbook and close VBA. 

2. Open Outlook.

3. Run the AutoEmail

run_auto_email.png

 

Here is Outlook:

auto_email_outlook.png

Here is Gmail:

Auto_email_gmail.png

5. Looks Messy?

If you look on your phone, the above example will look messy. But just keep playing around with row/column widths to adjust. 
Get rid of borders, conditional formatting etc. Another trick is to increase the range you are coding it to send, for example create some white space around it. 

    Set rng = Sheets("Sheet1").Range("C1:G13").SpecialCells(xlCellTypeVisible)  instead of Set rng = Sheets("Sheet1").Range("D2:F12").SpecialCells(xlCellTypeVisible)

You can get it looking decent (but never perfect) if you have the patience. The following is the best I have managed on my Android Phone using the Outlook app:

email_format_excel.png

 

6. That's it

This is the easiest auto email macro I have come across. Once you are happy with the formatting, change the Sub AutoEmail() to Auto_Open() and use the Macro Scheduler Tutorial to start automating emails!

If you still can't get the formatting right, think about creating a PDF and attaching that to your auto email instead. Read my Email PDF Excel Tutorial to find out how. 

If you are thinking of automating emails, I recommend to transition to Google Sheets if possible. This makes it easier to schedule them, and I have an easier time making the formatting look nice. Check back for a tutorial on this.

Download the workbook and code below!

 

7. Download

Name
Name
Helps me to design future templates that you will find relevant
Let me know if you need help, and I would be thrilled to learn what you are using this template for!

More templates