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 = "email@example.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.
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