Mail Merge Using Excel Data

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

Re: Mail Merge Using Excel Data

Post by HansV »

You should apply it to the first paragraph in the first cell only, not to the entire cell.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Thanks Hans. I created an new merge with the coding in them as you showed me before and everything is working well as far as the merge is concerned. I do have a bit of a problem with the the data source which the merge uses. I exported the data from an Access query to an Excel file, as I always do when making these merges for others who have Excel but don't use Access. Everything is there but I cannot get the grid lines to show in that Excel file. Everything I can find as far as checking Grid lines to show is checked and they do show in every cell except those that have data in them. I can make the cells show borders but in the merge they seem to be used as data and it tries to print out extra labels for those blank rows that have borders on them. How can I make the Grid Lines visible to make it easier to add more data later to this Excel spreadsheet?

In the attachments you can see that the Show Grid Lines is checked and I have no idea what else can be done to make them show.
Gridlines.jpg
Gridlines1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

What happens if you select the entire used range and set the fill color to No Fill?
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:What happens if you select the entire used range and set the fill color to No Fill?
That seems to have made it work. Thanks.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Have a question just to try to educate myself a little bit. I don’t make return labels using the template that is in the attachment but my wife does. It is an Avery 5167 template and you will see the “[Name]” field is bolded and the remainder of the label is not. I guess, the way we figured it out, you just click on the bolded field enter your return address hitting the enters within that “Name field.” Then we have been clicking out of that field on to another label and it automatically updates all the other labels. So far so good but I like to change the default font size from 9.5 to 9 before printing the actual labels. In doing so I noticed that the lines not in bold in the label are still there but only partially visible. Street address and City, State and Zip Code are what I am talking about. But only a small portion at the top of Street Address is visible after I change the font. Shouldn’t these fields or whatever they are have been erased when the labels updated or are we using this template wrong? By the way, I tried typing withing those areas that are not bold and the labels did not update so I guess that is not the solution.
5167.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 78236
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 understand. Are you overwriting the merge fields?
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:I don't understand. Are you overwriting the merge fields?
Hans I am not quite sure what I am doing. Below is a screenshot of what I see when opening this Avery 5167 template by creating a New Word file, searching for and choosing that template then creating the new document. All the instructions I have found is what you see circled in red on the attachment. I had to experiment to get all the labels to update and the only way I found was to type all the information in the bold "[NAME]" field you see in the attachment above. Once this template is opened all 80 labels appear as seen, again, in the above attachment and I have tried typing to replace what is not bold like the [Street Address] and the [City, State Zip Code] lines but that does not seem to update the labels when finished typing. So for an example I click so I have [NAME} chosen, then within that [NAME] field I type John Brown <Enter> Street address <Enter> City, State ZIP Code. Then I click on any other label of the 79 remaining and the entire document updates to what I have typed. But, again, whatever those lines in the original document are called that contain [Street Address] and [City, State Zip Code] do not disappear. They, however are out of sight until I change the font size. To better understand I created a new sheet of labels, filled all the labels by updating them as I mentioned above, then stretched open the first row of those labels, and circled what I am talking about. As a reminder you can see that this template chooses 9.5 as its default text size but I change that to 9 and that is when a small portion of the top of the row circled in red on the second attachment which reads Street Address becomes visible on all the labels. It would appear below "Anywhere, US 55312" in the attached example.
5167Instructions.jpg
5167Results.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

The instructions say that the labels are 1/2" tall.
Apparently you have increased the height of the top row by adding extra paragraph marks (returns), but that will mess with the label layout of course. You should delete

[Street Address]
[City, ST ZIP Code]

and restore the height of the top row to 1/2".
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:The instructions say that the labels are 1/2" tall.
Apparently you have increased the height of the top row by adding extra paragraph marks (returns), but that will mess with the label layout of course. You should delete

[Street Address]
[City, ST ZIP Code]

and restore the height of the top row to 1/2".
I did as you suggest even before posting here except I deleted those two lines before typing the information to begin with so I didn't have to stretch the label higher. Then I tried it again exactly as you suggest by stretching the label, deleting those two lines after typing the information and the results were the same. The only label without those lines is the first one where I type the information. All the remaining labels retain those two lines.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

Try starting over.
Select [Name] and type the name only.
Select [Street Address] and type the street address only.
Select [City, ST ZIP Code] and type the city, state and zip code only.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

