Merge in a picture from a database

Word cannot merge a graphic or OLE-object directly from a database because it does not understand the type of data the database sends across (long binary). Therefore, you have to workaround this obstacle.

  1. Save the graphics files in a folder (directory) on a drive
  2. Place the file name (or path information) in a field in the database
  3. Reference the graphic file information by nesting the merge field in an IncludePicture field.
    Example: { IncludePicture "C:\\Data\\Pics\\{ Mergefield Signature \* upper }" }
  4. You must now merge to a new document.
  5. Update the field display so that each record shows the correct picture. (You can press Ctrl+A to select the entire document, then F9 to update the fields.)

Note the double backslashes in the example, as well as the double-quotes. Both are necessary if the field is to work correctly.


Group multiple items for a single condition

Sometimes, you want to create a mail merge where you can list several items for one particular category, such as the invoices for each customer. Word does not have an automatic facility for this kind of merge, but there are workarounds.

  1. You can use the InsertDatabase field to link the category information from the merge to the same category from the database. The list will be in a table. There is an excellent sample file (Compound.EXE) on Leigh Weber's website. The main drawback of this method is the restricted possibilities for formatting.
  2. A combination of IF and SET fields can check whether a merge record meets the criteria, if it does the information is listed, otherwise the merge moves to the next record. The Microsoft Knowledge Base article 211303 gives a detailed example. Note that you will only see the end results of this method after merging to either a new document or to the printer; you will not seem them when viewing the merge result in the main merge document.
  3. You can create a user-defined function in your Access database that concatenates all items for the list into a single string. Place this function in an expression in the query you will be using as a datasource so that it can be selected as a single field in the mail merge. Please note that this method only works when the merge link method is DDE; ODBC drivers do not recognize user-defined functions. An example of such a function and a query using it is in the file WdAcc95.zip for Office 95 (51 KB); WdAcc97.zip for Office 97 and later (131 KB).
  4. If none of these methods is satisfactory, then you can forego the built-in mail merge process entirely and use OLE-Automation or DDE to create your Word documents from your database application or a VB or Delphi interface. I provide an example with sample files of using OLE-Automation to insert information from an Access 95 database into Word.

Later versions of Office give you even more flexibility in the approach you can take because Word VBA allows you to use OLE Automation and DAO or ADO to connect with databases. You can download a zip file with sample database, templates and explanatory documents (created in Office97 with a DAO interface). The MOD article Beyond Mail Merge with sample files explores similar territory in Office 2000 / 2002 using ADO. The information for Office 97 through 2002 is basically interchangeable. Unfortunately, this article is no longer available on-line and for copyright reasons, I can't provide it directly. It is, however, available on a CD with all the other articles from this publication that appeared through 2002.


Mail merge to a chart

Word mail merge doesn't provide any way to generate a chart for each record in a mail merge. There are four basic approaches you can use to create charts for mail merge:

The first three can be done manually, and are possible in all versions of Word; using VBA (macros) would make the process much faster, of course.

The fourth method relies on the Mail Merge Events introduced in Word 2002 to manipulate the main merge document as each record is merged. The sample files in MrgChart.zip provided here uses this method, automating an MS Graph object embedded in the main merge document. You can certainly derive the code necessary automating for the third method based on the code in this sample, as all the basics required for automating MS Graph are present.


Send Access database information to a Word 6/95 document

The Visual Basic for Applications (VBA) programming language allows you to control other MS Office applications using OLE-Automation. While versions of Word using Word Basic (prior to Word97) lack the capability of being able to use this method to run other programs, Excel and Access can control Word through the Word Basic commands.

Therefore, if you'd like to have Access interact with Word 6 or 7, and especially if you want to send Access information into Word, the best way is to use OLE-Automation from within Access. The procedures are too lengthy to include in an FAQ, so I've provided a zip file containing a sample Access 95 database, sample Word 95 template (no macros) and an explanatory Word document which you can download. (WdAcc95.zip, 51KB)

If you need to run Access from Word, you must use either DDE (see the Help files for Access and Word for examples) or the Word add-in wbodbc.wll which is on the diskette included in the Word Developer's Kit (ISBN 1-55615-880-7). It can also be downloaded from the GO MSWORD library on CompuServe and might be available on the Microsoft web-site, although "free stuff" for versions prior to Office97 are being phased out.

The WordBasic Help files for Word 6/95 that you need in order to automate these versions of Word can be downloaded from the Word MVP website.


Create an address directory

