Not able to copy range from a sheet and paste into outlook email!

[AutomateExcel]

Hi,

I'm trying to write macro code to copy a range from an excel sheet (Sheet Name: IN) and paste the copied range to Outlook Email Body.
Below is the code that I have. I'm not sure what am I missing. The code is creating the email but range is not being pasted.
Please see beloe my code and the email it generates:

>>> use code - tags <<<

Sub Copy_Range_to_Outlook_Email()

'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItem As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

'Declare Word Variables
Dim oWordDoc As Word.Document
Dim oWordRng As Word.Range

'Declare Excel Variables
Dim exclRng As Range

Dim msgText As String

On Error Resume Next

'Get the Active Instance of Outlook
Set oLookApp = GetObject(, "Outlook.Application")

'If error create a new instance of Outlook
If Err.Number = 429 Then
    'Clear Error
    Err.Clear
   
    'Create new instance of Outlook
    Set oLookApp = New Outlook.Application
End If

'Create a new Email
Set oLookItem = oLookApp.CreateItem(olMailItem)

'Create reference to excel range
'Set exclRng = Sheet3.Range("A1:C3")
Set exclRng = Sheets("IN").Range("A1:C3")

'Define Email Body
msgText = "Dear Reporter," & vbNewLine & vbNewLine & "Please take note of the following alerts:" & vbNewLine

With oLookItem

    'Define basic information
    .To = "abc@xyz.com"
    .CC = "abc@xyz.com"
    .Subject = "Alert on status"
    .BodyFormat = olFormatRichText
    .Body = msgText
    .Display
    '.Send
   
    'Get the Active Inspector
    Set oLookIns = .GetInspector
   
    'Get the word editor
    Set oWordDoc = oLookIns.WordEditor
   
    'Specify range in document
    Set oWordRng = oWordDoc.Application.ActiveDocument.Content
        oWordRng.Collapse Direction:=wdCollapseEnd
       
    'Add new paragraph
    Set oWordRng = oWdEditor.Paragraph.Add
        oWordRng.InsertBreak
       
    'Copy range
    exclRng.Copy
   
    'Paste range to email
    'oWordRng.PasteSpecial DataType:=wdPasteMetafilePicture
    oWordRng.PasteSpecial
   
End With

End Sub

 

[BobBridges]

Rather than examining your program and trying to guess where it's going wrong, let me give you this hint: The MS Office apps have an excellent tool for debugging your VBA programs so that you can easily spot the location of any problem, and usually see the cause. At the top of your VBA editor, look in the menu bar under Run. The <F8> keystroke lets you execute your program one statement at a time, and watch what it does. At each step you can examine the variables and objects to see their values. You should have no trouble watching your program work properly up to a certain point, and then at some place in the program do something unexpected. You should be able to tell what's going wrong from that.

If not, ask again with more details and someone will be able to opine further.