Imagine you need to send several similar emails to different addressees, and you need to make each email personal. This code allows you to convert generic text template to a personalized version through replacement of variables. For example, for the following template:
Dear %Surname%, We would like to thank you for buying %Product% from us... ...
It will be delivered on %DeliveryDate% ...
The specific text emailed to a particular customer will be:
Dear Smith, We would like to thank you for buying Big TV Set from us... ...
It will be delivered on 20/11/2009 ...
As the code below shows, personalization is achieved by replacing variables closed in % with values from a relevant record in Access, found by record ID, table name and PK field.
The function is normally used for creating notification emails reporting updates in certain business processes. Examples of most commonly used replacement variables are process name, process location and required action.
Using the Code
All replacements are carried out by function
MakeText$. The function has 4 parameters:
text$ - The text template that contains replacement variables closed in %
tableName$ - Name of Access table or view with field names that match replacement variables in the text template
pkFieldName$ - Name of the field that stores primary key
recordID$ - Primary key for the record that contains values that will be used for replacement
Public Function MakeText$(text$,tableName$,pkFieldName$, recordID$)
On Error GoTo Err
OriginalText$ = text
Dim i As Integer
Dim firstQual As Integer
Dim secondQual As Integer
Dim ToReplace As String
i = 1
Do While i < Len(text)
If Mid(text, i, 1) = "%" Then
firstQual = i
secondQual = InStr(firstQual + 1, text, "%")
ToReplace = Mid(text, firstQual + 1, secondQual - firstQual - 1)
text$ = replace(text, "%" & ToReplace & "%", _
""" & " & tableName$ & "." & ToReplace & " & """)
i = i + secondQual - firstQual + 13
i = i + 1
If isnumeric(recordID$) Then
sql$ = "SELECT Left(""" & text$ & """,255) as NewText " & _
"FROM " & tableName$ & " WHERE " & pkFieldName$ & "=" &
quotes$ & recordID$ & quotes$
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(sql$)
If Not rs.EOF Then
MakeText$ = rs![NewText].value
Else: MakeText$ = OriginalText$
Set rs = Nothing
MakeText$ = text$
Set rs = Nothing
MakeText$ = OriginalText$
To use the code, first save your text templates, and then call the function. In our example with the new customer Mr Smith, this may be:
newText$=MakeText$(templateText$, "NewCustomers", "ID", custID)
templateText$ is the text of the email notification template designed for new customers, "
NewCustomers" is an Access view that contains records of new customers, including the one for Mr Smith, "
ID" is a primary key field in this view and
custID is a variable which stores the actual ID of Mr Smith’s record.
Function will return original template text if record was not found.
Limitation: Due to the limit in Microsoft Access on string length in query results, template length should be less than 255 characters long.
Points of Interest
This function allows to keep the number of letters (templates) stored in the system as low as possible. Also it is very generic and can be used in the number of occasions.
- 30th October, 2009: Initial post
- 2nd November, 2009: Article updated
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.