Mail Merge Using Excel Data

[hlewton]

I am having a problem with a mail merge that I have not run into before. I am using 5160 Avery labels, which is what I always use. I am using an Excel spreadsheet as the source of my data. The column headings are:
Title Last Name First Name Middle Initial II Jr AuxiliaryName Street Address Street Address2 City State Zip Code
State Zip Code

In the document I use to make the merge I choose the 5160 labels then select the recipients by choosing the Excel spreadsheet mentioned above. I then insert these merge fields on the first line Title, First Name, and last Name. On the second line I insert the merge field AuxiliaryName, which not every label will have that field. Third I insert Street Address. On the final line I insert City add a comma manually after City, State, (2 spaces) then Zip Code.

This is how I have set up merges for many years now but never encountered this problem. On the first page of the printed labels everything is fine, no blank spaces even though only 2 of the 30 labels actually have data in the AuxiliaryName field. On the second page of the labels all labels are showing a blank line in the AuxiliaryName field and a comma on the remaining labels that should be blank since I am not using enough address to completely fill out the second page of labels.

The first attachment is the merge document and the second attachment is the second page of the merged document after the merge was performed.

Why is only the first page of labels merging correctly and how can I fix this problem?

Thanks

I tried actually uploading the actual Word files but it said they would not download so I made pdf files and 1 was way too big so I turned it into a jpg file and resized it so I hope it is still readable. Just found out the same was true for a pdf file so now they are both jpg files. I do hope they are readable.

Merge.jpg

labels.jpg

[HansV]

Check your Excel data carefully - could there be a space or something like that in the blank looking cells in the AuxiliaryName column?

[hlewton]

Check your Excel data carefully - could there be a space or something like that in the blank looking cells in the AuxiliaryName column?

