VBA - Replace text in OFT Template with condition

Question

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)

There's a lot wrong with the code from a syntax standpoint even. For starters, always use [Option Explicit](http://www.excel-easy.com/vba/examples/option-explicit.html). `.HTMLBody` is an uqualified assignment. `otlNewMail` is an unassigned variable. I suggest to break down your code to the simplest steps. Like really, really baby steps and make sure they work before moving onto the next. Then, if you get stuck, come here with a very specific problem and we can help you very easily. — Scott Holtzman, Sep 21 '17 at 15:38
Your code cannot possible work at all - for one thing, you have dots but no `With` statement. — SJR, Sep 21 '17 at 15:38
I break it apart and I found the flaw - I didn't post the whole code before, so I guess it didn't make much sense. I will do my research before uploading another question, but you can be sure I'll be back with other rookie question. Anyway thanks for being here guys! — Mari, Sep 22 '17 at 09:15

score 0 · Answer 1 · answered Sep 21 '17 at 15:51

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

VBA - Replace text in OFT Template with condition

1 Answers1