Word VBA to add variable bookmarks in mail merge

[f_elix]

Hello all,

I've got an Access select query with the following output:

[TABLE="class: grid, width: 550"]
[TR]
[TD]User[/TD]
[TD]City[/TD]
[TD]Travel[/TD]
[TD]Type[/TD]
[TD]Place[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]Paris[/TD]
[TD]private[/TD]
[TD]must see[/TD]
[TD]Eiffel Tower[/TD]
[TD]user1Parisprivate[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]Paris[/TD]
[TD]private[/TD]
[TD]art[/TD]
[TD]Louvre Museum[/TD]
[TD]user1Parisprivate[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]Paris[/TD]
[TD]private[/TD]
[TD]art[/TD]
[TD]Orsay Museum[/TD]
[TD]user1Parisprivate[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]Paris[/TD]
[TD]business[/TD]
[TD]must see[/TD]
[TD]Eiffel Tower[/TD]
[TD]user1Parisbusiness[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]Paris[/TD]
[TD]business[/TD]
[TD]biz[/TD]
[TD]La Defense[/TD]
[TD]user1Parisbusiness[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]London[/TD]
[TD]private[/TD]
[TD]must see[/TD]
[TD]National Gallery[/TD]
[TD]user2Londonprivate[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]London[/TD]
[TD]private[/TD]
[TD]art[/TD]
[TD]National Gallery[/TD]
[TD]user2Londonprivate[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]London[/TD]
[TD]private[/TD]
[TD]art[/TD]
[TD]Madame Tussauds[/TD]
[TD]user2Londonprivate[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]Rome[/TD]
[TD]business[/TD]
[TD]must see[/TD]
[TD]Colosseum[/TD]
[TD]user2Romebusiness[/TD]
[/TR]
[/TABLE]

I would like to get separated Word documents for each user, city & travel (or Customer), preferably with the bookmarks for each entry in 'Place' column. Of course I don't want to see duplicated items (like National Galery for user2) twice but I managed to avoid them (using grouping in the query). I need the bookmarks because I want VBA code to go to another Word document called e.g. Eiffel Tower and copy its content.

I had similar task previously (but without duplicates and with smaller amount of data) for which I combined Word mail merge IF rule, bookmarks and VBA.

IF rule:

{If {airport}="" "" "airport name"} followed by bookmark 'airport'


A piece of VBA code:

    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        ActiveDocument.MailMerge.SuppressBlankLines = True
        ActiveDocument.MailMerge.DataSource.ActiveRecord = i
    
'variables set here
city = ActiveDocument.MailMerge.DataSource.DataFields("city").Value
airport = ActiveDocument.MailMerge.DataSource.DataFields("airport").Value

InputDir = "C:\\location" & "\\" & city
ChangeFileOpenDirectory InputDir
    
'Set wdApp = GetObject(InputDir, "Word.Application")
Word.Application.Documents.add Template:=InputDir & "\" & city, NewTemplate:=True, Visible:=True 'uses different template for each city

'updates bookmarks
        With ActiveDocument
        On Error Resume Next
            .Bookmarks("airport").Select
            Path2 = "C:\\location" & "\\" & city & "\\" & airport name & ".docx" 
            Selection.Collapse Direction:=WdCollapseDirection.wdCollapseEnd
            Selection.InsertFile FileName:=Path2, ConfirmConversions:=False
    
'mail merge - data from Access query
            With .MailMerge
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = i
                    .LastRecord = i
                    .ActiveRecord = i
                End With
                .Execute Pause:=False

I ended here up with single document for each record from the access and was quite happy with the output.

But I'm struggling now to use it for the query with duplicated entries (no, I can't just delete them). I was working on Mergeseq (added 'Customer' as a key here) but somehow it doesn't see the change in the Travel column. In result I get everything that belongs to user1 in one document (should be in two docs, one for each 'Travel').

