Solved HTML and Variables (1 Viewer)

[Dumferling]

I am sending email using HTMLBody. I want to insert variables into the body of the text but the variables don't display. Is it possible to display a variable and what is the syntax, if it is? I have tried <var> & variable & <\var> but it just italicised the name of the variable and didn't actually display the content. I don't know much about HTML.

This is probably pretty simple but I can't find a simple answer.

 

[Minty]

You need to concatenate your variables into the HTML string

strHTML "<b>STuff goes here" & MyVariable & " more htmls in here</b>"

 

[Dumferling]

I tried this as well but it didn't work - just showed text " & variable & " text. The ampersands actually showed up like that.

 

[gemma-the-husky]

Try it again. Minty missed an equals sign out, but I am sure you picked that up.

strHTML = "<b>STuff goes here " & MyVariable & " more htmls in here</b>"

You might need to surround MyVariable with quote "" characters but I doubt it. It's chr(34) in my example below
Maybe you don't have the quote marks matched.
Keep playing around. You are bound to get the syntax right. Just play with msgboxes until you solve it

strHTML = "<b>STuff goes here " & chr(34) & MyVariable & chr(34) & " more htmls in here</b>"
msgbox strHTML

Show us the code you are using to concatenate the string.

 

[Dumferling]

Try it again. Minty missed an equals sign out, but I am sure you picked that up.

strHTML = "<b>STuff goes here " & MyVariable & " more htmls in here</b>"

You might need to surround MyVariable with quote "" characters but I doubt it. It's chr(34) in my example below
Maybe you don't have the quote marks matched.
Keep playing around. You are bound to get the syntax right. Just play with msgboxes until you solve it

strHTML = "<b>STuff goes here " & chr(34) & MyVariable & chr(34) & " more htmls in here</b>"
msgbox strHTML

Show us the code you are using to concatenate the string.

The text is stored in a table and looks like this: <p>A new regulation " & vblRegulation & "has been published and, in order to ...

The Code looks like this:
With MailOutlook
.BodyFormat = 2
.To = vblLegal
'.cc = ""
'.bcc = ""
.Subject = "New Regulation for Reading: " & vblRegulation
.HTMLBody = vblEmailID
'.Attachments.Add (vbl)
.Display
End With

vblEMailID is the variable that just picks up the text from the table

 

[Dumferling]

The message comes out fine, just the variables don't display the content. Maybe it is something to do with storing the message in a table.

 

[Gasman]

.HTMLBody = vblEmailID

Is that even correct?, seems like strange naming convention to me.

Debug and see what is actually in those variables (if anything)?

 

[Dumferling]

The variables have content - I checked that. I usually call my variables with a vbl prefix - a hangover from my initial learning to code on an Apple II

 

[Minty]

The variables in the table are just strings they won't hold a value.
You need to create the final string in the code to get the variables to populate.

 

[Dumferling]

Hmm. OK. I think I can figure that out. Basically I need to take the text from the table and run it in VBA to get the variables to populate, then put it in html.

Thanks. That should work

 

[Gasman]

Start from basics.

Debug.Print your variable for the body, before you assign it to the email body.

I am not talking about the prefix either, but EmailID?

To me that would hold the autonumber of the record for the email data I wanted to use?

 

[Dumferling]

Yes, that is correct - EmailID is the location in the table of the particular record

 

[Gasman]

So you would get the data from the rest of that record?, not the autonumber field?

 

[Dumferling]

The way I have it is that each email my system sends is stored in a table. The body of the email is stored in both text format and html format. The code just picks up the html formatted text from the relevant record (identified by the autonumber) using a DLookup function and puts it into HTMLBody. The issue is that if I insert a reference to a variable in that html text, it doesn't convert to a variable in the HTMLBody of the email. I have already put content into the variable in the code but the HTML text is just treated entirely as text and Access doesn't recognise the reference to the variable - it just treats it as text. So I have to try to get Access to recognise the text as a variable and not as just text.

 

[Gasman]

The way I have it is that each email my system sends is stored in a table. The body of the email is stored in both text format and html format. The code just picks up the html formatted text from the relevant record (identified by the autonumber) using a DLookup function and puts it into HTMLBody. The issue is that if I insert a reference to a variable in that html text, it doesn't convert to a variable in the HTMLBody of the email. I have already put content into the variable in the code but the HTML text is just treated entirely as text and Access doesn't recognise the reference to the variable - it just treats it as text. So I have to try to get Access to recognise the text as a variable and not as just text.

I understand that, but you are assigning html body to EmailID, now EmailHtml?
Have you considered using placeholders in your html and then just using Replace() function.

Did you edit post #5, as I did not see 'vblEMailID is the variable that just picks up the text from the table'

BTW, is this actually solved now, as the thread is showing Solved?

 

[Dumferling]

I haven't got it working but I did get the isolation of the issue which is that the copy from the table to the HTMBody is not working out. So I know that I need to to find a different way. I don't know about placeholders and Replace() functions and I will investigate that. If you have any example links, that would be great but otherwise Google is my friend. I am a believer that I learn a lot more by searching to resolve issues so I really only post here if I think it should be working and isn't or I really cannot find out how to take the next step. As it often turns out I just don't know how to frame the question properly - the answer is out there, I am just not asking correctly.

vblEmailID is used to locate the correct record - it is the autonumber. I just hard code it - in this instance vblEMailId is 6 which is the right record in the table for this email.

 

[Minty]

Place holders are a good concept. You put a unique identifier in your text something really obviously never going to appear in your normal text say

¬^Variable1^¬

Then use a replace() function to take your whole string and replace the placeholder with the value of your variable.
It works well for a smaller number of items.

 

[Dumferling]

Thank you, I will try that out. Sounds like it will work since I only have two variable to replace.

 

[gemma-the-husky]

Ah. It's making sense now.

If your table includes a field

"blah blah " & variable & " blah blah"

Then your table is actually storing a string defined as < "blah blah " & variable & " blah blah " >
and the variable is not tokenized. It's all just a single string. I suppose you might possibly be able to pass the string to eval(), and substitiute the variable in eval()

This isn't the same as forming a string with a statement, or multiple statements..

MyString = "blah blah "
MyString = MyString & chr(34) & variable & chr(34) 'I think it would need the chr(34) chars.
MyString = MyString & " blah blah"

You can build up the string in code, and incorporate the html tags. So you would need 3 email "bodies"
part1, part 2, part3, and add the variables at run time

You can also use @Minty idea of replacing placeholders. This is similar to a word document doing a mail merge. It knows which codes to replace with which fields from the mail merge. I do this within access to personalise messages and so on.

if you change your table string to

"blah blah [variable1] blah blah blah blah [variable2] blah blah "

then you can find whatever the tokens [variable1] and [variable2] are supposed to say, and replace them

replace(htmlstring,"[variable1]",replacementvariable1)
replace(htmlstring,"[variable2]",replacementvariable2)

 

[Dumferling]

Thanks for this. I thought of breaking it up into its component parts in the table and and building it at runtime but given that it is only one or two variables, the Replace() part looks simpler and easier to handle. Its great that I have sorted this out as it gives me much more flexibility with emails which will make things much better. I will have a lot of emails eventually and this will really help. Thanks