While working in Excel, you might send an email from Excel keeping a simple text, copied cell range, or even a table from a working sheet. In this guiding session, I’ll show you 3 cases to send an email from Excel with the body using Macro.
Download Practice Workbook
3 Cases to Send Email from Excel with Body Using Macro
Let’s introduce today’s dataset where the Sales Report of Fruits is given along with the necessary information e.g. Product ID, Fruits, and so on.
Now, you’ll see the process of sending an email from Excel keeping the above dataset in the email body. Before doing that, let’s see the method to send an email with a body and an attachment.
1. Send Email from Excel with Body and Attachment
Suppose, you have a PDF file and you want to send it with an email body describing the file briefly. In such a situation, you may use this method. Here, I’m showing the complete code without any splitting. However, you may visit a step-by-step guide to send an email from Excel with a body to understand clearly.
So, please do the following tasks to use this method.
➜ Firstly, open a module by clicking Developer > Visual Basic (or press ALT + F11).
➜ Secondly, go to Insert > Module.
➜ Then, insert the complete code.
Sub Send_Email() 'Declare Variables Dim EmailApp As Object Dim EmailItem As Object Dim myAttachments As Object 'Set Variables Set EmailApp = CreateObject("Outlook.application") Set EmailItem = EmailApp.CreateItem(0) Set myAttachments = EmailItem.Attachments 'Specify Email Items and Add Attachment With EmailItem .To = "[email protected]" .Subject = "Sales Report of Fruits" .Body = "Hi Brown," & vbNewLine & _ "I'm sharing the Sales Report of our company. Please find the PDF attachment." & _ vbNewLine & "Regards," & vbNewLine & "Williams" .Attachments.Add "E:\Exceldemy\Sales Report March.pdf" '.send .Display End With Set EmailItem = Nothing Set EmailApp = Nothing End Sub
⧬ Code Explanation:
I declared and set the necessary variables for sending the email automatically first. Then, I assigned the necessary email items e.g. the email address, subject, and email body using the MailItem object. In the email body, I used vbNewLine to insert the new line for splitting the body into salutation, main body, and closing remarks. Lastly, I used the Attachments.Add method to add the published PDF (the file path is “E:\Exceldemy\Sales Report March.pdf“) document and close the statement by setting the EmaiItem and EmailApp as Nothing.
After running the code (keyboard shortcut is F5), you’ll get the following output.
2. Send Email with Selected Cell Range into Body
What if you can keep a specified cell range (e.g. B4:F15) from Excel in the email body automatically?
Fortunately, you can do this using the following code.
Sub Send_Email_Range_in_Body() Dim cRng As Range Dim xRow, xCol As Long Dim xAddress As String Dim mBody As String Dim EmailItem As Object Dim EmailApp As Object On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set cRng = Application.InputBox("Define the cell range", "Sending Email", _ xAddress, , , , , 8) If cRng Is Nothing Then Exit Sub Application.ScreenUpdating = False Set EmailApp = CreateObject("Outlook.Application") Set EmailItem = EmailApp.CreateItem(olMailItem) For xRow = 1 To cRng.Rows.Count For xCol = 1 To cRng.Columns.Count mBody = mBody & " " & cRng.Cells(xRow, xCol).Value Next mBody = mBody & vbNewLine Next mBody = "Hi Brown," & vbNewLine & _ "I'm sharing the Sales Report of our company." & _ vbNewLine & "Regards," & vbNewLine & "Williams" & vbNewLine & mBody & vbNewLine With EmailItem .Subject = "Sales Report of Fruits" .To = "[email protected]" .Body = mBody .Display '.Send End With Set EmailItem = Nothing Set EmailApp = Nothing Application.ScreenUpdating = True End Sub
⧬ Code Explanation:
After declaring variables, I used InputBox to assign the cell range. Then I utilized the For…Next statement to count the row and column numbers. Meanwhile, I applied the Cells property with the Value to keep the data from the specified cell range in the email body. And the rest things are almost the same as shown in the first method.
So, if you run the code, you’ll get a dialog box to specify the cell range.
After doing that, you’ll get the selected cell range in the email body.
3. Send Email from Excel Keeping Table in Body
Again, how would you feel if your existing dataset return as a table in the email body using a single command in Macro?
For example, I select the B4:F15 cells and I want to keep the dataset in the email body as a table without changing the specific formatting.
Now, copy the following code.
Option Explicit Sub Sending_Range_Email() Dim xRg As Range Dim EmailApp As Object Dim EmailItem As Object Set xRg = Nothing On Error Resume Next Set xRg = Selection.SpecialCells(xlCellTypeVisible) On Error GoTo 0 With Application .EnableEvents = False .ScreenUpdating = False End With Set EmailApp = CreateObject("Outlook.Application") Set EmailItem = EmailApp.CreateItem(0) On Error Resume Next With EmailItem .To = "[email protected]" .Subject = "Sales Report of Fruits" .HTMLBody = RgToHTML(xRg) .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set EmailItem = Nothing Set EmailApp = Nothing End Sub
⧬ Code Explanation:
Here, I employed the SpecialCells method where xlCellTypeVisible is the XlCellType to select the visible cells (B4:F15). Later, I run the Application.EnableEvents property to take place the event. Similarly, I utilized the Application.ScreenUpdating property to speed up the VBA code (these two properties work when I assigned True).
More importantly, I used a function namely RgToHTML in the above code. You can define the function utilizing the following code to convert the dataset from Excel to HTML format in the email body.
Function RgToHTML(xRg As Range) Dim xS, yS As Object Dim tF As String Dim tBook As Workbook tF = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" xRg.Copy Set tBook = Workbooks.Add(1) With tBook.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 tBook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=tF, _ Sheet:=tBook.Sheets(1).Name, _ Source:=tBook.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set xS = CreateObject("Scripting.FileSystemObject") Set yS = xS.GetFile(tF).OpenAsTextStream(1, -2) RgToHTML = yS.readall yS.Close RgToHTML = Replace(RgToHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") tBook.Close savechanges:=False Set yS = Nothing Set xS = Nothing Set TempWB = Nothing End Function
If you run the code, you’ll get that the following table has been sent automatically.
Things to Remember
While using the above code, don’t forget to change the following things.
- Filename: Firstly, specify the attached file name based on your requirement e.g. E:\Exceldemy\Sales Report March.pdf
- Email address: Enter the suitable email address.
- Email subject line: Don’t forget to add an attractive subject e.g. Sales Report of Fruits.
- Email body: Describe briefly the attachment
That’s the end of today’s session. This is how you can send from Excel with Body using Macro. I hope this article would be highly beneficial for you. Anyway, don’t forget to share your thoughts.