Trying to Copy and Paste Excel Range to Outlook but Paste Not Working

[larssonkok]

Hi,

I am trying to create a VBA code to create a new Outlook email and paste a range from my Excel file to the body of the email. Everything is working up until the paste piece. The correct cells are copied in the clipboard and I can paste it to the email by CTRL+V after the macro runs but I cannot get the data to paste as part of the running code. I am absolutely not an expert in vba or writing code and have put this together by lots of googling and watching tutorials.

My code is below and everything up until the last piece where I am trying to paste works. I would appreciate any guidance as I'm banging my head against a wall at this point!

Sub Macro3()
'
' Macro3 Macro
'

'
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range


Dim ExcRng As Range


On Error Resume Next

Set oLookApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set oLookApp = New Outlook.Application

End If

Set oLookItm = oLookApp.CreateItem(olMailItem)
Set ExcRng = Sheet6.Range("A7:J180")

With oLookItm

.Subject = Range("A2").Value
.To = "123@dummyemail.com"
.CC = "123@dummyemail.com"
.Body = ""

.Display

Set oLookInsp = .GetInspector
Set oWrdDoc = oLookIns.WordEditor

ExcTbl.Range.Copy
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd

Set oWrdRng = oWrdDoc.Paragraphs.Add
oWrdRng.InsertBreak

ExcRng.Copy

oWrdRng.PasteAndFormat wdFormatOriginalFormatting


End With

End Sub

 

[NdNoviceHlp]

Hi *larssonkok and Welcome to the Board! Your code requires a Word process be running with a document open... do you have this going on? There's really no need to use Word. You can paste ranges directly from XL to your outlook message. See this link for an example. HTH. Dave

Paste Excel range to Outlook email body

I want to copy data from a fixed range in an Excel sheet and paste on an email body. Below is the code I have come up with. However I am not able to paste the specified range A11:H12. Private Sub

stackoverflow.com

ps. please use code tags