There are a couple of tricks to creating a multi-columned address book or telephone list using Word's mail merge, but the steps are not difficult :-)

  1. In Tools/Mail Merge create a Catalog type of merge, rather then form letter. This will ensure that there are no section or page breaks between the records.
    (In Word 2002 the type of merge is Directory, and you set it either in the first step of the Mail merge wizard, or using the first button of the Mail merge toolbar.)
  2. Insert all the mail merge fields and text in the main merge document as you would like each address block to appear. Keep in mind that any text you type will appear with each record.
  3. Set the number of columns you want.
  4. Press enter a couple of time after the last line of information in order to create some space between the addresses (or use Paragraph spacing, followed by one empty paragraph with no spacing).
  5. Select all paragraphs except the last two) and apply Format/Paragraph/Text Flow/Keep with Next and Keep Lines together so that the address blocks will not break across columns or pages.
  6. Pre-sort the listing using the Query Options.

Note that catalog type merges will not retain any headers and footers! This is because the last paragraph mark, which contains the header/footer information and is also a Next Page type of section break, is cut off of each record as it is merged. You must either create header/footer information in the merge result or insert a continuous section break just before the last paragraph mark of the main merge document. The drawback with the latter is that this will insert a section break between each merged record and this is not compatible when the document is formatted for columns.

Creating "Index headers" for the addresses

Using SET and IF fields in the main merge document you can generate "Index Headers" for grouped address blocks. You will need to include the item you want to group on as a field in the data source since Word does not allow you to use TEXT functions in its fields. For example, if you want to group all records where the last name starts with the same letter you must include a field in the data source to contain that letter and enter the information, either manually or using a macro.

In the main merge document create an IF field at the top of the address block and a SET field at the end of it. (How to use fields)

Example: { IF { REF Index } <> { MERGEFIELD INITIAL } "-- { MERGEFIELD INITIAL } --" }
          { MERGEFIELD LASTNAME }, { MERGEFIELD FIRSTNAME }
          { MERGEFIELD PHONE }{ SET Index { MERGEFIELD INITIAL } }
Display   -- A --
          Anonymous, Jane
          999 000-9999 

          Anonymous, Joe
          000 999-0000

In order for this to work properly, be sure to NOT have the first record updated before running the merge. If necessary, view a record that does not have the first "index" value just before merging.


Define Text to precede and follow a catalog merge

As mentioned in the previous discussion, Word will repeat all the text in the main merge document when merging to a catalog (directory). It is possible, however, to define text to only appear at the beginning of the document by using an If field to check whether the record being merged is the first one. If so, then the text is displayed, otherwise not:

{ IF { MergeSeq } = 1 "Place all the introductory text here" }

A similar construct can be used to add text to the end of a catalog/directory merge. It is not possible, however, to automatically determine which is the last record. Instead, you need to insert the number by hand, or you can use a macro to do so, as in the following sample. Here, the number of records is stored in a document property (File/Property), and a DocProperty field passes the value to the field.

{ IF { MergeSeq } = { DocProperty NumRecords } "Put closing text here" }

Sub SetNumRecsAndMerge
Dim mm As Word.MailMerge
Set mm = ActiveDocument.MailMerge
If mm.MainDocumentType <> wdNotAMergeDocument Then
mm.DataSource.ActiveRecord = wdLastRecord
ActiveDocument.CustomDocumentProperties( _
  "NumRecords").Value = mm.DataSource.ActiveRecord
mm.Execute
End If
End Sub


Suppress duplicate records

Similarly to point 2 for a complex merge, or for creating an index header in the catalog merge, here again you can use a combination of SET and IF fields in the main merge document to check for duplicate information and suppress merge fields for a record if they are the same as in the previously merged record. As for the other merge types, here you also need to pre-sort the records before merging.

Example:

{ IF REF Check <> "{ MERGEFIELD FIRSTNAME } { MERGEFIELD LASTNAME "
"{ MERGEFIELD FIRSTNAME } { MERGEFIELD LASTNAME }
{ MERGEFIELD ADDRESS }
{ MERGEFIELD CITY }" }
{ SET Check "{ MERGEFIELD FIRSTNAME } { MERGEFIELD LASTNAME }" }


Merge blocks of formatted text from separate source file

Often, when creating large, complex documents, it would be advantageous to store and manage blocks of formatted "boiler-plate" text outside the data source. A good method is to create a document where each block of "boiler-plate" is marked with a bookmark. It is easy to centrally manage and maintain a set of such documents.

In the main merge document, this information is pulled in by using a IncludeText field, on the same principle as the IncludePicture field when merging a picture (see above). Usually, the block of text should be inserted according to a condition or the bookmark name stored in the data source.

Example:

{ INCLUDETEXT "C:\\DocInfo\\Boiler.doc" { MERGEFIELD CUSTTYPE } }

Where MERGEFIELD CUSTTYPE holds the bookmark name.


Return to top of this article

Return to Home Page