Mail Merge Using Excel Data

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

Sorry about that.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

macropod wrote:
HansV wrote: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 wrote: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 ", "}
I'm again a bit confused. What are the space issues, is the "/b" slash also the wrong direction, and I just noticed the "[/fx]" after Zip Code, what is that and is that slash correct?

I had tried last night and didn't get the results I wanted so I quit and decided to try again today and that is why I am asking these questions. Thanks.

Edit: Not at all sure why the "[/fx]" after Zip Code is not showing up when I post this but I can see it as I write and edit this post. Here's what I see {·MERGEFIELD·"Zip·Code"·}[/fx] when editing this but not in its place when I post it. Seems strange doesn't it?
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

The [fx] and [/fx] do not belong to the field code. They are Lounge tags.

The switches in the field code should be \b and \f
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:The [fx] and [/fx] do not belong to the field code. They are Lounge tags.

The switches in the field code should be \b and \f
Thank you.
Regards,
hlewton

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

I am still having problems and would like to paste the code like Hans did but when I try copying it, it pastes the field names only even though I have the code showing when I do a copy from Word. How can I paste the code so maybe you can tell me what is wrong with it? Even if I try saving it to a plain text file it does not show the code.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

You cannot copy/paste field codes. The field braces { } are not ordinary text characters; you cannot type them yourself or copy/paste them from a post here. If you press Alt+F9, the merge fields should be displayed as

{ MERGEFIELD fieldname }

and if you press Alt+F9 again, the field codes should be hidden.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

I am so sorry to have to ask this. I have 2 separate merges below. The first one is the one we have been discussing. I think I have the code correct but it still does not work correctly. On the last page of labels it still is leaving a blank space in place of the AuxiliaryName field. However the comma problem with the City field is working fine. Can you see anywhere where I made a mistake in the coding? Also I know I have not eliminated the paragraph code at the end of the Last_Name field because without it there it does not print out correctly at all. When there is data in thAuxiliaryName field it prints it out without a space or line feed between it and the Last_Name field. Also I have found no way to post the actual code so I tried manually typing it so it can be viewed to see what I am doing wrong.


«Title» «First_Name» «Last_Name»
«AuxiliaryName»
«Street_Address»
«City», «State» «Zip_Code»

Manually typing code for above merge.

{ MERGEFIELD Title } { MERGEFIELD First_Name } { MERGEFIELD Last_Name }
{ MERGEFIELD AuxiliaryName \b }
{ MERGEFIELD Street_Address }
{ MERGEFIELD City \f “, ”}{ MERGEFIELD State } { MERGEFIELD Zip_Code }

In the below merge there are two fields that should not be printing out if they are blank. Again I believe I have the coding correct and the AuxiliaryName field is working correctly on all sheets of the labels but the Street_Address2 is not. It is the last of the 2 possibly blank fields in the merge where in the above the one that could be blank is the first and the last of a possible blank field if that matters. Again that field, Address2 is leaving blank spaces on the last page of the labels. Also again, I know I have not eliminated the paragraph code at the end of the II_Jr field, which is the last field in the first line of the label, because without it there it does not print out correctly at all. When there is data in the AuxiliaryName field it prints it out without a space or line feed between it and the II_Jr field. Also I have found no way to post the actual code so I tried manually typing it so it can be viewed to see what I am doing wrong.

An “H” shows up because the first data information has a middle initial in it but if other lines of data don’t have data in that field it appears as a blank space in the merge below.


«Title» «First_Name» H. «Last_Name» «Family»
«AuxiliaryName»
«Street_Address»
«Street_Address2»
«City», «State» «Zip_Code»

Manually typing code for above merge.

