CODEWITHSUNDEEP
excelvbaoutlook
Eem Jee
Eem Jee

Reputation: 1309

Copying a template from Excel to Outlook

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.

enter image description here 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".

Upvotes: 1

Views: 154

Answers (2)

0m3r
0m3r

Reputation: 12499

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...

enter image description here

Upvotes: 1

Preston
Preston

Reputation: 8177

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

Upvotes: 2

Related Questions