That worked but it puzzles me because I tried that before too. I must have been doing something wrong. Thanks.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:Try starting over.
Select [Name] and type the name only.
Select [Street Address] and type the street address only.
Select [City, ST ZIP Code] and type the city, state and zip code only.
As I mentioned above I was sure I had tried it this way. However, I think I figured out what, between my wife and I, we were doing wrong. When we would finish typing in any field we would hit <Enter> instead of clicking (or Selecting) on the next field. As you probably already know, that messes things all up. All is well now though.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote: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.
I thought all was well but I had to run a merge today and it uses what is described above as the "second." I had to manually place a return or paragraph, whatever it is called between { MERGEFIELD Street_Address } and { MERGEFIELD Street_Address2 \b " " } to get them to print out on separate lines. Is there some code missing in the { MERGEFIELD Street_Address } field that would make it and { MERGEFIELD Street_Address2 \b " " } print out on separate lines without the paragraph being inserted after { MERGEFIELD Street_Address } ?
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

The way I posted it, there is already a paragraph break in

{ MERGEFIELD Street_Address2 \b "
" }

Note that I did NOT post it as { MERGEFIELD Street_Address2 \b " " }
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:1. and 2. You can use either \b or \f

The \b switch inserts something (for example a paragraph break) BEFORE the field result.
The \f switch inserts something (for example a paragraph break) FOLLOWING (after) the field result.

If you use \b "¶", there should not be a paragraph break ¶ before the field, since the switch will insert it there if necessary.
If you use \f "¶", there should not be a paragraph break ¶ after the field, since the switch will insert it there if necessary.

3. The space after \f " " is not relevant, it is just there for legibility.

4. and 5. In the merge document (the one with the merge fields), it should be possible to edit the first label, and then propagate the change to the rest by clicking 'Update Labels'.
In the result of the mail merge (what you get after clicking 'Edit Individual Documents...'), each label is independent of all others; changes to one label cannot be propagated to the rest.
HansV wrote:The way I posted it, there is already a paragraph break in

{ MERGEFIELD Street_Address2 \b "
" }

Note that I did NOT post it as { MERGEFIELD Street_Address2 \b " " }
Not sure I am understanding this again. You are right about the paragraph break. I copied the an incorrect example. However, it is not after Street_Address2 that I am having the problem. It is after Street_Address that it combines that with Street_Address2 without starting a new line. I have tried using both the “\b” and the “\f” switches and I cannot get Street_Address2 to start on a new line. That is why I put a paragraph break at the end of Street_Address field. That does make Street_Address2 print on another line and it does not create a blank line if Street_Address2 field is empty. So it solves the immediate problem but not sure it will always work on the 32 Bit versions of Word and that is why I think it needs a code to make it work but nothing I have tried so far is successful. I'm sure I am again typing something wrong but not sure what the correct code should be to force Street_Address2 to print on a separate line from Street_Address and not create a blank line if Street_Address2 field is empty in the database. I have tried both the \b and\f switches both with and without the paragraph break and have not been successful.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

Well, if it works I'd leave it that way.
Best wishes,
Hans

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Mail Merge Using Excel Data

Post by Charles Kenyon »

When dealing with merge codes in labels there often is not enough space to easily read and edit the codes.
I will generally copy the codes from one label into a blank document where they are not in a table cell.
I can edit them there, then paste back into the label.

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:Well, if it works I'd leave it that way.
I believe I have it fixed now and it is like you told me when I first posted this thread. My mistake was, because I have so many merges to deal with, I choose the wrong one which didn't have the correct code in it. I made the change by editing the Street_Address2 field instead of the Street_Address field. Then after finally hitting on the correct code I opened another merge with a name too similar to the one I just questioned and it did have the correct code. I am going to change the names so hopefully I never choose the incorrect one again. Sorry for the confusion.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Charles Kenyon wrote:When dealing with merge codes in labels there often is not enough space to easily read and edit the codes.
I will generally copy the codes from one label into a blank document where they are not in a table cell.
I can edit them there, then paste back into the label.
Thank you for the suggestion.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Rather than starting a new thread I hope it's fine to ask this question here. In the attachment you can see the merge fields I am using. The labels are 5160 so there are 3 rows across and 10 rows down for a total of 30 labels on a page. I don't think that is pertinent but just in case it is I thought I'd state it.

I messed up when I created this merge (not the one in the attachment, it has been recreated from scratch and is fine) and instead placing the Last_Name merge field where it is shown now, I placed the First_Name merge field there again. So I had 2 First_Name merge fields and no Last_Name merge field. Instead of starting over, I thought I could edit the document and correct it. I highlighted the second occurrence of the First_Name merge field and deleted it in every one of the 30 labels on the page, at least I thought I had deleted it, and inserted the Last_Name merge field. When I did the merge to the final document it merged it as Title, First Name, Last Name, First Name. For instance Mr. John Login John. I tried once more by highlighting the second First_Name merge field and deleting it again. It merged to the final document exactly as it did before. Any idea why this would be the case?

What I did notice when trying to delete that field no matter if I doubled clicked it or just dragged the cursor over it to highlight it, it took almost as many times of hitting the "delete" key as there are letters in the field to see it deleted, but obviously it really did not delete it.
Merge.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton