Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Word Mail-Merge with Ms-Access Table

Introduction.

Form Letters.

Form Letters are prepared from a standard Microsoft Word template merged with addressee-related information filled in on each letter.  Appointment letters, call letters for interviews Employment Contracts, etc., are some of the examples, and easier to prepare them in Ms-Word Mail-Merge mode, rather than composing them individually for each addressee.

These types of Letters will have some common body text, applicable to all the recipients, and will have specific areas where personalized information can be inserted, like Name & Address, interview dates, or the nearest location where the candidates can attend the interview, and so on.

If the interview calls should go to hundreds of prospective candidates, then we need to prepare only one letter with standard body text applicable to all recipients.  Other personalized information can be automatically inserted from the attached data table, at appropriate places on each letter, at printing time.  This process, in MS Word, is known as Mail-Merging.

We have already made an attempt to mimic this process in Microsoft Access itself with the help of VBA, some time back, and the links of those posts are given below for your reference:

  1. Ms-Access and Mail-Merge
  2. Ms-Access and Mail-Merge-2
  3. Ms-Access and Mail-Merge-3

The Names, Addresses, and other related information of each individual can be kept in Data Table.  The external Table can be prepared in any of the following Applications:

  1. MS-Word Document with Table Grid.
  2. MS-Excel Data Table.
  3. 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 them where Table Field-Codes are placed on the body of the letter.

Sample MS-Word Table.

We have prepared a small Table in MS Word Document, within the Table Grid, as an example in case if 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.

Click on the Image to Enlarge.


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, from the original Employees Table in the Northwind sample database.  The  image of the Employees2 Access Table is given below:

Click on the Image to Enlarge.

Create the Employees2 Table from the Employees Table of Northwind, with the selected fields in your Database.  Create three more fields and fill them up 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 given above and save it with the name: Employees.

Sample Merge-Printed Letter.

When we merge-print the personal information on the Document, from Access Table, it will look like the sample image given below:

Click the Image to Enlarge.

Inserting the Merge Fields on Document.

Let us prepare a Mail-Merge Document in MS Word, with the above 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 appreciation of their commendable contribution to the Company’s overall growth, and encourage them to do better by taking up future challenges of the company.

  1. Open the Employees Word Document, as per the specimen given above, created and saved earlier.  If you have not yet created one, let us do it now.

  2. Click on Start Mail-Merge from Mailings Menu and select Letter.

  3. Click on Select Recipients and select Use Existing List.

  4. Browse and find the Database where you have the above Employees2 Table and Click Open.

  5. The list of Tables is displayed, select Employees2 Table, and click OK to select it.  Now, the Employees2 table is attached to the Document.

  6. Next, if you click on the Edit Recipient List, it will display all the records in the Employees2 Table with checkmarks indicating that all the employee records are selected for Merge-Printing the Letters.  If any employee is to be exempted from the letter, then remove the check mark on that item.

    First, we will insert the Address Block and Greeting Line information on the Letter, from the Merge-Fields, selected from the attached Table.  Even though we can insert these into sections in two simple steps, we will do it the hard way to make things to our liking and less complicated, if the automatic method is not properly understood, by the first-time user.  You may try them later by selecting Address Block and Greeting Line buttons and their optional settings to modify the output of the document.

  7. Click below the Reference information (Ref: ABC/HRD/2020/1234-) and above the word Dear on the Letter.

  8. 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.

  9. Click in the middle of both field codes and tap the space bar to insert a space between the First-Name and Last-Name segments of the Employee Name and position the insertion point on the following line.

  10. Select Address from the Merge-Field list for the second line.

  11. Select City for the third address line.

  12. Insert State/Province and Country/Region on the next line, with space in between.

  13. Position the insertion point after the word Dear with space, and insert the First Name and Last Name fields with space in between.

    After inserting the merge fields it will look like one of the images given 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 Keys.  See that the Preview Result button is in the de-selected state.  If the Preview Result button is active then the actual data will be displayed.

  14. Position the insertion point on the Designation line and insert the Job Title Merge-field.

  15. 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 Date of the Letter.

  16. Position the insertion point at the end of the existing reference number, immediately after the dash character (Ref: ABC/HRD/2020/1234-).

  17. Select Page Number from Insert Menu and select Current Position from the displayed list.

  18. Position the insertion point after the word Date:.

  19. Click on the Date & Time option, under Insert Menu, and select one of the Date-format options displayed.

  20. 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.

  1. Click on the Preview Result button, with the Zoom Glass image, under Mailings Menu. 
  2. 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.
  3. Now, you are viewing the sample Document you will get when Merge-Print the Document, on the Printer,  one Document per employee.
  4. Now, Click on 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 inserted information can be in Bold letters. 

Once you are finished editing, select Print from 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.

  1. MS-Access and E-Mail
  2. Invoke Word-Mail Merge from Access2007
  3. Automated Email Alerts


Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code