VBA copy visible cells to email body below text

[Number1One]

Hi All,

Have the below code, almost there, but body text is currently going below the copied table and also want to add a signature.

Code- to copy visible cells in range into email body.

Sub SendEmail()


Dim OutlookApp As Object
'Dim OutlookApp As Outlook.Application
Dim MItem As Object
'Dim MItem As Outlook.MailItem


'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")
'Set OutlookApp = New Outlook.Application


Dim Sendrng As Range
Set Sendrng = Worksheets("report").Range("C2:L63").SpecialCells(xlCellTypeVisible)
Sendrng.Copy


'Create Mail Item
Set MItem = OutlookApp.CreateItem(0)
'Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = "test@email.com"
.Subject = "report"
.CC = ""
.BCC = ""
.Body = "Email body text here"
.Display
End With




SendKeys "^({v})", True
DoEvents
With MItem
'.Send
End With


Set OutlookApp = Nothing
Set MItem = Nothing


End Sub

 

[Domenic]

Instead of using the SendKeys method, use the WordEditor property of the Inspector object, which returns the Word Document object model...

With MItem
    .To = "test@email.com"
    .Subject = "report"
    .CC = ""
    .BCC = ""
    .Body = "Email body text here"
    With .GetInspector.WordEditor
        .Application.Selection.EndKey Unit:=6 'wdStory
        .Application.Selection.TypeParagraph
        .Application.Selection.Paste
    End With
    .Display
End With

Hope this helps!

 

[Number1One]

Thanks Domenic, finally got a chance to try it and that does the job great. Can you add a signature in Word mode?

Im now needing a second paste into the same email, any help with that?
VBA is running this filter, then copying the resulting visible cells into the above code:

wsDest.Range("$A$4:$BK$750").AutoFilter Field:=8, Criteria1:=Array("Core | Critical", "Core | No"), Operator:=xlFilterValues
wsDest.Range("$A$4:$BK$750").AutoFilter Field:=12, Criteria1:=Array("Today", "Future", "Today Future"), Operator:=xlFilterValues

It now needs to filter again and copy visible cells again in the email, below the other paste with a small gap of body text in between.

wsDest.Range("$A$4:$BK$750").AutoFilter Field:=8, Criteria1:=Array("A", "B", "C"), Operator:=xlFilterValues
wsDest.Range("$A$4:$BK$750").AutoFilter Field:=12, Criteria1:=Array("Today", "Future", "Today Future"), Operator:=xlFilterValues

To create:
Body
Paste 1
Body 2
Paste 2
Signature