How to send HTML emails from Excel via Outlook

Sending emails from Excel is a great way to automate and become more efficient

There are several reasons why you might want to send emails from Excel.

• To automate your email marketing campaign. If you have a large list of subscribers you can automate your marketing campaigns

• You can personalize your emails by inserting each recipient's name, address and other information in the email body.

• Send out emails for newsletters, announcements and other types of mass email

In this post we will explain how to send emails via Excel in HTML format

Sending emails in HTML format makes email more visually appealing and allows for inserting images, hyperlinks and other types of rich media

The key steps are the following 

1. Define the email object application and related code to send emails

2. Convert to HTML the excel range to be sent 

3. Send the email

1. Define the email object application and the code to send emails

The first step is to give the routine a name and declare variables to store various objects and values that will be used in the code:

• Sub SendEmailfromExcel() is the name of the routine 

•   rng,OutApp, OutMail are the variables that will store references to Excel range and Outlook objects

• Set rng = Nothing The statement ensures that the rng variable is set to empty

• Set rng = Selection.SpecialCells(xlCellTypeVisible) The currently selected range of the active sheet is allocated to the variable rng. The SpecialCells method ensures that only the visible cells of the selected range will be sent via email

• With Application... The standard spreadsheet settings of EnableEvents and ScreenUpdating are switched off in order to increase the speed of the code.  

• Set OutApp = Create Object("OUtlook.Applicaiton") The object representing the outlook aplication is created 

• Set OutMail = OutApp.CreateItem(0) The object representing the email to be sent is created

• With OutMail The different fields of the email object are populated with the necessary information 

  • .To = "xyza@gmail.com" Email address to whom the email is addressed 

  • .cc = "" Insert in quotes email address of people to be copied on the email

  • .Subject = "Mail from Excel" Replace with any text for the email sibject

  • .HTMLBody = HTMLFormat (rng) The  range containing the text to be sent is converted to HTML format using the function HTMLFormat. Therefore this line of code calls the function which is written following the current routine 

Sub SendEmailfromExcel()

Dim rng As Range

Dim OutApp As Object

Dim  OutMail As Object

Set rng = Nothing 

Set rng =Sheets("ToMail").SpecialCells(xlCellTypeVisible)

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

With OutMail

.to = "xyza@gmail.com"

.cc = ""

.subject = "Mail from Excel"

.HTMLBody = HTMLFormat ( rng)

2. Convert to HTML the excel range to be sent

The range in the spreadsheet containing the text that will represent the body of the email is converted in HTML. 

The function starts by defining the variables 

• 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" The string represents the path of an HTML file on the user's temporary folder. The Format(Now, "dd-mm-yy h-mm-ss") function formats the current date and time as a string in the specified format

• rng.copy The selected range of the spreadsheet is copied 

• Set TempWB = Workbooks.Add(1) A new workbook is created

• The range is copied in the new workbook

  • .Cells(1).PasteSpecial Paste:=8 The column width is copied

  • .Cells(1);PasteSpecial xlPasteValues, , False,False The values of the original range are copied 

  • .Cells(1);PasteSpecial xlPasteFormats, , False,False The formats of the original range are copied 

• On Error Resume Next This line avoids code break in case of errors

• In case the original range in excel contains objects there are deleted 

  • .DrawingObjects.Visible = True

  • .DrawingObjects.Delete

• On Error GoTo 0 The error handling solution is ceased. 

Function HTMLFormat (rng as Range)

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"

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

A temporary excel workbook containing the data reported in the original range is now available 

The next step is to copy the sheet to a HTML file. The PublishObjects property of the workbook achieves this result

• With TempWB.PublishObjects.Add Workbooks have a PublishObjects property that allows to add item to the PublishObjects Collection in which are represented all the items published to the web.

  • SourceType:=xlSourceRange Indicates that the item to publish is a range of cells

  • Filename:=TempFile The path were the source data needs to be saved It can also represent a WEB URL 

  • Sheet:=TempWB.Sheets(1).Name The name of the worksheet to be saved The Name property of the worksheet is used to identify the name

  • Source:=TempWB.Sheets(1).UsedRange.Address The address of the range of cells to be published

  • HtmlType:=xlHtmlStatic  Indicates that the data will be published in a static format. That is to say that it will not be subject to future updates

  • .Publish (True) statement publishes the data. The True argument specifies that the existing HTML file should be overwritten if it already exists.

  • End With The statement deactivates the PublishObjects collection of the temporary workbook

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

The data to be emailed is now contained in an HTML file Next step is to have the data reported in the variable HTMLFormat in order for the main routine to embed it in the body of the email

• Set fso = CreateObject("Scripting.FileSystemObject") statement creates a new instance of the FileSystemObject object. The FileSystemObject object provides access to the computer's file system.

• Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) The HTML file specified by the TempFile variable is opened as a text stream. The 1 argument specifies that the text stream should be opened for reading. The -2 argument specifies that the text stream should be opened in binary mode.

• RangetoHTML = ts.ReadAll  The entire contents of the HTML file is read into the RangetoHTML variable

• ts.Close The text stream is closed

• RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")All instances of the align=center x:publishsource= attribute are replaced with the align=left x:publishsource= attribute.

• TempWB.Close savechanges:=False The temporary workbook and changes are not saved

• Kill TempFile The temporaryfile is deleted

• All the variables are deleted

  • Set ts = Nothing

  • Set fso = Nothing

  • Set TempWB = Nothing

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=")

TempWB.Close savechanges:=False

Kill TempFile

Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

 End Function

3. Send the email

The email text to be sent in now in the email body. The routine is completed by sending the email

• .Send Is the instruction that indicates to send the email 

• .EndWith Ends the code related to the OutMail object

• The EnableEvents and ScreenUpating functionalities are bought back to their original setting

  • With Application

    • .EnableEvents = True

    • .ScreenUpdating = True

  • End With 

• Set OutMail = NOthing The mail object is cancelled 

• Set OutApp = Nothing The Outlook object is cancelled

.Send 

End With

With Application

.EnableEvents = True 

.ScreenUpdating = True 

End With 

Set OutMail = Nothing 

Set OutApp = Nothing 

End Sub 

The full code is reported below to be copied in the Excel VBA module

Sub SendEmailfromExcel()

Dim rng As Range

Dim OutApp As Object

Dim  OutMail As Object

Set rng = Nothing 

Set rng =Sheets("ToMail").SpecialCells(xlCellTypeVisible)

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

With OutMail

.to = "xyza@gmail.com"

.cc = ""

.subject = "Mail from Excel"

.HTMLBody = HTMLFormat ( rng)

      .Send 

End With

With Application

.EnableEvents = True 

.ScreenUpdating = True 

End With 

Set OutMail = Nothing 

Set OutApp = Nothing 

End Sub 

Function HTMLFormat (rng as Range)

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"

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

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

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=")

TempWB.Close savechanges:=False

Kill TempFile

Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

 End Function