{ MERGEFIELD Title } { MERGEFIELD First_Name } { MERGEFIELD Middle_Initial } { MERGEFIELD Last_Name } { MERGEFIELD Family } { IF { MERGEFIELD II_Jr } = “” “” “ {MERGEFIELD II_Jr }”
{ MERGEFIELD AuxiliaryName \b }
{ MERGEFIELD Street_Address }
{ MERGEFIELD Street_Address2 \b }
{ MERGEFIELD City \f “, ”}{ MERGEFIELD State } { MERGEFIELD Zip_Code }

Is there anything obviously wrong with my code? It is so strange that these merges worked for years and now no matter what I do I cannot get them to merge correctly.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

The first should be

{ MERGEFIELD Title } { MERGEFIELD First_Name } { MERGEFIELD Last_Name }{ MERGEFIELD AuxiliaryName \b "
" }
{ MERGEFIELD Street_Address }
{ MERGEFIELD City \f “, ”}{ MERGEFIELD State } { MERGEFIELD Zip_Code }

and the second

{ MERGEFIELD Title } { MERGEFIELD First_Name } { MERGEFIELD Middle_Initial } { MERGEFIELD Last_Name } { MERGEFIELD Family } { IF { MERGEFIELD II_Jr } = “” “” “ {MERGEFIELD II_Jr }” }{ MERGEFIELD AuxiliaryName \b "
" }
{ MERGEFIELD Street_Address }{ MERGEFIELD Street_Address2 \b "
" }
{ MERGEFIELD City \f “, ”}{ MERGEFIELD State } { MERGEFIELD Zip_Code }

But once again, you should not copy/paste this. Instead, use Alt+F9 to display field codes. Then edit them, and finally press Alt+F9 again.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

Thank you. I will give this a try.
Regards,
hlewton

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

Hans I'm sorry but I cannot tell if there is a space between the quotes after the \b or not. Is there a space? Thanks
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

There is an Enter between the quotes, i.e. the second quote is in a new paragraph.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:There is an Enter between the quotes, i.e. the second quote is in a new paragraph.
OK thanks. BTW I just hung up from a remote session with Microsoft support and the first line of support had not heard of this but since she was remotely connected she saw it happen. She tried it again using Word in the safe mode but that too did the same. She is escalating the problem and MS is supposed to contact me about 40 hours from now. If I get an answer what is happening I will post it here. All I know for sure is all my merges worked perfectly up to and including Word 2013 but not in 2016's version. Hopefully I can get these switches to work for me.
Regards,
hlewton

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

OK I put {MERGEFIELD AuxiliaryName \b “
”} with an Enter between the quotes and it really messed up the merge like this I left the paragraph markers visible and I believe the second one down on the left hand side of the labels is the Enter between the quotes in the AuxiliaryName field. Am I still doing something wrong or not understanding what I am supposed to be doing?

I had to place "P" in place of the paragraph symbol since it would not copy but you can see the address is not complete although it is further on down in the label which would place it outside the label boundaries. All the labels looked like this one.

P
Mr. & Mrs. Gerald V. Jones P
P
3335 Stratford Green P
P
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

I don't think we are going to solve it this way. Could you attach the mail merge document, with sensitive information altered or removed?
For the moment, you don't need to attach the Excel data source, but I may ask for it later if we don't progress.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:I don't think we are going to solve it this way. Could you attach the mail merge document, with sensitive information altered or removed?
For the moment, you don't need to attach the Excel data source, but I may ask for it later if we don't progress.
Sure I will attach a merge document but the only ones I have are connected to an Access file. I did not save the one connected to the Excel spreadsheet because of the problem that cropped up but I can recreate one if necessary. I think the one I attach can be used and then I can see what changes were made to make it work. Please let me know if you want me to recreate a merge to use an Excel spreadsheet. I should mention that this file is an exact copy of a merge that has always worked for me but it has no edits we have talked about in this thread. Thanks
Test - 5160NoPostalCode.docx
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

Here is a merge I just created using an Excel spreadsheet and all the names are factious but it does show the problem on the last page of the labels.
MergeWithExcel.docx
TestMergeSpreadsheet.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

Here is the modified document.
I edited the fields in the first label, then clicked Update Labels.

You will probably have to reconnect to the Excel workbook.
MergeWithExcel.docx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:Here is the modified document.
I edited the fields in the first label, then clicked Update Labels.

You will probably have to reconnect to the Excel workbook.
MergeWithExcel.docx
Something is strange. When I download this it opens with the peoples' names and only 1 sheet of them. I had no option to re-associate it with the Excel spreadsheet. If I do an Alt F9 I can see the code but doing it again it goes back to publishing the actual addresses.
Regards,
hlewton

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Mail Merge Using Excel Data

Post by hlewton »

I very carefully copied and reentered the code as you wrote it in a new merge and attached it to the same Excel file and from what I can see it appears to be working as it should. Now if I can only copy it correctly to my other merges to make them work correctly again that would be great. Thanks for all your help.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mail Merge Using Excel Data

Post by HansV »

Glad it appears to be working.

Apparently the document that I attached somehow lost its mail merge settings, so you'd have to select Start Mail Merge > Labels again on the Mailings tab of the ribbon.
Cancel selecting a label type (that has already been done), then click Select Recipients, etc.
Best wishes,
Hans