Create an outlook task with a range from excel in the body of the task

[Firesword]

Hi Everyone

My first post, I'm new to VBA but have got a lot of help from this site from articules already posted, but this is the first time I cannot find what I'm looking for hence the post.
I'm using Winows 10 64bit with office 2016. I have an excel file that I'd like to create a task from, my code is below. The bit I cannot get to work is the .body which is a table on sheet2 it's a varialbe range which is why I went down the route of a table. But I cannot add this to the body of the task. I can add a string but not the table. I have no preference to how this is done ie if the range is copied to a picture and pasted into the body or anyother way.
Any help would be greatly appreciated.
Thanks
Simon

Sub NewTask()

Dim rng As Range

Set rng = [Table2[#All]]

With CreateObject("Outlook.Application").CreateItem(3)
.Subject = "Stock Requisition Request"
.StartDate = Now
.DueDate = Now
.ReminderSet = True
.Assign
.Recipients.Add Sheets(Sheet4.Name).Cells(1, 1)
.Body = [Table2[#All]]
.Display
End With

End Sub

PS I did read the FAQ and I hope I've posted this correctly

 

[Coding4Fun]

When you post your code you want to put the code inside code tags just FYI. [ CODE ] [ / CODE ]

Sub NewTask()


Dim rng As Range
Dim outlook As Object
Dim myEmail As Object
Dim xIns As Object
Dim pEd As Object


Set outlook = CreateObject("Outlook.Application")
Set myEmail = outlook.CreateItem(3)


Set rng = [Table2[#All]]


With myEmail


    .Subject = "Stock Requisition Request"
    .StartDate = Now
    .DueDate = Now
    .ReminderSet = True
    .Assign
    .Recipients.Add Sheets(Sheet4.Name).Cells(1, 1)
    .Body = "Opening Message:"
    .display
    
    Set xIns = myEmail.GetInspector
    Set pEd = xIns.WordEditor
    
    rng.Copy
    
    pEd.Application.Selection.Start = Len(.Body)
    pEd.Application.Selection.End = pEd.Application.Selection.Start
    pEd.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display


End With


Set xIns = Nothing
Set pEd = Nothing


Set outlook = Nothing
Set myEmail = Nothing


Set rng = Nothing


End Sub

 

[Firesword]

Thank you for such a quick reply and for the code. TBH I don't understand it, but it does work. . If I may ask though, the data does not align to the headings, is there a way to format the data so it aligns to the headings?

 

[Firesword]

Hi Coding4Fun

After a lot of searching I have found the formating part that I need I have only changed one bit of your code from

    pEd.Application.Selection.PasteAndFormat (wdFormatPlainText)

To

    pEd.Application.Selection.PasteAndFormat (wdFormatOriginalFormatting)

Again I could not have done this without your help. Thank you.