HomeDownloadsSource Code LibraryMarked Text Fields (8)How to build a mail-merge application

How to build a mail-merge application

Author:	TX Text Control Support Department
Language:	Visual Basic
Version:	1.1
Released:	March 20, 2001
Last modified:	January 11, 2008
Requirements:	TX Text Control ActiveX with Visual Basic 6.0
Download code:	mail_merge.zip  [52.37 KB, ZIP]

Part #1

If you are an experienced TX Text Control user, then you no doubt will already know all about Mail Merge and how to implement it in TX Text Control - thus this example will be somewhat superfluous. However, we still get a lot of support questions concerning mail merging, so we thought it is about time that we created a set of tutorials describing how to create a complete mail merge application from scratch, using TX Text Control. This is part one of the step-by-step guide.

First of all, we are going to start with an example that illustrates just about the same thing as the Stdlet example from the TX Text Control sample application, shipped with TX Text Control.

In this example you will find a menu point, with which a template file can be loaded. This file already contains a bunch of Marked Text Fields. So that we can get some data from a database into these fields, each one has a unique name. These unique names are saved in the FieldData property.

Using a second menu point, a further form can be opened - this allows a suitably prepared database to be linked. With a Buttonclick, the current data set can be merged into the document.

The most important function of this example is the one which allows a data field from the linked database to be merged into a field from TX Text Control. Using the Stdlet example as it is, this is not possible. To get this to work, we need to get the field number of the Marked Text Field which belongs to the data field. This can be simply achieved using the following code:

Function FindField(name As String) As Integer
 
    Dim id As Integer
    Dim foundID As Integer
 
    foundID = 0
    id = Form1.TXTextControl1.FieldNext(0, 0)
 
    While id <> 0
 
        If Form1.TXTextControl1.FieldData(id) = name Then
            foundID = id
        End If
 
        id = Form1.TXTextControl1.FieldNext(id, 0)
    Wend
 
    If foundID <> 0 Then
        FindField = foundID
    Else
        FindField = 0
    End If
 
End Function

Part #2

As you remember from last week, we were in the process of creating a mail merge application using TX Text Control - we have already built in the basic features such as pulling data from a database and inserting it into our TX document. This week, we are going to add the ability to print our mail merged document to a local printer.

Now, I guess some of you are screaming: "BFD - that's a piece of cake. I mean, I can grab the code from other examples and use that, right?" So that we do not bore you with this (what would seem) trivial task, we are going to build in a few advanced features, to make the example a little more complicated.

We are going to set up a print queue. With the example from last week, the user can browse through a database and merge single data records in to his or her document. The user will have the option to add single records to the print queue and then start the printing of the queue on command.

The queue is a simple list box, in which the company name of the current record is added. This record name will then be replaced by the actual data from the current record.

To print a document in the background - i.e. so that the user does not know it is being printed - we have to use a cunning trick: We use a second, invisible TX Text Control, which we fill with the data from the first and only then do we print it.

So that we do not have to write a second routing for the merging, the old one has been changed a little and defined globally. This globally defined routine now gets the parameters from the TX Text Control in which the merging is currently taking place. What's more the actual records from the database are now merged in the marked text fields and no longer the label captions.

Now we just need to call StartPrinting to start the printing! Take a look at the following VB code:

Sub StartPrinting()
 
    Dim data() As Byte
    Dim i As Integer
 
    ' Copy document to hidden TX
    data = Form1.TXTextControl1.SaveToMemory(3, False)
    Form2.TXHidden.LoadFromMemory data, 3, False
 
    ' Print queue
    While (doPrint And Form2.List1.ListCount > 0)
 
        Merge Form2.TXHidden, Form2.List1.List(0) ' Merge data
 
        For i = 1 To Form2.TXHidden.CurrentPages
            Printer.Print
            Form2.TXHidden.PrintDevice = Printer.hDC
            Form2.TXHidden.PrintPage i
            Printer.NewPage
        Next i
        Printer.EndDoc
 
        RemoveItem (0)  ' First item printed, remove it from queue
 
    Wend
 
End Sub

As you can see the complete content of the visible document is copied into the second, hidden TX Text Control. We use LoadFromMemory and SaveToMemory to do this. Now we simply have to work off the queue:

A simple while loop, which runs until there are no longer entries in the list box, takes care of this.

However first, we have to do the actual merging. The hidden TX and the string at position 0 of the list box are passed as parameters. In this case it is the company name of the record which is to be printed. The document is then printed and finally the record is removed from the list box.

