Copy EXCEL Range to E-mail BODY

[gmayor]

Personally I would do it like this

Option Explicit

Sub SendWorkBook()
'Graham Mayor - http://www.gmayor.com - Last updated - 26 Nov 2017
'This macro requires the code from
'http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to open Outlook

Dim oOutlookApp As Object
Dim oItem As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim xlRng As Range
Set xlRng = Range("$I$22:$M$36")    'The range to be copied
xlRng.Copy    'Copy it

Set oOutlookApp = OutlookApp()    'Use the function from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to open Outlook, or it will not work correctly

    'Create a new mailitem
    Set oItem = oOutlookApp.createitem(0)
    With oItem
        .BodyFormat = 2    'html
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor    'access the message body for editing
        Set oRng = wdDoc.Range
        oRng.collapse 1    'set a range to the start of the message
        oRng.Text = "This is the message body before the Excel range:" & vbCr & vbCr
        'Collapse the range to its end
        oRng.collapse 0
        oRng.Text = vbCr & "This is the text after the Excel range."
        'The range will be followed by the signature associated with the mail account
        'collapse the range to its start
        oRng.collapse 1
        'paste the excel range in the message
        oRng.Paste
        'Address the message
        .To = "someone@somewhere.com"
        'Give it a title
        .Subject = "This is the subject"
        'attach the workbook
        .attachments.Add ActiveWorkbook.FullName
        'display the message - this line is required even if you then add the command to send the message
        .display
    End With

    'Clean up
    Set oItem = Nothing
    Set oOutlookApp = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set xlRng = Nothing
lbl_Exit:
    Exit Sub
End Sub