0

I am trying to accomplish this:

Output

I am trying to avoid using RangeToHtml if possible. It seems to copy the range but does not paste it in Outlook. Here is what I have thus far.

Sub Send_Email_Condition_Cell_Value_Change()

    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim rng As Range
    Set rng = Range("B6:C16")
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    On Error Resume Next
    With pMail
        .To = "@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "BLANK Account Action Price Notification"
        .Body = "Hello, our recommended action price for BLANK has been hit." & vbNewLine & vbNewLine & _
            "Thank you."
        .Display
         Dim wdDoc As Object     '## Word.Document
         Dim wdRange As Object   '## Word.Range
        Set wdDoc = OutMail.GetInspector.WordEditor
        Set wdRange = wdDoc.Range(0, 0)
        wdRange.InsertAfter vbCrLf & vbCrLf
        'Copy the range in-place
        rng.Copy
        wdRange.Paste
        'Below will auto send the email when apostrophe is removed
        '.Send
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

I have tried utilizing RangeToHtml, but that is a bit complex for my abilities. I have found this solution however I am unable to make it work.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
HunterN
  • 5
  • 2
  • Why a word document? Are you trying to insert that document later into Outlook? What do you have in that cells? what is the result desired (a table, simple text)? – wrbp Feb 13 '23 at 22:37
  • You can identify this type of problem with with `Option Explicit`. Consider it mandatory. The problem is due to misuse of `On Error Resume Next`. `On Error Resume Next` is incorrect as used in this code. Delete it. **Note** `On Error Resume Next` is misused 99.9999% of the time. https://stackoverflow.com/questions/31753201/vba-how-long-does-on-error-resume-next-work/31753321#31753321. – niton Feb 14 '23 at 23:28

1 Answers1

0

You are using the wrong object name

Change

Set wdDoc = OutMail.GetInspector.WordEditor

with

Set wdDoc = pMail.GetInspector.WordEditor
wrbp
  • 870
  • 1
  • 3
  • 9