Copying a template from Excel to Outlook

Question

I have a template that is stationed in an excel file. Once I click the preview button, this template will be displayed in outlook as well as its subject, to and etc.

I have this code that works fine but is not working in the body field.

Sub previewMail()
Dim objMail, objOutLook As Object
Dim rngTo, rngCC, rngBCC, rngBody As Range
Dim lRow As Long
Dim i As Integer


Set objOutLook = CreateObject("Outlook.Application")
Set objMail = objOutLook.CreateItem(0)
Set main = ThisWorkbook.Sheets("Main")

lRow = main.Cells(Rows.Count, 2).End(xlUp).Row

For i = 11 To lRow
    With main
        Set rngTo = .Range("B" & i)
        Set rngBody = .Range(.Range("C10:N30"), .Range("C10:N30")) 
    End With

    With objMail
        .To = rngTo.Value
        .Subject = "Sample"
        'i like the rngbody to be here
        .HTMLBody = RangetoHTML(rngBody)' from Ron de Bruin site
        .Display

    End With
Next i
End Sub

This is the template stationed in the said range above.

Can anyone please help me figure this out? I have tried this from Ron de Bruin but I can't make it work. This only gives a product that is an "invisible table".

Answer 1

Try Range.PasteAndFormat wdChartPicture

Example

Option Explicit
Sub previewMail()
    Dim objMail, Main, objOutLook As Object
    Dim rngTo, rngCC, rngBCC, rngBody As Range
    Dim lRow As Long
    Dim i As Integer
    Dim wordDoc As Word.Document '<---

    Set objOutLook = CreateObject("Outlook.Application")
    Set objMail = objOutLook.CreateItem(0)
    Set Main = ThisWorkbook.Sheets("Main")
    Set wordDoc = objMail.GetInspector.WordEditor '<---

    lRow = Main.Cells(Rows.count, 2).End(xlUp).Row

    For i = 11 To lRow
        With Main
            Set rngTo = .Range("B" & i)
            Set rngBody = .Range(.Range("C10:N30"), .Range("C10:N30"))
            rngBody.Copy '<---
        End With

        With objMail
            .To = rngTo.Value
            .Subject = "Sample"
            .Display
             wordDoc.Range.PasteAndFormat wdChartPicture '<---
             ' Or
             'wordDoc.Range.PasteAndFormat wdChartPicture & .HTMLBody = " "
        End With
    Next i
End Sub

Make sure to set references to the Microsoft Outlook and Microsoft Word Object libraries

Tools > References...

Answer 2

EDIT: OP has indicated text is not in range, but in a textbox in front of range.

Use this code to find the textbox name:

for i = 1 to activesheet.chartobjects.count
    debug.print chartobjects(i).name
next i

It will be like Textbox1 or something, then use(untested):

dim strBody as string
Set strBody = activesheet.chartobjects("Textbox1").Value
.HTMLBody = strbody