Click here to Skip to main content
15,665,480 members
Articles / Productivity Apps and Services / Microsoft Office
Posted 30 Oct 2009

Tagged as


8 bookmarked

Customizable Text with Replacement Variables

Rate me:
Please Sign up or sign in to vote.
3.33/5 (2 votes)
3 Nov 2009CDDL2 min read
An article on how to use text template with replacement variables in VBA


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
    ' 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

    Dim quotes$ ' Add quotes if ID is of string type
    If isnumeric(recordID$) Then
    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
        ' Get New Text
        MakeText$ = rs![NewText].value
    Else: MakeText$ = OriginalText$
    End If
    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.


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


This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)

Written By
Database Developer
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralQuestion Pin
grovelli3-Nov-09 3:21
grovelli3-Nov-09 3:21 
AnswerRe: Question [modified] Pin
byapparov3-Nov-09 6:30
byapparov3-Nov-09 6:30 

This was a bug, which is now corrected in the new version of the article.


modified on Thursday, November 5, 2009 8:57 AM

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.