VBA - Replace text in OFT Template with condition

920 Views Asked by At

I am starting with VBA. This time I am creating a simple automation for business emails, that could be send from excel, while using OFT Template. The code below works perfectly fine for replacing the text, but it can't be used to create a proper condition. I need to add a condition, that if the cell is empty, than do nothing, if contains something add "on" & text in the cell. The "Change Source" part of the code is obviously wrong. Could someone help?

Sub Test()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim x As Variant
    Dim Sour As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    x = Cells(1, 3)

        vTemplateBody = otlNewMail.HTMLBody

        strFind = "NAME1"
        strNew = Cells(x, 3)
        .HTMLBody = Replace(.HTMLBody, strFind, strNew)

'Change SOURCE
        vTemplateBody = otlNewMail.HTMLBody

        strFind = "SOURCE1"
        strNew = if Cells(x, 3) = "n/a" Then .Skip Else = " on " & Cells(x, 12)
        .HTMLBody = Replace(.HTMLBody, strFind, strNew)
1

There are 1 best solutions below

0
On

fyi: these two program snippets operate exactly the same

OutApp.CreateItem(0)
OutApp.CreateItem(1)
OutApp.CreateItem(2)
OutApp.CreateItem(3)
OutApp.CreateItem(4)

with OutApp
    .CreateItem(0)
    .CreateItem(1)
    .CreateItem(2)
    .CreateItem(3)
    .CreateItem(4)
end with