VBA String Variable Contains Other Variable

Question

I have a string that I am trying to build with text and other variables.
Is there a way to build the string so that I can use other variables ?

I have a text file that my vba is reading to compose the body of an email.

The text file has this line:

email_body|"Final Reports will be distributed on or after \" & Me.cbo_Month.Column(0) & \" 10th.

I am calling the function

MessageBody = getAdmSetting("email_body")

which returns the string exactly:

Final Reports will be distributed on or after \" & Me.cbo_Month.Column(0) & \" 10th.

If I debug.print MessageBody, i get the string exactly. I am trying to get the value of Me.cbo_Month.Column(0) to print.

debug: Final Reports will be distributed on or after \" & Me.cbo_Month.Column(0) & \" 10th.

I would like it to print: Final Reports will be distributed on or after January 10th.

What is the issue? At glance, your string is structured correctly. Are you sure Me.cbo_Month.Column(0) returns a valid string to concatenate here? — urdearboy
– urdearboy, Commented Feb 25, 2020 at 17:22
maybe i didtn ask the correct question or post enough information. I have a function that reads a text file for the body of an email. I would like to be able to add variables into the text file. — Tom
– Tom, Commented Feb 25, 2020 at 17:25

Tim Williams · Accepted Answer · 2020-02-25 19:23:22Z

2

You can't easily evaluate random pieces of code in VBA like you can in some other languages. You'd be better off using tokens which you replace.

In your text file:

email_body|Final Reports will be distributed on or after {month} 10th.

then you can

Debug.Print Replace(MessageBody, "{month}", Me.cbo_Month.Column(0))

answered Feb 25, 2020 at 19:23

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1