Mail Merge Using Excel Data

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Still playing around with trying to learn to use the switches in my mail merges that have been used in this thread. I am experimenting with both my Access databases and Excel spreadsheets with data exported from Access tables. I have noticed this before when trying to direct the merge to an Excel spreadsheet it shows 2 choices even though there is only 1 tab in the Excel spreadsheet. Hopefully you can see the attachment well enough to see what I am referring to. The choices are the same name with the exception that the second choice has a “$” after it. From what I see it doesn’t seem to matter which one I choose the results appear to be the same. What is the one with the “$” sign and why is it displayed as a choice?

Thanks.
Address.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

Your image consisted mostly of white space. I have cropped it for you; please do so yourself in the future. Thanks for your cooperation!

The one with the $ refers to the worksheet named address1.
The one without the $ refers a named range or table.
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Mail Merge Using Excel Data

Post by StuartR »

I have moved the subsequent discussion about how to crop images to the Lounge Matters forum
StuartR


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

Re: Mail Merge Using Excel Data

Post by hlewton »

I think I have gotten a shell document I can use for my merges, as long as I make sure the all fields are in the data source. Though I am not certain exactly which switches to use and when to use them I have taken Han’s example and changed a couple switches and got the end result I need for both Word 2016 64 Bit and Word 2016 32 Bit versions. Here is what confuses me from my searches and this is direct from online even though the some of the fields are actually the same as mine,
“Use \b and \f switches to insert line breaks when needed.
Example
{MERGEFIELD street_address1}
{MERGEFIELD street_address2 \f "↲"}
{MERGEFIELD city}, {MERGEFIELD state} {MERGEFIELD zip}
Replace ↲ with an actual line break (Shift-Enter)
If all fields are not blank, the result will be
123 Main
MS 123
Any Town, CA 00000
If street_address2 is blank, the result will be
123 Main
Any Town, CA 00000
Without the \f switch, there would be an empty line between street_address1 and the city/state/zip line.”


I have not tried the "↲" option because I like the way Han’s code works plus I am not at all certain what it would do.
I have some questions I haven’t been able to find answers to online.

1. Form the above online information I still do not know when it is better to use the \b or the \f switch. The only thing I can come up with is to use the \b when it needs an Enter inside the MERGEFIELD brackets to possibly create a blank and empty line in the merge document. Is it fair to assume that?
2. I think I have figured out when to use and Enter after a MERGEFIELD but not certain. Do I NOT use an Enter between MERGEFIELDS when it is possible that the next MERGEFIELD will not contain data for some records?
3. I believe the codes and switches take the place of spaces between MERGEFIELDS especially on the same line when it has completed the merge. So in the code you see in the attachment below, for example, is the space in the MERGEFIELD Title after the \f “ ” } and before the bracket important or is only the space between the quotes necessary?
4. Sometimes I want a line at the top of my labels so when I create the merges I move the cursor down a space before inserting fields. If, however, I create a merge starting on the very top line of the label and later want to move the entire sheet of labels down 1 space, or visa versa by moving them up 1 space, I haven’t been able to do that. Is this possible? I thought I did this but it would not allow me to “Update” the entire sheet so I edited every cell and that still didn't work.
5. Finally, I have tried to edit a saved merge. For example, if I wanted to insert a space or remove a space in one of the fields, I see it taking place within the cell I am editing in but it will not allow me to “Update” the document. Can editing of this type be done?
Thanks
Merge Codes.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

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.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Thanks you Hans. I thought I should be able to edit the merges but for some reason that isn't working for me but I will keep trying.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Hans in the below attachment I am attempting to show that all I did was open my merge and delete the blank line above the label in the first cell. Hopefully in the first attachment you can see that the “Update Labels” or Propagate is not active so the other cells are not updated. In the second attachment, after running the merge, you can see the other cells were not updated. It happens this way for me whether I am adding a line above the top of the label, eliminating a line a line above the top label, deleting a space between MERGEFIELDS or anything else I have tried so far. I know way back when I first created most of my merges I was able to edit them in the manner I have mentioned here but it seems, at least to me, that is no longer possible. Is there something I am missing or doing wrong when trying to edit these merges?
Update.jpg
Not Updated.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