{IF {MERGESEQ}=1"
{MERGEFIELD user} {MERGEFIELD city} {MERGEFIELD travel}" ""}{SET Customer1 {MERGEFIELD Customer}} {IF {MERGEFIELD Customer1}<>{MERGEFIELD Customer2} "{MERGEFIELD user} {MERGEFIELD city} {MERGEFIELD travel}" "
{MERGEFIELD type} {MERGEFIELD place}"}
{SET Cusomer2 {MERGEFIELD Customer}}

Maybe it would work if I put the {MERGEFIELD Customer} right here:

{IF {MERGESEQ}=1"{MERGEFIELD Customer}" ""[CODE]
but I don't want it to be visible in the output file.

Obviously, I can't use the VBA code:

[CODE]For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount

because it counts all the records. I was thinking that maybe there's a way to count only different entries in the Customer column, but I can't find any (would SQL select distinct work here?).

Another thing is that I need to paste description of each 'Place' that applies for a user and travel. Previously I set bookmarks manually but I don't think is feasible here, as I'd need to have as many bookmarks as the records in the 'Place' column for each city and the number will be quite big. Adding a column in Access with the description is not an option as there are some pictures and formatting that would disappear. Have you got any ideas how to do this?

So summing up my long post, I need only two pieces of advice

1. how to make Word to merge records separately for each entry in Customer column
2. how to set variable bookmarks for each 'Place' in the documents merged in1.

I've been thinking on that for several days already and can't find any solution other than stated above. Anyone can help?
Thanks in advance!

 

[Macropod]

For the field coding, you might try:
{QUOTE{IF{MERGESEQ}= 1 "{SET Key1 ""}{SET Key2 ""}"}"{IF{={IF{MERGEFIELD City}= {REF Key2} 1 0}*{IF{MERGEFIELD Travel}= {REF Key1} 1 0}}= 0 "{IF{MERGESEQ}> 1 "==Section Break=="}{MERGEFIELD Travel}{SET Key1 {MERGEFIELD Travel}}
" }{IF{MERGEFIELD City}<> {REF Key2} "
{MERGEFIELD City}{SET Key2 {MERGEFIELD City}}" " '' ''"} {MERGEFIELD Customer} {MERGEFIELD Place} {MERGEFIELD Type}
"}
where ==Section Break== is an actual Section break. You could then use code like the 'Split Merged Output to Separate Documents' example in the Mailmerge Tips and Tricks threads at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
to split the output to separate files.

You also refer to the need for bookmarks so you can insert conditional content. That won't work with the mailmerge, because any bookmarks you have in the mailmerge main document will be deleted from the output files. You could instead us field coding within the mailmerge main document for the conditional insertion - either from within the field itself or from another document. For example, you might use field coding like:
{IF{MERGEFIELD City}= "Rome" "Additional content for Rome"}
Similarly, you could replace "Additional content for Rome" with an INCLUDETEXT field pointing to an external document.

For more on the field coding possibilities, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
Microsoft Word Catalogue/Directory Mailmerge Tutorial | Windows Secrets Lounge
or:
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some worked examples, see the attachments to the posts at:
mail merge with duplicate names but different dollar amounts
Access to Word, Creating a list from multiple records
Word 2010 Merge from excel into Table Directory | Windows Secrets Lounge

 

[f_elix]

Hi Paul,
Thanks a lot, that really helped. I wish I had asked this question a week ago
And the Tutorial is also great, I'm sure I'll come back to it each time when dealing with more advanced Word tasks.

Finally I end up with something like that:

{QUOTE{IF{MERGESEQ}= 1 "{SET Key1 ""}{SET Key2 ""}{SET Key3 ""}"}{IF{={IF{MERGEFIELD City}= {REF Key2} 1 0}*{IF{MERGEFIELD Travel}= {REF Key1} 1 0}
*{IF{MERGEFIELD Type}= {REF Key3} 1 0}}= 0  {{IF{MERGESEQ}> 1 “” “”}{MERGEFIELD City}}}{IF{MERGEFIELD Travel}<> {REF Key2} "====SB======
{MERGEFIELD Type}” {IF {MERGEFIELD Type} <> {REF Key3}” {MERGEFIELD Type}” “”} “”}” “{INCLUDETEXT “directory\\{MERGEFIELD City}\\{MERGEFIELD Place}.docx” \*MERGEFORMAT}}
{SET Key1 {MERGEFIELD Travel}}{SET Key2 {MERGEFIELD City}}}{SET Key3 {MERGEFIELD Type}}

It's enough if the entries are grouped by city/travel but I don't really need to see them (they will be in a different document). Didn't try to save them in separate documents yet, but I've seen many threads on that so I believe I will manage. However, I still have some questions.

Firstly, the last City is not separated by the section break from the previous one. Seems to me that it's because the Type of the both journeys is the same and the City information is not taken into account. I was trying to add an IF statement but I was getting each Type entry in a separate page. I did something like that:

{{IF{MERGESEQ}> 1 “” “”}}}[B]{IF{ MERGEFIELD City}<>{REF Key2}[/B] {IF{MERGEFIELD Travel}<> {REF Key2} "====SB===== 'rest of the code remains the same

Could you advise please where to put it?

Secondly, when performing the merge I'm getting error message: 'A field calculation error occured in record 1.' and it pops up for every record in the database. What's more, in the merged file the first page is blank, having only the section break. Probably it's some stupid mistake I made. Have you got any idea what it can be?

And the last point, as you probably imagine: INCLUDETEXT. Of course I've got different formatting than expected. I've found your comment on the very same issue here: IncludeText - How to keep paragraph format of the source document - Microsoft Office Word Forum - WordBanter but wanted to check something with you... When stating that the solution is to '.. use a set of attributes in the source document that don't exist in the target document for the linked material' do you mean that I'd need to go to each source document and apply a style to each paragraph with different formatting? I gave a try to style set in the design tab but it didn't work. Is there any way to overcome that? Unfortunatelly, I can't use the source files' formatting in the merged file either Kindly advise, please.

Thanks in advance!

 

[Macropod]

I see you're trying to implement splits based on the use of 3 keys, rather than the two I suggested. Perhaps you could explain how your keys are intended to work compared to how the two-key approach in the tutorial works. That way hopefully I'll get a better understanding of what your requirements are.

As for saving to separate documents, do be aware that most of the threads you'll read on that concern letter merges. Even the links I pointed you to contain code for that. However, code that generates separate documents as part of a letter merge execution won't work with a directory merge. Hence referring you to code for a post-merge split.

The field calculation error may be related to your field coding - what you've posted can't work, as the number of opening braces doesn't match the number of closing parentheses and it's impossible to create fields with such structures.

Finally, regarding the INCLUDETEXT fields, you should ensure the source and target documents use the same Style definitions or, if that's not possible, that they don't use the same Style names for the content being imported.

 

[f_elix]

Thanks Paul,

I will work on Styles for the documents. I wanted to avoid manual correction of all the files but if there's no other way I need to do this In terms of the error msg I think I had sth like "{SET Key1 = ""} and that was causing the error. For the saving, I'll try first with some VBA code. I think already I saw some threads about that so it shouldn't be an issue.

As for the code itself, I came back to your Tutorial and adjusted it a bit. Actually that's why I created the 'Customer' column - it's just tying entries from three first columns. What I've got so far is:

{QUOTE{IF{MERGESEQ}= 1  "{SET Key1 ""}"}     '====> setting the key for Customer
{IF{MERGEFIELD  Customer} <> {REF Key1} "{IF{MERGESEQ}> 1  "====SB======{MERGEFIELD Type}”      '====> check if Customer changes, if so start new section with the first Type on top
"{MERGEFIELD  Type}”}"   '====>Customer changes, but set a new Type only (that's for the first  record, if I'm not wrong)
 "{IF {MERGEFIELD Type} <> {REF Key2}  ”{MERGEFIELD Type}”  “”}”}”      '====> if Customer  doesn't change, check the Type, if changes, put it's name, if it doesn't  do nothing
 “{INCLUDETEXT “directory\\{MERGEFIELD City}\\{MERGEFIELD Place}.docx” \*MERGEFORMAT}}        '====>Includetext stuff
{SET Key1 {MERGEFIELD Customer}}{SET Key2 {MERGEFIELD Type}}                       '====>set keys

What I'm trying to get is:

(user1Parisprivate - this is the key, not visible in the output doc)
must see
Eiffel Tower
art
Louvre Museum
Orsay Museum
"====SB===="
(user1Parisbusiness)
must see
Eiffel Tower
biz
La Defense
"====SB===="
(user2Londonprivate)
must see
National Gallery
art
Madame Tussauds
"====SB===="
(user2Romebusiness)
must see
Colosseum

and I'm almost there, but the code doesn't separate user1Parisprivate from user1Parisbusiness. I mean, I see the records in the right order but there's no section break between. That's a bit weird as the key (Customer field) differs. I went through the code several times but can't find a bug

 

[Macropod]

Try:
{QUOTE{IF{MERGESEQ}= 1 "{SET Key1 ""}{SET Key2 ""}"}"{IF{IF{MERGEFIELD Customer}= {REF Key1} 1 0}= 0 "{IF{MERGESEQ}> 1 "==SB=="}{SET Key1 {MERGEFIELD Customer}}"}{IF{MERGEFIELD Type}<> {REF Key2} "{MERGEFIELD Type}{SET Key2 {MERGEFIELD Type}}" " '' ''"}<tab>{MERGEFIELD Place}
"}
That should give you an output like you described. As for the INCLUDETEXT, you might try:
{QUOTE{IF{MERGESEQ}= 1 "{SET Key1 ""}{SET Key2 ""}"}"{IF{IF{MERGEFIELD Customer}= {REF Key1} 1 0}= 0 "{IF{MERGESEQ}> 1 ""}{INCLUDETEXT "directory\\{MERGEFIELD City}\\{MERGEFIELD Place}.docx"}{SET Key1 {MERGEFIELD Customer}}"}{IF{MERGEFIELD Type}<> {REF Key2} "{MERGEFIELD Type}{SET Key2 {MERGEFIELD Type}}" " '' ''"}<tab>{MERGEFIELD Place}
"}
Note where you'd need to use tabs for alignment purposes - along with the corresponding tab-stops.