2 Methods to Quickly Send Selected Cells in an Excel Worksheet as an Outlook Email

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

1. To start with, open the source Excel file and access the right Excel worksheet.
2. Then select the cells which you want to send as an Outlook email.
3. Next press “Ctrl + C” key shortcuts to copy the selected cells.
4. After that, launch your Outlook application.
5. Subsequently, click on the “New Email” button in the “Home” ribbon to create a new Outlook email.
6. Later in the new Message window, click into the message body section.
7. And then press “Ctrl + V” key buttons to paste the previously copied selected Excel cells.
8. 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

1. At the very outset, open the source Excel workbook.
2. Then press “Alt + F11” key buttons to access Excel VBA editor.
3. In the new window, click “Insert” > “Module” to insert a new module.
4. Next should enable Microsoft Outlook Object. Click “Tools” > “References”. In the popup dialog box, find and check the “Microsoft Outlook Object Library”.
5. 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

6. 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