When you clicked Start Mail Merge, are you sure that you selected Labels from the dropdown? If you selected another option such as Letters, the Update Labels button will be greyed out.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:When you clicked Start Mail Merge, are you sure that you selected Labels from the dropdown? If you selected another option such as Letters, the Update Labels button will be greyed out.
No I hadn't done that. Actually I just started editing it because it appeared to be labels already but apparently it wasn't. I just tried it and I believe it is working. I am going to try that on editing spaces. Thanks again.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Hans years ago, I believe it was at the other site and I can’t find it, you helped me create a merge that forms a single line of information for as many different people that are involved in the query to which the merge is attached. I have tried to recreate it and can’t. I’m not sure what the arrows are between fields and that is one thing I can’t replicate. Also I noticed there are 2 paragraph marks after the last MERGEFIELD. I put them in and still only get one line of data when there should be 20 when I run the merge. The first screen shot shows what I see on the screen when opening this merge that you made work for me and the second screenshot shows the code. I am starting the mail merge using the Letter option, I believe that is correct. How do I make another similar one with the same code so it works as this one does?
Single.jpg
SingleLine.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

If you want one line per record instead of one page, use Directory instead of Letters when you start the mail merge.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:If you want one line per record instead of one page, use Directory instead of Letters when you start the mail merge.
How do I get the arrows between the fields ? I just tried inserting the MERGEFIELDS in the Directory with the 2 paragraph entries after the last MERGEFIELD and got them to print on separate lines but the arrow is missing in the code. I think those arrows between the MERGEFIELDS must have made the spacing work good in the attached example above.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

The arrow is a tab - press the Tab key to insert it.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:The arrow is a tab - press the Tab key to insert it.
Hans in the first attachment below it shows my attempt at recreating the same merge we have been talking about. As you can see, I put the TABs in it but it doesn’t look like the one further up that you created years ago, even the first TAB spacing is completely different but I only hit the TAB key 1 time. Using the same merge, after hitting Alt F9 to show codes the Tab between the last two fields isn’t showing even though I entered it, as you can see in the first attachment.

In the merge you created the 3 entries of MERFEFIELDS after the merge has been run line up perfectly in columns. Nothing I have done will make that happen again. I am using the exact same query as the input for the old merge and the one I am trying to create again but the results are very different. Is there a step I am missing to make the data line up in columns after running the merge? Also as is apparent, my TAb entries are so different and I don't know what is causing that either.

BTW I am so sorry to continually have to ask you for help but this is the last merge I will be working on because I managed to get all the other 20 merges I have used over the years to work for me with all the help from you. Again, this is the last merge I am going to try to get working. Thanks.
NewMerge without Alt F9.jpg
With Alt F9.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

If you currently don't see the ruler at the top of the document window, tick the check box for Ruler on the View tab of the Ribbon.
Select the entire document (Ctrl+A)
Click on the ruler at the positions where you want the text to be aligned, for example at 2" and 4".
S2661.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:If you currently don't see the ruler at the top of the document window, tick the check box for Ruler on the View tab of the Ribbon.
Select the entire document (Ctrl+A)
Click on the ruler at the positions where you want the text to be aligned, for example at 2" and 4".
S2661.png
Thank you. I will give that a try.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Whew, that worked and, as I mentioned, I am done once again with merges. Until the next time.

Thanks so much.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by hlewton »

Well I ran into something else I need help with. I will attach 2 screen shots so you can see what I am talking about. In both of them you can see the "Update labels" feature is active. I want to put a 4 point space before the top of the labels. In the first attachment you can see that the label starts printing right at the top of the labels. In the second attachment you can see I have the space I want but when I press the "Update Labels" button it reverts to the same as you see in the first attachment and I do not get that space I need. What am I doing wrong this time and how do I update all the labels?
Merge.jpg
Merge1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Mail Merge Using Excel Data

Post by HansV »

That is strange. When I try this, the empty paragraph at the top of the first cell is propagated to the others.
But instead of using an empty paragraph, you could set the Spacing Before of the paragraph with the Title merge field in the first cell to for example 12 points:
S2690.png
Then click 'Update Labels'.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

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

Re: Mail Merge Using Excel Data

Post by hlewton »

HansV wrote:That is strange. When I try this, the empty paragraph at the top of the first cell is propagated to the others.
But instead of using an empty paragraph, you could set the Spacing Before of the paragraph with the Title merge field in the first cell to for example 12 points:
S2690.png
Then click 'Update Labels'.
I know it is strange because with so many of my other merges I have done exactly the same thing and somehow managed to propagate all the labels but this one just would not cooperate for some reason.

What I did was create a whole new merge and got the finished product I was looking for. But let me take your example and ask wouldn't setting a spacing before a paragraph create that same spacing before each line of the label since this merge is a simple one, meaning after each line I want on the label I hit "Enter."
Regards,
hlewton