Paste Excel range into Outlook

Question

I'm upgrading an Excel macro. I want to generate an email copying in a table that changes range daily.

Strbody populates the email but the timetable isn't attaching.

Sub Ops_button()
'Working in Office 2000-2010
Dim Outapp As Object
Dim Outmail As Object
Dim Strbody As String
Dim Timetable As String

'Auto Email Attachment Variables
Set Outapp = CreateObject("Outlook.Application")
Set Outmail = Outapp.createitem(0)
Timetable = Sheets("sheet1").Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
Strbody = "body text."

On Error Resume Next
With Outmail

    'Send email
    .To = ""
    .bcc = ""
    .Subject = "Report" & " " & Format$(Date, "dd-mm-yyyy")
    .body = Strbody & Timetable

    On Error Resume Next
    .Display
End With

On Error GoTo 0
Set Outmail = Nothing
Set Outapp = Nothing

End Sub

Yup. I want the spreadsheet to load under the text. I add or remove rows each day so I want it to adjust accordingly. — Flyside, Jul 21 '15 at 03:00

score 0 · Answer 1 · edited May 23 '17 at 12:21

You can't do this the way you're trying to do it... Let's see why not :)

You've declared Timetable as a String type variable. In this statement, you're assigning its value as the return from the .Select method (which will return a value of True if there is no error).

Timetable = Sheets("sheet1").Range("C2").Select

So, you're in no way appending the Table's Range object to the string, in this statement:

.body = Strbody & Timetable

Instead, you really need to either convert the table to HTML or copy and paste the range directly from Excel to Word.

Use Ron de Bruin's function to convert the table to an HTML PublishObject and insert that to the email, or
.Display the MailItem and then get a handle on the MailItem's .Inspector object (which is really just a Word document)

For the solution 1, adapt the answer already given, here:

Paste specific excel range in outlook

For the solution 2, you'll need to use the method outlined here to get the Inspector (Word Document representing the Email item):

https://msdn.microsoft.com/en-us/library/office/ff868098.aspx

Then, Dim TimeTable as Range, and change code to:

Set Timetable = Sheets("sheet1").Range("C2").End(xlToRight).End(xlDown)

Then, copy the table:

Timetable.Copy

And then following the MSDN link above once you have a handle on the Inspector, get the destination range in Outlook (Word) and you can use the PasteAndFormat method of a Word.Range object:

Dim wdRange as Object 'Word.Range
OutMail.Display

Set wdRange = OutMail.getInspector().WordEditor.Range
wdRange.Text = strBody
wdRange.Expand (1)
wdRange.Characters.Last.PasteAndFormat 16  'wdFormatOriginalFormatting

Option 2 would be my preferred method. I'm on a computer that doesn't have outlook, so I'm winging this a little bit from memory and I can't test right now, but if you have any issues with it just leave a comment and I'll try to help out some more in the morning.

Thanks David. I'll have a crack at it this weekend as works been a bit too hectic to give it a shot. — Flyside, Jul 23 '15 at 03:27

Paste Excel range into Outlook

1 Answers1