How to send email with copying and pasting a specified range into email body in Excel?
In many cases, a specified range of contents in Excel worksheet may be useful in your email communication. In this article, we will introduce a method of sending an email with specified range pasting into email body directly in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following VBA code can help you to copy a range and paste it into an email body directly in Excel. Please do as follows.
1. In the worksheet contains the range you need to copy, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the opening Microsoft Visual Basic for Applications window, please click Tools > References as below screenshot shown.
3. In the References – VBAProject dialog box, please find and check the Microsoft Outlook Object Library option, and then click the OK button.
4. Click Insert > Module, then copy and paste the below VBA code into the Module window.
VBA code: Send email with a specified range pasted into email body in Excel
Sub Send_Email() Dim xRg As Range Dim I, J As Long Dim xAddress As String Dim xEmailBody As String Dim xMailOut As Outlook.MailItem Dim xOutApp As Outlook.Application On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select range you need to paste into email body", "KuTools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False Set xOutApp = CreateObject("Outlook.Application") Set xMailOut = xOutApp.CreateItem(olMailItem) For I = 1 To xRg.Rows.Count For J = 1 To xRg.Columns.Count xEmailBody = xEmailBody & " " & xRg.Cells(I, J).value Next xEmailBody = xEmailBody & vbNewLine Next xEmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & xEmailBody & vbNewLine With xMailOut .Subject = "Test" .To = "email@example.com" .Body = xEmailBody .Display '.Send End With Set xMailOut = Nothing Set xOutApp = Nothing Application.ScreenUpdating = True End Sub
1). Please change the email body in line xEmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & xEmailBody & vbNewLine as you need.
2). Please specify your email recipient and subject .To = firstname.lastname@example.org and .Subject = "test" lines in the code.
5. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select the range you need to paste in the email body, and then click the OK button. See screenshot:
6. Now an email is created with specified recipient, subject, body and selected Excel range, please click the Send button to send this email. See screenshot shown.
Note: The VBA code is only working when you use Outlook as your email program.
- How to send email to email addresses specified in cells in Excel?
- How to send email with multiple attachments attached in Excel?
- How to insert signature into Outlook email when sending by vba in Excel?
- How to send email if due date has been met in Excel?
- How to automatically send email based on cell value in Excel?
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 11 days agothis is pasting as a text. Kindly suggest how to send the table or the same format which is copied from the excel.
To post as a guest, your comment is unpublished.· 1 months agoCode to send automatically after selecting after ok
To post as a guest, your comment is unpublished.· 3 months agoHi
This code is vary excellent, by using the code i have completed my 90% of my project.
I have same issue as mentioned by Anirudh that is table formatting. How can i format the table in email.
Please help me......
To post as a guest, your comment is unpublished.· 3 months agoGood Day,
The problem can't be solved yet. Sorry for the inconvenience and thank you for your comment.
To post as a guest, your comment is unpublished.· 29 days agoHi, Is there any update on below.......
To post as a guest, your comment is unpublished.· 29 days agoHi, Also I Wanted code for "Filter by Date".
I am working on project, on that project I wanted to filter the data by the date, actually we have filter/hide the and last 05 days to current date data and we have highlight all other data.
Please help me to complete this project.
Your help is very great-full for me.
To post as a guest, your comment is unpublished.· 7 months agoThis is great. It is working as expected. The only issue is that the format of the table is not maintained in the mail. Can you let me know how do we preserve the format of the table in the mail
To post as a guest, your comment is unpublished.· 8 months agoThis code is great, but I need to execute with a command button rather than pressing F5 in code view. I'd like my employees to be able to fill out some info, then hit the button and have it copy the range of cells they completed and paste into email. I can get code working for the email button and separately for the copy/paste of the range of cells, but can't seem to combine both functions. Please help!!! Thanks!