Replace a placeholder in a VBA code

[Paula777]

Hello all,

I am new to VBA and am trying to create my first code to automate sending mass emails to various email adresses with the same subject and the same body of the email (written and taken from a text box) but I need to adjust and personalize one amount value which I need to be different for each email. In the text box which is the body of the email I put a placeholder XYZ and I want this to be replaced with values from column E (amount column)

My code looks like this:

....


Dim i as Integer
Dim last-row as Integer
Last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

For i = 2 To last row
Set msg = OA.createitem(0)

Msg.to = sh.Range("B" & i).Value
Msg.subject = sh.Range("C" & i).Value
Msg.body = sh.TextBoxes("TextBox 1").Text

And here i wanted to add sth like in order to replace XYX with the values from the Amount column:

Amount = Range("E" & i).Value
' replace place holders
Body = Replace(body, "XYX", amount)

But it doesnt work. It doesnt show any error, it simply doesnt do anything.

Do you maybe know how to fix it and replace the XYX from the text box which is put as the body of the email for the values from the E Column?

 

[6StringJazzer]

Maybe use qualifiers. Body is not the same as Msg.Body. It is a new undeclared variable. This would have been caught as a compiler error if you used Option Explicit.

Amount = sh.Range("E" & i).Value
' replace place holders
Msg.Body = Replace(Msg.Body, "XYX", amount)

I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

 

[gravanoc]

Hello all,

I am new to VBA and am trying to create my first code to automate sending mass emails to various email adresses with the same subject and the same body of the email (written and taken from a text box) but I need to adjust and personalize one amount value which I need to be different for each email. In the text box which is the body of the email I put a placeholder XYZ and I want this to be replaced with values from column E (amount column)

My code looks like this:

....


Dim i as Integer
Dim last-row as Integer
Last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

For i = 2 To last row
Set msg = OA.createitem(0)

Msg.to = sh.Range("B" & i).Value
Msg.subject = sh.Range("C" & i).Value
Msg.body = sh.TextBoxes("TextBox 1").Text

And here i wanted to add sth like in order to replace XYX with the values from the Amount column:

Amount = Range("E" & i).Value
' replace place holders
Body = Replace(body, "XYX", amount)

But it doesnt work. It doesnt show any error, it simply doesnt do anything.

Do you maybe know how to fix it and replace the XYX from the text box which is put as the body of the email for the values from the E Column?

Are you using a reference to the Outlook object model or are you using CreateObject?