Word Mail Merge with MS Access Table.
Form Letters.
Form letters are prepared from a standard Microsoft Word template, with addressee-specific information inserted into each letter. Appointment letters, interview call letters, employment contracts, and similar documents are typical examples. Preparing such letters using Microsoft Word’s Mail Merge feature is much easier than composing each letter individually for every addressee.
These types of letters contain common body text that applies to all recipients, along with specific areas where personalized information can be inserted, such as the recipient’s Name and Address, interview date, or the nearest location where a candidate can attend the interview.
If interview call letters need to be sent to hundreds of prospective candidates, we need to prepare only a single template letter containing the standard body text applicable to all recipients. Other personalized information can then be automatically inserted from the associated data table at the appropriate locations in each letter during printing. In Microsoft Word, this process is known as Mail Merge.
We have already tried to replicate this process in Microsoft Access using VBA some time ago. The links to those posts are provided below for your reference:
The Names, Addresses, and other related information of each individual can be kept in a data table. The external Table can be prepared in any of the following Applications:
- MS Word Document with Table Grid.
- MS Excel Data Table.
- MS Access Table.
The Table can be linked to the Form Letter so that MS Word can pick the required information from the Table and insert it where table field codes are placed in the body of the letter.
Sample MS Word Table.
We have prepared a small Table in an MS Word Document within the Table Grid, as an example in case you prefer to prepare one in Microsoft Word itself. Here is an image of the data table with only 3 entries, similar to the one in the Northwind Employees Table.
MS Access Employees2 Table.
But we will be using a modified Employees Access Table prepared with a few new fields for our Mail Merge Demo Run, taken from the original Employees Table in the Northwind sample database. The image of the Employees2 Access Table is given below:
Create the Employees2 Table from the Northwind Employees Table, with the selected fields in your Database. Create three more fields and fill them with sample data as shown in the above image.
Sample Letter Specimen.
A Specimen image of the Letter, without the individual Employee details, is given below:
Click the Image to enlarge it.
Create a Letter in Microsoft Word similar to the specimen above, and save it as Employees.
Sample Merge-Printed Letter.
When we merge-print the personal information in the Document, from the Access Table, it will look like the sample image given below:
Click the Image to Enlarge.
Inserting the Merge Fields in the Document.
Let us prepare a Mail-Merge Document in MS Word, using the Employees2 Table as Source Data, matching the above-merged letter model.
We assume that the above employees of our organization are awarded an annual increment in recognition of their commendable contributions to the company’s overall growth and to encourage them to perform better by taking on future challenges.
Open the Employees Word Document, as per the above specimen, created and saved earlier. If you have not yet created one, let us do it now.
Click on Start Mail-Merge from the Mailings Menu and select Letter.
Click on Select Recipients and select Use Existing List.
Find the Database where you saved the Employees2 Table and click Open.
The list of Tables is displayed; select the Employees2 Table, and click OK to select it, and the Employees2 table is attached to the Document.
Next, click Edit Recipient List to display all records in the Employees2 table. A checkmark indicates that an employee record is selected for inclusion in the merged letters. If an employee should be excluded from the letters, clear the checkmark for that record.
First, we will insert the Address Block and Greeting Line information into the letter using the Merge Fields selected from the attached table. Although these elements can be inserted in two simple steps using the automatic options, we will take a more manual approach to customize the output and avoid complications if the automatic method is not fully understood by the first-time user. You can try the automatic method later by selecting the Address Block and Greeting Line buttons and experimenting with their available options to modify the document output.
Click below the Reference information (Ref: ABC/HRD/2020/1234-) and above the word Dear on the Letter.
Click on the Insert Merge Field option in the Mailings Menu and click on the First Name Field. Open the same list again and select the Last Name.
Click in the middle of both field codes and tap the space bar to insert a space between the FirstName and LastName segments of the Employee Name and position the insertion point on the following line.
Select Address from the Merge-Field list for the second line.
Select City for the third address line.
Insert State/Province and Country/Region on the next line, with a space in between.
Position the insertion point after the word Dear with a space, and insert the First Name and Last Name fields with a space in between.
After inserting the merge fields, it will look like one of the images below, based on the Field-Code display option setting ALT+F9.
OR
The second image is the expanded form of Merge Field Codes. You can toggle between these two states by pressing ALT+F9. See that the Preview Result button is in the deselected state. If the Preview Result button is active, then the actual data will be displayed.
Position the insertion point on the Designation line and insert the Job Title merge field.
On the next three lines, insert Pay, Grade, and Wef (stands for with-effect-from) Fields.
When completed, it will look like the following image:
We need two more pieces of data for the Reference Sequence Number and Letter Date.
Position the insertion point at the end of the existing reference number, immediately after the dash character (Ref: ABC/HRD/2020/1234-).
Select Page Number from the Insert Menu and select Current Position from the displayed list.
Position the insertion point after the word Date:.
Click on the Date & Time option, under the Insert Menu, and select one of the date format options displayed.
Press Ctrl+S Keys to save the Word Document with the changes.
Merge-Printing the Letter.
Now, we are ready to merge-print the Document for each selected employee on the Employees2 Table.
- Click the Preview Results button, with the Zoom Glass image, under the Mailings Menu.
- If it is still on Field-Code display, then press ALT+F9 Keys to fill in the first Employee data wherever we have inserted the Merge-Fields on the Document.
- Now, you are viewing the sample Document you will get when you merge-print the Document, on the Printer, one Document per employee.
- Now, click the Finish & Merge button and select Edit Individual Document..., and select All.
All the Documents are merged with Employee information, and you are allowed to edit the pages if needed. Can insert spaces between lines or the inserted information in Bold letters.
Once you are finished editing, select Print from the Office Button, then Preview or Print the Document on Paper.
If you have selected Send Email Message at Step 4 above, then you can send the Document as an Email Message.
