Click here to Skip to main content
15,867,756 members
Articles / Productivity Apps and Services / Microsoft Office

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 13.3K   8   2
An article on how to use text template with replacement variables in VBA

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
VB.NET
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:

VB.NET
...
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

License

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 

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.