Introduction
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.
Background
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
Dim sql$
Dim OriginalText$
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
End If
i = i + 1
Loop
Dim quotes$
If isnumeric(recordID$) Then
quotes$=""
Else: quotes$=""""
End If
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
rs.MoveFirst
MakeText$ = rs![NewText].value
Else: MakeText$ = OriginalText$
End If
rs.Close
Set rs = Nothing
Exit Function
Err:
MakeText$ = text$
Set rs = Nothing
MakeText$ = OriginalText$
End Function
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)
...
Here, 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.
History
- 30th October, 2009: Initial post
- 2nd November, 2009: Article updated