User generated email template

[Chris Morris]

Dear All

I would like to create a table containing email templates for use around various applications and interfaces. I would also like the user to be able to create the text for these templates, but as well as the dynamic text. So a user might create a record as such:

Email_Template_ID: 1234
Email_Name: "Payment notification"
Email_Subject: "Project ID: {Project_ID} - Payment made"
Email_Body:
"Dear {Title} {Surname}
We have recently made a payment of {Amount}. Please check your bank account in the next two days. This relates to Project ID: {Project_ID}, {Project Name}.
Best wishes
Payment Team"

So a user, with sufficient knowledge of certain field names, would be able to drop in the dynamic content as they deem appropriate. The VBA would then replace anything in curly brackets with the genuine field name.

I could possibly start with Replace(Email_Body, "{", "' & [") and repeat it for the end curly bracket, but I'm sure VBA would just render this as Dear ' & [Title] & ' etc. without interpreting it as Dear Mr...

I hope I've explained myself OK. To look at it another way, in Excel there is a function called INDIRECT() which allows Excel to interpret a cell reference as the actual content of the cell...

Best wishes

Chrises

 

[Chris Morris]

OK, so after some digging and playing with the Eval() function, I've settled on this:

stBody = Eval("""" & Replace(Replace(Me.TestText, "{", """ & [Forms]![Payments Requested].["), "}", "] & """) & """")

With the Me.TestText just being the text box containing:
Project ID: {Project_ID} payment