Customizable Text with Replacement Variables

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
    ' Make Sql string
    Dim sql$
    Dim OriginalText$
    OriginalText$ = text

    Dim i As Integer
    Dim firstQual As Integer
    Dim secondQual As Integer
    Dim ToReplace As String
    i = 1
    ' Replace replacement variables with access fields
    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$ ' Add quotes if ID is of string type
    If isnumeric(recordID$) Then
        quotes$=""
    Else: quotes$=""""
    End If

    ' Select specific text from source table (tableName)
    sql$ = "SELECT Left(""" & text$ & """,255) as NewText " & _
            "FROM " & tableName$ & "  WHERE " & pkFieldName$ & "=" & 

quotes$ & recordID$ & quotes$

    ' Open Recordset
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(sql$)

    ' Get the text from the recordset
    If Not rs.EOF Then
        rs.MoveFirst
        ' Get New Text
        MakeText$ = rs![NewText].value
    Else: MakeText$ = OriginalText$
    End If
    rs.Close
    Set rs = Nothing
    Exit Function
Err: ' Oops

    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 

• 30^th October, 2009: Initial post
• 2^nd November, 2009: Article updated