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!
1. Copy In VBA Code
2. Adjust Range & Send To
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:
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
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("C1:G13").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 = "email@example.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 = "firstname.lastname@example.org" .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
Here is Outlook:
Here is Gmail:
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:
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. View the Google Sheet Email Tutorial.
Download the workbook and code below!