I will check that and get back immediately. Nope absolutely nothing. First I checked them one by one since there are so few addresses and then I did "end down" and "end up" and the only cells it stopped on were the ones with actual data in them. Those that do have actual data in them all printed out fine on the first p[age of the labels and so did the labels that had no data in that field. It was only on the second page of the labels that this blank line appeared and it appeared on every label on that page.

Not sure if it matters and I have also done this many times, the Excel spreadsheet was created by exporting the data from an Access table and then edited to only contain the addresses of those I wanted to create labels for in this usage.

[hlewton]

As would be expected if I remove the AuxilaryName field all prints as expected with the exception of all labels that are to remain blank have a comma printed on them which I have also never noticed happening in the past since I always use a comma between the fields of City and State.

[hlewton]

OK something has definitely changed in the way Word is doing merges. I just did a merge with a file I have used for years and years with the help of those here in the lounge. I always merged correctly but not now. It too places blank lines on the last page of the labels but it does not print the comma on those blank labels. This merge is tied to an Access table as it always has been. So something is not right in Word itself I believe. It is always blank where the field sometimes has data and other times it does not but all the other pages, which there are 4 total in this example, print fine, just the last page is messed up.

[hlewton]

With further experimenting I discovered this problem happens on the first page of the document if there is only 1 page of labels in the entire document. Also I do have the option set to "Don't print blank lines when data fields are empty." In addition in searching the NET I came across a suggestion to us "Alt F9" and place a (/b " ") inside the field that does not always have data in it. I reran the merge it it still did the same thing leaving the blank lines on the last page of labels.

[hlewton]

I ran 1 more experiment and hopefully this will help with ideas. Using older versions of Word and Access with extensions of *.doc and *.mdb I ran the mail merges on a machine using Office 2003 and they worked perfectly. Using the exact same files on the machine using Office 365 - Word and Access 2016 they still created the error of blank lines. So since the same problem exists whether using Access or Excel as the databases it seem the problem stems from Word. Hope there is a solution.

Thanks.

[HansV]

Using the \b switch as in the screenshot below works for me:

S2647.png

I wanted the strRegion field to be on a separate line if populated, but suppressed if blank.
After pressing Alt+F9 to display field codes, I added \b followed by a space and quotes, then pressed Enter (shown as ¶ in the screenshot) and entered quotes, all inside the field.
Note that there is NO paragraph mark after the strLastName field.

[HansV]

You'll probably have to use an IF field for the last line:

{ IF { MERGEFIELD City }<>"", "{ MERGEFIELD City }, " "" }

[macropod]

{ MERGEFIELD City \f ", "}

[HansV]

That's better!

[hlewton]

You'll probably have to use an IF field for the last line:

{ IF { MERGEFIELD City }<>"", "{ MERGEFIELD City }, " "" }

I have been experimenting with your suggestions and though the feild I need to alter is the third line of the merge even if I highlight it I cannot see it to alter without expanding the distance between the space of the labels. So I tried that and have used the \b " " but it did no good. I am not too worried about the comma after City but the other 2 merge fields of AuxiliaryName and StreedAddress2 are the ones I need to suppress. I am really confused right now so if you could, would you use the AuxiliaryName field in an example of the If field? Also can I edit the field any way other than greatly expanding the distance between labels to see it and then hoping to set it back to the proper spacing? I have found no way to see what I am trying to edit except expanding the space even though I highlight that field before hitting the Alt F9 keys. I think I can get the comma fixed with yours and macropod's suggestions.

I probably should tell you I have tried this syntax, { IF { MERGEFIELD Address2 } = "" "" "{MERGEFIELD ADDRESS2}<line break>" }, but with my fields both copying it from a Word document and trying to type it manually into the merge field but even after I hit the Alt F9 the second time the code is still showing

[HansV]

Try the following.
First, turn on Show/Hide on the Home tab of the ribbon (the ¶ button should be highlighted)
After pressing Alt+F9 you should see something like this:

{·MERGEFIELD·Title·}·{·MERGEFIELD·"First·Name"·}·{·MERGEFIELD·"Last·Name"·}¶
{·MERGEFIELD·AuxiliaryName·}¶
{·MERGEFIELD·"Street·Address"·}¶
{·MERGEFIELD·City·},·{·MERGEFIELD·State·}··{·MERGEFIELD·"Zip·Code"·}

Delete the paragraph mark after the Last Name field and insert /b " after AuxiliaryName (inside the field), press Enter and type "
Also delete the comma and space after the City field, and insert /f ", " after City (inside the field).
It should now look like this:

{·MERGEFIELD·Title·}·{·MERGEFIELD·"First·Name"·}·{·MERGEFIELD·"Last·Name"·}{·MERGEFIELD·AuxiliaryName·/b·"¶
"}¶
{·MERGEFIELD·"Street·Address"·}¶
{·MERGEFIELD·City·/f·",·" }{·MERGEFIELD·State·}··{·MERGEFIELD·"Zip·Code"·}

Press Alt+F9 to hide the field codes. Select everything and press F9 to update the fields.

[hlewton]

Thank you. I am getting closer but still having the problem with the comma. I will try to get it right but this did seem to get rid of the blank lines. Still I have to ask once more; after hitting the Alt F9 keys how can I see the fields to edit without expanding the height the label is supposed to be? After expanding it to an extreme height I can see the fields but it is a guess getting it back to the correct height. Also with saved merges after editing as above is there a way to update the entire document to the changes. I selected all and then pressed F9 and that didn't update any labels except the one I did the editing in.

[HansV]

The field codes often take up more space than the text itself. You might decrease the font size and crank up the zoom percentage. Afterwards reversing this might be easier than messing with the label height.

[hlewton]

The field codes often take up more space than the text itself. You might decrease the font size and crank up the zoom percentage. Afterwards reversing this might be easier than messing with the label height.

Thanks. Not sure you saw this because I edited probably while you were posting. Also with saved merges after editing as above is there a way to update the entire document to the changes. I selected all and then pressed F9 and that didn't update any labels except the one I did the editing in.

In addition I am not getting the {·MERGEFIELD·City·/f·",·" } to work at all. It places no space between City and State and no comma.

[HansV]

I hope that you understood that the · characters represent spaces.

[HansV]

Try clicking the Update Labels button on the Mailings tab of the ribbon.

[hlewton]

OK thanks for all your help.

[macropod]

It should now look like this:

{·MERGEFIELD·Title·}·{·MERGEFIELD·"First·Name"·}·{·MERGEFIELD·"Last·Name"·}{·MERGEFIELD·AuxiliaryName·/b·"¶
"}¶
{·MERGEFIELD·"Street·Address"·}¶
{·MERGEFIELD·City·/f·",·" }{·MERGEFIELD·State·}··{·MERGEFIELD·"Zip·Code"·}

Press Alt+F9 to hide the field codes. Select everything and press F9 to update the fields.

In addition I am not getting the {·MERGEFIELD·City·/f·",·" } to work at all. It places no space between City and State and no comma.

Aside from the spaces issue, you have the switch separator the wrong way around - you should have {MERGEFIELD City \f ", "}