Forum Discussion
Using Mail Merge with MACROS
Hello,
I am currently trying to use Mail Merge from EXCEL with a WORD document to be emailed in OUTLOOK
The emailed document takes the form of a .pdf attachment
Our designer has created a Customer Order Update document that requires a WORD MACRO to realise his formatting once the Merge has taken place.
I have created a MACRO in the WORD document that is able to perform the correct formatting on the WORD document after the merge with EXCEL spreasheet data has taken place.
However, with a multiple page document I find that the MACRO is only applied to the 1st page of the document
Subsequent pages of the generated .pdf do NOT have the MACRO applied
( I have various detail lines that are hash preceded capital letters that trigger the MACRO to perform a subtitle substitution )
As the MACRO goes through 25 lines of detail (below the fixed document header) in a Loop to perform substitutions I wondered if expanding the Loop to 50 lines would allow the second page of the document to also receive the MACRO processing. This did NOT happen
Please advise how I can get the MACRO to apply to every page of the mail merged document
I do not believe that the MACRO can only be applied once to the 1st page of the mail mereged document only
regards
8 Replies
Use my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from:
Extract the files from the archive and read the:
“READ ME – Setting up and using the Merge Tools Add-in.pdf
to see how to install and use the various tools.
Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:
- Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message . The email messages can, if necessary, also be sent to CC and BCC addresses and the subject of the message can include data from a field in the data source.
- Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
- Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
- Merging to a document that will include a chart that is unique to each record in the data source
- Merging a document with Content Controls
- Merging a document that contains Legacy FormFields
- Duplex Merges
- Merging to a printer that will collate and staple the output created from each record in the data source.
The requirements for using the system are:
- The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.
- 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. For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility
- 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.
- 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.
The MergeTools Add-in will NOT work with the “New Outlook”, which is just a re-badged version of Windows Mail and like its predecessor, it does not support automation and hence will not send the messages created by the MergeTools Add-in.
Be aware, that any messages that you had tried to send with the “New Outlook” will have been placed in the Outbox of the original Outlook and they will be sent as soon as you revert to that version of Outlook. If you do not want that to happen, you should put Outlook Off-line, or disconnect your computer from the Internet.
For a demonstration of the use of the facility, prepared by a Microsoft employee, see
It sounds like the macro is only processing a fixed range instead of the entire merged document. Rather than looping through a set number of lines, update the macro to loop through all paragraphs or the full document after the mail merge is complete. Then generate the PDF only after the formatting has finished. That way, every page should receive the same formatting.
Can you respond with the code that you have in the macro so that we can make some appropriate suggestions.
Hi Doug
Thanks for your reply.
It is a very basic Macro that runs through a table and on certain # prefixed monikers in the 1st cell triggers the generation of certain formatted headers or footers (and a blank line)
When I was informed that the merge would combine different pages together I wondered if new Tables would have been added on subsequent pages , so I tried changing the Table index on an inner loop going through the number of pages in the document on the outer loop. But the Macro did not recognize any additional Tables. I tried a string search to the #monikers, but only the monikers on the first page were processed.
I tried a Macro record of the Merge page forward operation. But this only worked for the interactive Macro. When I tried the mail merge posting for real the Macro crashed. I am not an adept with VBA Macro in WORD I have always used EXCEL in the past.
The above is the start of the macro, where it clears any previous subtitles generated
It carries on processing through Table 1 doing substitutions like a cascading style sheet
And it ends further down with a default catchall section before it ends
Very simple, works perfectly on the first page. And leaves subsequent pages alone.
I am sure it is something straightforward to solve for people who know how the merge document can be addressed once it is constructed over several pages.
Thank you for taking a look.
Sticking my two bits in here...
Try using Range rather than selection and cycle through Word sections.
A mail merge will create a new section for each record. (If the primary mail merge document has multiple sections, you will need to account for that as well.
Hi! A Word macro is not applied separately to each merged page—the merged result is one document containing sections. Run the formatting macro after MailMerge.Execute, loop through the resulting document's sections or paragraphs rather than a fixed number of lines, and export the completed result to PDF only after that loop finishes.
Hello Jamony
I do not know how to address the next section to apply the MACRO. Although the resulting pdf occupied two pages addressing the second page using (an absolute setting for test purposes):
Selection.GoTo wdGoToPage, wdGoToAbsolute, 2
merely resulted in the MACRO applied to the 1st page again, which is what you suggested could happen
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord - this worked interactively but would not work with the actual merge to create the document, as I presume merge would no longer be active then, and even then some changes from the previus page were carried over
I had two tables on a single page so I wondered if the merge would generate extra tables on the next page for the same document
Thus, I used:
ActiveDocument.Tables(1).Cell(index, 1).Select
To select the 1st cell in the table on the 1st page
I wondered if new Tables were created for subsequent pages in the same active document
when I ran
ActiveDocument.Tables(3).Cell(index, 1).Select
the table was not found
I tried to see if the SECTIONS property of ACTIVEDOCUMENT could be used
ActiveDocument.Sections.Count = 2
An attempt at using this to navigate resulted in an error applying an assignment to a read only property
I still seem to be trapped applieing my macro to the 1st page of the document
regards
I do not know how to address the next section to apply the MACRO. Although the resulting pdf occupied two pages addressing the second page using (an absolute setting for test purposes):
Selection.GoTo wdGoToPage, wdGoToAbsolute, 2
merely resulted in the MACRO applied to the 1st page again, which is what you suggested could happen
I still seem to be trapped applieing my macro to the 1st page of the document
It is important to distinguish between pages and sections. A letter merge will create at least one new section for each record, more if the primary merge document contains more than one section.
Page numbering is likely to restart with each newly created section break.
