• How to mail merge to labels

    Author
    Topic
    #480494

    I have a list of names and addresses. The person I’m doing this for wants to be able to print labels from this list. At the top of the screen (Word Starter 2010) is a choice of Mailings and under that is a choice for labels, a section called Start Mail Merge and in the Start Mail Merge tab is a choice of Step by Step Mail Merge Wizard, but I couldn’t figure out how to get it to work.

    1. How to I turn this list into a form that can print labels?

    2. Do I need to know exactly what label it will be to set it up? Or, is there a way that I can just set up the merge list and send it to her in a format that she could use for whatever labels she buys?

    Viewing 15 reply threads
    Author
    Replies
    • #1310509

      Hi Sherry,

      Welcome to the Lounge.

      With Word Starter, do you have the ‘labels’ button near the left end of the mailings tab? If so, you click on that, then select the appropriate label type (via Options) then ‘New Document’ before starting the mailmerge.

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

      • #1310609

        Thank you for the prompt reply, Paul. I do have the labels button. At the moment I don’t know what labels it would be, but I could try it with something and once I know how to do it, I could change to whatever she needs.

        I selected a label and got the “form” of the labels showing on the screen. I tried some of the options to set up the merge, but nothing worked. I had hoped that the space between each name and address would serve as the record delimiter. I decided to go back and use $ to end each record and adjusted the list to have the $ after each one.

        I didn’t want to choose fields. I was using the full name and address as the one field. I tried some of the options for creating both the label form and using the list, but again, nothing seems to work. I think I need step-by-step instructions.

        Sometimes it has a message that in order to use it, it will have to delete the contents of the file I’m using for the list. I went ahead and said yes, since I could always retrieve the original document later. Then, if I tried to link that document, it said it was in use as the current file. One time I got a preview which included the name and beginning of the address all on one line and not the remainder of the address. Do I have to add separate field for each line? It’s a long list, so that would take quite a while!

    • #1310682

      Hi Sherry,

      Using spaces as field delimiters isn’t a good idea, since multi-word fields are quite common (including in surnames).

      Perhaps you could post a copy of your date file, with just a small extract of the data you’re using?

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

      • #1310684

        Hi Paul,

        I didn’t know if my customer would want the actual names and addresses posted, so I used symbols or letters to type over the specific info and used a generic City, State. These are the first few entries. There are approximately 12 to a page and about 28-29 pages.

        Once I’ve chosen the labels, I don’t know how to set up the form for the merge and how to tell it to merge with this particular list. I’ve added the $ as the record delimiter.

        Mr. And Mrs. William ////////
        929 ZZZZZZZZZ Street
        City, State 11111
        $
        Mr. And Mrs. Mark \\\
        7732 16th Avenue
        City, State 111111
        $
        Mr. and Mrs. Philip ~~~~~
        186 AAAAA Road
        City, State 11111
        $
        Pamela and Alan ######
        $
        Mr. and Mrs. Tim %%%%%%%%%
        BBBBBB Road
        City, State 11111

        Thanks again,
        Sherry

        • #1310687

          Hi Sherry,

          Your data are not laid out for a mailmerge! They should be laid out like:
          Mr. And Mrs. William ////////,929 ZZZZZZZZZ Street,City,State,11111
          Mr. And Mrs. Mark \\\,7732 16th Avenue,City, State,111111
          Mr. and Mrs. Philip ~~~~~,186 AAAAA Road,City,State,11111
          Pamela and Alan ######,,,,
          Mr. and Mrs. Tim %%%%%%%%%,BBBBBB Road,City,State,11111
          etc. with empty fields marked by consecutive commas as shown. If there are already commas in your data, you can use tabs as the field delimiters instead.

          The alternative is to skip the whole mailmerge process and simply convert the exiting data to a Word table corresponding to your client’s label format (in Word, labels are nothing more than tables with particular layouts)

          Cheers,
          Paul Edstein
          [Fmr MS MVP - Word]

          • #1310695

            Hi Paul,

            Number one, with the commas (or tabs) separating the fields, would they end up being printed one under the other? I imagine it would be quite time consuming to convert it to that form. I primarily use WordPerfect, and when doing a simple merge like this, where I don’t need the name or parts of the address as separate fields, I just set it up as the full name and address as I showed before. I put an [endfield] code at the end of the full item and then [endrecord] and go on to the next. When I set up the labels, I just put a Field 1 code on the label and merge.

            Your suggestion of the table sounds like the best solution. I haven’t done much on tables in Word, either. At this point I don’t know what kind of labels she will get, but if you could give me an example of how to do it. If the document with the current names and addresses is set up in the appropriate table format, or if I copy that file into a new document set up the appropriate way, will all of the names and addresses automatically fit into the various cells of the table?

            Thank you,
            Sherry

    • #1310697

      Hi Sherry,

      The mailmerge output format can be whatever you want it to be. We’ve been discussing labels, and that’s the format I was aniticipating. The mailmerge data source layout has little to do with the mailmerge output format.

      To convert your existing data to a table format, you’ll need to use a couple of Find/Replace operations beforehand:
      • Find = ^p Replace = ^l
      • Find = ^l$^l Replace = ^p
      Your data are now ready for the conversion. Once you know the required label format:
      • use the Labels button & dialogue box to create a new document based on the required label format.
      • using the Properties option on the ‘Table Tools’, Layout tab, make a careful note of the number of columns, their widths and the cell heights (which will be set to ‘exactly’ some measurement).
      • you can now delete the table.
      • copy the data and paste your data into the label document.
      • select your pasted data and choose Insert > Table > Convert text to table. Click on ‘Separate text at:’ Paragraphs, then change the ‘number of columns’ to match the number of columns in the label table. Click ‘auto-fit to window’, then OK. Your data will now have been converted to a table, all of which will be selected. Do not change the selection!
      • on the Table Tools, Design tab, use the borders option button to clear your table’s borders.
      • using the Properties option on the ‘Table Tools’, Layout tab, make the column widths the same as for the original table (you’ll probably have to change the units from Percent to Inches or Centimetres). Do likewise for the row heights, setting their height to ‘Exactly’ and unchecking the option that allows them to break across pages.
      Your labels should now be ready to print.

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

    • #1310707

      Hi Paul,

      I was completely lost with the ^p and ^1 etc. I have no idea what they mean, but I tried it. Very interesting! The first put each record on one line and the second put them back in the original spacing without the $.

      However, I didn’t get much further than that. I wasn’t sure if I had to define the labels, the table or both. I selected a label format of 3 across and 10 down on a page and brought it up so that I had a document with the outline of those 30 labels showing. I then tried defining the table, but nothing seemed to happen. And, when you said to delete the table, that was even more confusing and I couldn’t figure out how to do it anyway. I didn’t read too carefully the steps after that, as I figured I would have to get that first part set up before the other parts would matter.

      Also, in defining the table, would I use the dimensions of the label? I would assume that a bit of space would have to be incorporated for the space between the labels. The form I was using had labels of 1 inch height, 2.63 inch width. It also mentioned .5 top and .19 side. I didn’t know if that referred to each page, if that was the space between them, or exactly what it was. It also mentioned vertical pitch 1 and horizontal pitch 2.75.

      I hope that’s sufficient for you to figure out my current situation and what I need to do to follow your previous directions.

      Sherry

    • #1310929

      Hi Sherry,

      Before defining the lables, you need to know which ones your client will be using. It’s no good setting-up the document for something else!

      After copying the parsed text and pasting into the end of the labels document, the next step is:
      • select your pasted data and choose Insert > Table > Convert text to table. Click on ‘Separate text at:’ Paragraphs, then change the ‘number of columns’ to match the number of columns in the label table. Click ‘auto-fit to window’, then OK. Your data will now have been converted to a table, all of which will be selected.
      I really don’t know how to make that part any clearer/simpler.

      And yes, you must use the cell dimensions of the (now deleted) label table. The .5 top and .19 side are most likely the internal cell clearances. IIRC, Word’s defaults are 0 top and .19 side. These are just to ensure the text doesn’t get too close to the label edges. You can achieve the same result via paragraph formatting. The pitch dimensions can probably be ignored.

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

    • #1310957

      Hi, Sherry,

      If I may jump in… I think some of the confusion here has to do with the two different options Paul has presented. In the first option, you would convert your mailing list into a table and then perform a mail merge whereby the data in the table is pulled into a separate document formatted as labels. (As in WordPerfect, merges in Word require two files: (1) a data file — in this case, the mailing list, which in Word must be in the form of a table, and (2) a form file such as labels.) In the second option, you would convert your mailing list into a table and format the table to match the dimensions of the type of labels your client wishes to use. No merge involved.

      Is that a little clearer?

      I think Paul is focusing on the first option for now. (Paul, please let us know if that’s not the case.) So his instructions are intended to help you reformat your mailing list as a table that uses the same margins as your client’s labels. Essentially, you’ll be putting the data into a label form without using an official label form.

      If you haven’t done so already, I would suggest making a copy of the mailing list before you start playing with it.

      Assuming I’m understanding Paul’s instructions correctly, the find and replace operations are designed to turn each record into a separate paragraph — sort of equivalent to a single “Address field,” if you will. Each label will contain one such record / “Address field.”

      After you do the find and replace, you’ll convert the text to a table (with paragraphs as the field separator and with the number of columns matching the label type your client uses). I’m pretty sure you will be able to tweak the table margins after the conversion. Just be careful; when you change the table margins, you want to make sure that all cells are formatted identically. For people who are accustomed to working in WordPerfect, that can be a bit of a challenge. (Using the Table Properties dialog to accomplish this task works better, IMHO, than trying to adjust the margins by dragging the margin markers on the Ruler.)

      I hope that helps.

      Good luck!

      Jan

      • #1310965

        Hi Paul and Jan,

        First, Paul:

        I chose a label format to try it out. If that worked, then presumably I could easily do it again with whatever labels she gets.

        It was the in-between part that I had a problem with. I did the search and replace that you provided. But, before the part that you repeated today, you had said:

        • use the Labels button & dialogue box to create a new document based on the required label format.
        • using the Properties option on the ‘Table Tools’, Layout tab, make a careful note of the number of columns, their widths and the cell heights (which will be set to ‘exactly’ some measurement).
        • you can now delete the table.

        I’m assuming that means to define the labels and bring up that form first. Then define the table. Nothing seemed to happen when I did that, and deleting the table confused me. Do I get the label document first, then create a table and set up the dimensions on top of the labels. And, if that takes, then delete the table? Also, I couldn’t find a means of deleting the table. There were options to delete a row or a column, but I didn’t see any option for the entire table.

        The rest of your instructions should be simple; I just hadn’t paid too much attention to that part since I didn’t have the previous step completed.

        Jan,

        Your explanation of creating the table seemed clearer. I tried it – first it took a little while to figure out how to get it to convert text to table, but once I chose that, I decided to do 3 labels across and it automatically put 433 (or something like that) rows. It already had paragraph selected for separating the text, so I thought it should work. But, when I did that, it put the first name in the first cell, the street address in the 2nd and the city, etc. in the 3rd. The first cell of the 2nd row was blank and then the next name in the 2nd cell of that row, etc.

        Is there something else I need to know to set that up?

        Thank you both,
        Sherry

    • #1310968

      I’m not sure about the portion of Paul’s instructions where he has you open a document based on a label form and then delete the resulting table — I’m wondering if perhaps, once you know the dimensions of the label, you could begin by having the records in any document — then convert the text to a table.

      When Paul said to run a find and replace as follows — Find = ^p Replace = ^l — he was having you replace paragraphs (hard returns, inserted with the Enter key) with line breaks, also sometimes referred to as soft returns. Then when he had you do the second find and replace operation — Find = ^l$^l Replace = ^p — he was having you replace the character you were using to separate records (a dollar sign) with a paragraph mark (a hard return). In other words, the only hard returns remaining after the two find-and-replace operations should have been between records. Then, when you converted the text to a table, using paragraphs as separators, Word should have taken each entire record and plunked it into a cell in the table, rather than dividing up the records based on the line breaks. I’m not sure why that didn’t happen; I assumed that Word would differentiate between a true hard return and a line break.


      Maybe Paul can clarify.

      Incidentally, there are a couple of different ways you can select a table in Word. From the Table Layout tab, you can click Select (at the top left side of the Ribbon), or you can move the mouse pointer close to the upper left-hand side of the table and, when you see a four-headed arrow, click it.

      Jan


      [/FONT]

      • #1310970

        Hi Jan,

        I found my mistake. Your explanation of Paul’s search terms helped. I thought it was the number 1 he was using, but when you explained that it stood for “paragraph” and “line break,” I decided it must be the letter L. I pasted it from your reply and typed a 1 and a small L next to it, and it was clearly the L.

        So, I tried the whole thing again and this time the full name and address appears in each cell. I’ll try setting up the size of the cells and then see what she gets. If her labels are totally different, presumably that will be easy enough to adjust now that I know what I’m doing.

        If any problems arise with that or if things work, I’ll let you both know!

        Thanks again,
        Sherry

    • #1310973

      That’s good news indeed! Hooray!

      Keep us posted, and have a good weekend. (You too, Paul!)

      Jan

      • #1311452

        Hi Sherry – attached (I hope) is a tutorial I have written for my students.
        Mike

      • #1311475

        Hi Sherry again – have attached the companion tutorial “Creating a Data Source”
        Mike

        • #1311527

          Thank you, Mike. I haven’t had a chance to look at them yet. Hopefully what I’ve just learned will be sufficient, but it will be nice to have for reference in case something comes up again.

    • #1310977

      I thought I was progressing, but still lots of problems. Once I had a table set up with all of the names and address in the appropriate places, I tried printing on blank paper and holding it up to labels I have of the size I’m using. At first it looked good, but as it went down, they were off, and sometimes the next column started a bit too far over.

      I decided that perhaps the instructions meant to copy the table into a separate document with the labels definition. But that didn’t work.

      I finally decided that the merge might be the best way after all.
      1) How do you set up the form document? I assume one has to have some sort of code to indicate to insert the field info, but I couldn’t find any way of doing that.
      2) Is there any easy way, like the find and replace above, to format my existing file into the proper data file? If that has to be done line by line or even record by record, it would be too much to bother with.

    • #1310978

      Sherry,

      Before you decide to do a mailmerge, let’s check a few things. When you set up the table, did you do all of the following:

      (1) Make sure the top of the table started at the exact same point on the page as the labels? (You might have to tweak your page margins and/or header margins a bit.)

      (2) Make sure the columns were the same width and the rows the same height? (Take a look at the Row tab in the Properties dialog and make sure the row height is set to “Exactly,” not “At least.”)

      (3) Center the table on the page? (You can do that from the Table tab in the Properties dialog.)

      If so, the table dimensions should approximate that of the labels.

      One more thing: Before you print a test page, try selecting the entire page, then going into the Paragraph dialog and setting the left indent for .25 or .3 (so that the text isn’t right at the margin), and also add some “Before” spacing — maybe 6 points or 12 points.

      Let us know.

      Jan

      • #1310982

        Jan, I think some of the points you bring up are issues I was working on.

        When I held it up to the labels the first time, I discovered I needed a larger top margin and changed that. I had a hard time getting all the columns the same width. I kept setting it, but only the first looked right. I apparently got it to work, but don’t know how. I tried holding a ruler up to the screen. Then, to make sure, I typed the alphabet in each cell and they all went to the same spot, so I knew they were equal.

        I didn’t center it. I chose small side margins and the table size of 3 x the cell width, and it looks close to equal, but a bit larger on the right. I can change that, obviously.

        I did adjust the top margin of each cell so that they didn’t print right at the top. I forget if I did that for the left margin or not. However, as I first asked Paul, with each cell the size of the labels, that doesn’t leave any space between the labels and so the other columns aren’t quite right. And, as it goes down the page, they no longer fit in the label form that way, either.

        Also, is this printer dependent? If I get it fixed right, will it necessarily work with her printer?

    • #1310986

      Hi Sherry,

      The internal cell margins (eg the 0.19 side margin you mentioned earlier), ensure the cell contents don’t get too close to the label edges – provided the labels are the right size. If the top margins on the printout change as you go down the page, that suggests the cell heights aren’t right. An error in the margins wouldn’t cause that unless different cells had different margins. The same principles apply to movements across the page.

      None of this should be printer dependent.

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

      • #1310988

        Hi Paul,

        I can’t check anything now, but I quickly looked at the labels I was using. There isn’t any space between them going down, so I’ll check the cell heights the way I did with the widths. But going across there is a slight space between each “column,” and I’m not sure how to set up the table to allow for that. Maybe I could add 2 columns of about 1/8″ to allow for the space in between.

    • #1310993

      Hi Sherry,

      The ‘space’ setting can be controlled by adjusting the left/ride-side internal margins, or by indenting the paragraphs in the cells. There’s really no need to insert spacer columns.

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

    • #1311017

      Sherry,

      As Paul suggests, the cell height (as well as the width) needs to be identical in each cell. I’m not sure if you answered my earlier question: Did you check the cell height and also check the Properties dialog, Row tab, to make sure that the row height is set for “Exactly,” rather than “At least”? If it’s set to “Exactly,” it can’t change spontaneously, but if it’s set to “At least,” it can.

      Jan

      • #1311034

        This thing keeps getting “weirder.” The row height HAD been “at least,” so I changed to “exactly,” but I don’t notice any difference. They all look the same, but I added an extra letter in the spaces below the text to see just how many lines fit in a label, and all of them were 5 except the 8th row which allowed 6. Meanwhile, I just noticed that I only had 9 rows and the label sheet has 10. Given my 1″ dimensions, surely 10 rows should fit on the page.

        As to the sideways spacing, adjusting the internal cell margin would push the text to the right, but since the cell would remain the same size, wouldn’t that leave less room for text? I found a setting which said to allow space between cells and I set that for .12 inch and going across that looks perfect. BUT, it also put the space going down, so now there are 8 rows instead of 9!!

        • #1311038

          I think I’m better off using the merge. I got instructions for doing find and replace in WordPerfect to get a hard left tab between fields and a hard return between records. So, I presumably have my data file in appropriate shape. I tried it in Word and it shows a little preview screen of what I have and I’m to select my field and record delimiters. In the preview it shows an arrow for the tabs and a paragraph symbol for the hard returns. So, in the field delimiter I chose “tab.” But, for record delimiter, they have no option for hard return and no paragraph symbol. I’m thinking of doing another find and replace and using one of the symbols they suggest instead of the return.

          If I get that part done, I still don’t know how to insert the merge codes in the label document. There is an option at the top saying insert merge field, but it’s grayed out.


          I’ve since tried some more. When I exited the “delimiter” box, it showed the label form with a blank first label and Next Record in each of the others. At that point the various merge options are now available. When I tried Insert Field it puts all 3 fields one after another on one line. I tried putting returns between them to get them one under the next, but it still doesn’t work. I then added the little double bracket symbols before and after the way the original was, but it still didn’t help.

    • #1311079

      Getting closer! I discovered that the word “enter” works for the hard return in the “delimiter” box. Once I chose that, when I try “insert field,” it provides a list of the 3 fields and I was able to insert them one above the other as they should be.

      I had some problems doing the merge. The first time there were a lot of records without enough fields. Some had a name only and I hadn’t added the extra tabs. Then there were a bunch with too many fields! It turned out that on some with 2 names or 2 parts to an address, it had added a tab between those parts. A couple had an extra tab at the end.

      Once I had all of that fixed, I thought for sure it would work. But, it only puts text into the first label. It says it’s on page 1 of 12, but only that first label shows the text. So, there must be some other step I need to do to set up the merge.

    • #1311088

      Success! Someone indicated the need to “update labels” to get the merge codes into the remaining labels. I hadn’t known about that, but that missing step was the last piece of the puzzle.

      Thanks again, Paul and Jan, for all of your help.

    • #1311216

      Hi, Sherry,

      Sorry to have dropped the ball — I had to prep for a trip to Sacramento (doing a Word 2010 training tomorrow at a small law firm that’s moving from WP to Word). In any case, I’m glad you got the merge working. (“Update labels” isn’t exactly intuitive, is it? But, once you know about it, it usually works quite well…).

      Jan

    Viewing 15 reply threads
    Reply To: How to mail merge to labels

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: