Solved

VBA to mail merge multiple documents from one data set

Posted on 2016-08-24
3
61 Views
Last Modified: 2016-09-13
Hi All,
I need some guidance on the mail merge to create multiple pdf documents using vba. Example: From the below  sample table i need to create letters in PDF ( if possible with password protection) based on the country column. The result should be 2 PDF seperate documents USA file will have 5 and UK 4 letters ( based on number of records in each country).

First Name      Title                                       city            Country
Davolio            Sales Representative      Seattle            USA
Andrew              Vice President, Sales      Tacoma            USA
Janet               Sales Representative      Kirkland            USA
Margaret      Sales Representative      Redmond      USA
Steven              Sales Manager            London            UK
Michael              Sales Representative      London            UK
Robert              Sales Representative      London            UK
Laura              Coordinator                      Seattle            USA
Anne              Sales Representative      London            UK

Appreciate your help.
0
Comment
Question by:S Kumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
xtermie earned 400 total points (awarded by participants)
ID: 41769845
You can actually do this without VBA by using query/filter parameters in the mail merge Select recipients where you can set Country = USA and do the merge, save the resulting document into a single PDF and name it USA and then change the query/filter parameter to UK and repeat and save that as UK.pdf - see attached screen shot for query parameter.  To do this:
1)  On Mailings click Edit Recipient List
2) Click Filter
3) Select the Field you want to filter by and the value and press OK
4) Press OK

If you need each document to be separate or something more detailed there is a free add in that you can use named MergeTools – 20150422.dotm Add-in that Doug Robbins - Word MVP has created from the following page of hisOne Drive: http://bit.ly/1hduSCB

The MergeTools – 20150309.dotm file needs to be saved in the Word Startup folder.  In Windows Vista and Windows 7, 8 or 8.1, the default location for that folder is

 C:\Users\[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

The requirements for using Doug's add-in/system are:

1.The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.  

2.For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.  For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility

3.For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.

4.For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

5.For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.
0
 

Assisted Solution

by:S Kumar
S Kumar earned 100 total points (awarded by participants)
ID: 41770511
Thank you for the solution. I had tried this method for small database. However for merging  large database this will be a repetitive work. I will also try the add in suggested above.
Any other solutions will be appreciated.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41795683
Good Comments and recommendation made
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question