Part #3

This week we are getting back to our top topic of mail merge. This is the third part of the three part tutorial.

Last week, we added a print queue to our mail merge application. This week we are going to build a real mail merge layout editor into our little sample program. In other words, the user should be able to select a database and insert its fields into his active document just by clicking his mouse. Then, when we start the actual mail merging process, these fields will be replaced by the data from the database.

So, what is so difficult about that? Well, the problem is that we do not know how many fields there are in the database. In other words our application must be able to work with databases of varying size.

Let's have a look at what we need to do, step-by-step:

First of all, we set up a global variable curDBName. In this memory we are going to write the name of the currently loaded database.

Secondly, we are going to set up a menu point, which will call a Common Dialog so that the user can select a database. However, the database is not really loaded -- just the name of the database is stored in curDBName. Storing an entire database in the memory of a PC could be somewhat problematic, should the database be several terabytes in size! :-)

Thirdly, we need to let the user insert fields from the database into this document. However, only fields which are in the database may be inserted into the active document. So, how do we do this? We use a combo box. On loading the database we then fill this combo box with the names of its fields. Using a simple button, we can then insert a field from the database into our active document.

Taking the samples from the previous 2 parts where ever we had static code, we now have to replace it with dynamically produced code.

Take for example the "Browse Database" form: In the previous example, the labels which display the data from the database were statically linked to the database.

Using a control array, these labels now need to be dynamically created when the form is loaded. Once they have been loaded, they then have to be linked to the corresponding database field.

To do this, we need to use the following Visual Basic code:

Private Sub Form_Load()
 
    Data1.DatabaseName = curDBName
    doPrint = False
    TXHidden.Left = 10000 ' Move TX out of the visible area
 
    Dim db As Database
    Dim rs As Recordset
 
    ' Find record
    Set db = OpenDatabase(curDBName)
    Set rs = db.OpenRecordset(db.TableDefs(0).name)
 
    lblField(0).Caption = rs.Fields(0).name
    lblData(0).DataField = rs.Fields(0).name
 
    ' Create labels
    For i = 1 To rs.Fields.Count - 1
        Load lblField(i)
        lblField(i).Top = 320 + (i * 300)
        lblField(i).Caption = rs.Fields(i).name
        lblField(i).Visible = True
 
        Load lblData(i)
        lblData(i).Top = 320 + (i * 300)
        lblData(i).DataField = rs.Fields(i).name
        lblData(i).Visible = True
 
        ' if i > 6 We need more space
        If i > 6 Then
            Form2.Height = Form2.Height + 300
            Frame1.Height = Frame1.Height + 300
            Data1.Top = Data1.Top + 300
            cmdMerge.Top = cmdMerge.Top + 300
            cmdCancel.Top = cmdCancel.Top + 300
        End If
    Next i
 
End Sub

On one side we have the field labels, which will show the name of the currently active database field and on the other we have data labels, which will display content from the database.

As soon as we have more that seven fields in the database, the form will have to be enlarged so that there is enough room to accommodate all the labels.

A very important point here is the saving and loading of the templates: When the fields from a database appear in the active document, the connected database must also be loaded when the document is loaded.

We therefore need to save the name of the database in the document. To do this, we have to write a header to the document we are saving. Only then may we save the TX Text Control data. On loading, the header is read first.

Here is the Visual Basic code to do exactly this:

Private Sub mnuSaveTemplate_Click()
 
    Dim FileID As Long
    Dim lFilePos As Long
 
    On Error Resume Next
    ' Create a "Save File" dialog box
    CommonDialog1.Filter = "TX Demo (*.ttx)|*.ttx"
    CommonDialog1.DialogTitle = "Save As"
    CommonDialog1.Flags = cdlOFNOverwritePrompt Or cdlOFNHideReadOnly
    CommonDialog1.CancelError = True
    CommonDialog1.ShowSave
    If Err Then Exit Sub
 
    ' Write Header
    Open CommonDialog1.FileName For Binary As #1
 
    FileID = FILE_ID
    Put #1, , FileID
    Put #1, , curDBName
    Put #1, , vbCrLf
 
    lFilePos = loc(1)
 
    Close #1
 
    TXTextControl1.Save CommonDialog1.FileName, lFilePos, 3
 
End Sub

Please note that for this code to work, the database has to be in the same folder as our application. Please feel free to improve on this example. Any improvements you may make, you can certainly send to us and we will copy them onto your web site to share with the rest of the TX Text Control Community.