Macro to Send Email from Excel with Body (3 Useful Cases)

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.

Read More: How to Apply Macro to Send Email from Excel with Attachment

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.

Read More: Macro to Send Email from Excel with Body (3 Useful Cases)

Similar Readings

• How to Send Bulk Email from Outlook Using Excel (3 Ways)
• How to See Who Is in a Shared Excel File (With Quick Steps)
• Enable Share Workbook in Excel
• How to Share Excel File Online (2 Easy Methods)
• How to Share Excel File for Multiple Users

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?

Sounds interesting.

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.

Read More: How to Send Email from Excel with Body Using a Macro (with Easy Steps)

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

Conclusion

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.

Related Articles

• How to Send Email If Conditions Met in Excel (3 Easy Methods)
• How to Send Automatic Email from Excel to Outlook (4 Methods)
• Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
• Macro to Send Email from Excel (5 Suitable Examples)
• Send Reminder Email Automatically from an Excel Worksheet Using VBA
• How to Send Email Automatically When Condition Met in Excel