At times, you may want to quickly insert the selected cells in an Excel worksheet to an Outlook email and send out this email. This article will look at this wish to offer you 2 methods.
My previous article “2 Quick Methods to Send an Excel Worksheet as an Outlook Email” has taught you how to send a whole Excel worksheet as an email. However, sometimes, you only wish to send the selected cells in a worksheet as an email. In response to this requirement, here we will expose you 2 methods. You can choose either as per your preference.
Method 1: Copy Selected Cells to Email Manually
- To start with, open the source Excel file and access the right Excel worksheet.
- Then select the cells which you want to send as an Outlook email.
- Next press “Ctrl + C” key shortcuts to copy the selected cells.
- After that, launch your Outlook application.
- Subsequently, click on the “New Email” button in the “Home” ribbon to create a new Outlook email.
- Later in the new Message window, click into the message body section.
- And then press “Ctrl + V” key buttons to paste the previously copied selected Excel cells.
- Lastly, you can compose the email as usual and click “Send” button to send it out.
Method 2: Send Selected Cells as Email via VBA
- At the very outset, open the source Excel workbook.
- Then press “Alt + F11” key buttons to access Excel VBA editor.
- In the new window, click “Insert” > “Module” to insert a new module.
- Next should enable Microsoft Outlook Object. Click “Tools” > “References”. In the popup dialog box, find and check the “Microsoft Outlook Object Library”.
- Subsequently, copy the following VBA code into this module.
Sub SendSelectedCells_inOutlookEmail() Dim objSelection As Excel.Range Dim objTempWorkbook As Excel.Workbook Dim objTempWorksheet As Excel.Worksheet Dim strTempHTMLFile As String Dim objTempHTMLFile As Object Dim objFileSystem As Object Dim objTextStream As Object Dim objOutlookApp As Outlook.Application Dim objNewEmail As Outlook.MailItem 'Copy the selection Set objSelection = Selection Selection.Copy 'Paste the copied selected ranges into a temp worksheet Set objTempWorkbook = Excel.Application.Workbooks.Add(1) Set objTempWorksheet = objTempWorkbook.Sheets(1) 'Keep the values, column widths and formats in pasting With objTempWorksheet.Cells(1) .PasteSpecial xlPasteValues .PasteSpecial xlPasteColumnWidths .PasteSpecial xlPasteFormats End With 'Save the temp worksheet as a HTML file Set objFileSystem = CreateObject("Scripting.FileSystemObject") strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm" Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address) objTempHTMLFile.Publish (True) 'Create a new email Set objOutlookApp = CreateObject("Outlook.Application") Set objNewEmail = objOutlookApp.CreateItem(olMailItem) 'Read the HTML file data and insert into the email body Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile) objNewEmail.HTMLBody = objTextStream.ReadAll objNewEmail.Display 'You can specify the new email recipients, subjects here using the following lines: 'objNewEmail.To = "johnsmith@datanumen.com" 'objNewEmail.Subject = "DataNumen Products" 'objNewEmail.Send --> directly send out this email objTextStream.Close objTempWorkbook.Close (False) objFileSystem.DeleteFile (strTempHTMLFile) End Sub
- Eventually, you can have a try.
- Firstly, select the specific cells as usual.
- Then back to the new macro and press F5 key to trigger it.
- At once, a new Outlook email will display, in which you can see that the selected cells have been inserted into the email body, like the following screenshot:
PST File Is Same Vulnerable as Excel File
As we know, Excel file and Word document are prone to corruption. Actually, the same holds true for PST file, which contains all items in your Outlook mailbox. So, it is an arduous task to secure your PST data. One of the most effective measures is to back up the file regularly. Plus, so as to provide immediate rescue, you had better prepare an experienced tool, such as DataNumen Outlook Repair, which is able to recover Outlook data without breaking a sweat.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair mdf and outlook repair software products. For more information visit www.datanumen.com
Great! Thanks a lot for the content. It helpme a lot.
Sub SendSelectedCells_inOutlookEmail()
Dim objSelection As Excel.Range
Dim objTempWorkbook As Excel.Workbook
Dim objTempWorksheet As Excel.Worksheet
Dim strTempHTMLFile As String
Dim objTempHTMLFile As Object
Dim objFileSystem As Object
Dim objTextStream As Object
Dim objOutlookApp As Outlook.Application
Dim objNewEmail As Outlook.MailItem
‘Copy the selection
Set objSelection = Selection
Selection.Copy
‘Paste the copied selected ranges into a temp worksheet
Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
Set objTempWorksheet = objTempWorkbook.Sheets(1)
‘Keep the values, column widths and formats in pasting
With objTempWorksheet.Cells(1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
End With
‘Save the temp worksheet as a HTML file
Set objFileSystem = CreateObject(“Scripting.FileSystemObject”)
strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & “\Temp for Excel” & Format(Now, “YYYY-MM-DD hh-mm-ss”) & “.htm”
Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address)
objTempHTMLFile.Publish (True)
‘Create a new email
Set objOutlookApp = CreateObject(“Outlook.Application”)
Set objNewEmail = objOutlookApp.CreateItem(olMailItem)
‘Read the HTML file data and insert into the email body
Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
objNewEmail.HTMLBody = objTextStream.ReadAll
objNewEmail.HTMLBody = Replace(objNewEmail.HTMLBody, “align=center x:publishsource=”, _
“align=left x:publishsource=”)
objNewEmail.Display
‘You can specify the new email recipients, subjects here using the following lines:
‘objNewEmail.To = “johnsmith@datanumen.com”
‘objNewEmail.Subject = “DataNumen Products”
‘objNewEmail.Send –> directly send out this email
objTextStream.Close
objTempWorkbook.Close (False)
objFileSystem.DeleteFile (strTempHTMLFile)
End Sub
This code is working great! Many, many thanks.
Could you help me to modify it a little? A should send a selected range of cells every time, for instance cell range A1:C21. How I need to modify the code? I tried to change the ojb selectio to a range, but it doesn’t work.
Another question: current code alligns the pasted table in the email body in the middle. Is it possible to allign it on the left?
Thanks again.
Massimo