How do I replace text with cell value?

Viewed 89

In this email sender how do I replace the text "employee_name" and "voucher_no" with the value in a cell.

Trying to send personalized email with an individual voucher for each email address.

Sub bulk_emails()

Dim ol As Outlook.Application
Dim olm As Outlook.MailItem
Dim inspect As Outlook.Inspector
Dim wd As Word.Document

Set ol = New Outlook.Application

For r = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    Sheet2.Range("C7:R26").Copy

    Set olm = ol.CreateItem(olMailItem)

    With olm
        'Display
        .To = Sheet1.Cells(r, 3).Value
        .Subject = Sheet2.Range("C6").Value
        .Body = Sheet1.Cells(r, 2).Value
        .BodyFormat = olFormatHTML
        .Display
        Set inspect = olm.GetInspector
        Set wd = inspect.WordEditor
        wd.Content.Paste
        .HTMLBody = Replace(.HTMLBody, "employee_name", Sheet1, Cells(r, 1), Value)
        .HTMLBody = Replace(.HTMLBody, "voucher.no", Sheet1, Cells(r, 2), Value)
    End With

Next

Set olm = Nothing
Set ol = Nothing
Set wd = Nothing

End Sub
0 Answers
Related