Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge letters to landlords with multiple prop (2003)

    I've been asked to create a mail merge from an Excel data source. It's a tricky one, for me. So I am begging for your help.

    We need to mail letters to people who own multiple properties (landlords) about their properties. What we want is one letter for each landlord and then a list of their properties either on the letter (preferred) or as a table attachment.

    The data source is an Excel spreadsheet that has about 2000 rows. Each row represents a property address. So, there may be from 1 to 15 lines per landlord and the landlord's name and address are repeated on each row for his/her property.

    How can I tell set up the letter to show one letter for each landlord and a list of the landlord's properties?

    Here are my fields in the Excel spreadsheet:

    LLID (landlord ID)
    ACCT ID (property address ID)
    LLAddress
    LLCity
    LLState
    LLZip
    PropAdd
    PropCity

    I would be thrilled to email someone a sample of the spreadsheet if that would help you help me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    You'll have to sort the worksheet (or the data source of the mail merge) by LLID. You can then use the technique described in How to use mail merge to create a list sorted by category in Word.
    It's a bit tricky to get it right, so read the instructions carefully.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Ok thanks. It looks ugly, but doable.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Hi Melanie,

    With the field coded provided in the KB example, an extra line is inserted between records for the same location when using an Excel workbook as the data source. Use the following (simpler) field coding instead:
    {QUOTE{IF{MERGESEQ}= 1 {SET Place ""}}"{IF{MERGEFIELD City}<> {Place} "{IF{MERGESEQ}= 1 "" "<ENTER>
    "}{MERGEFIELD City}{SET Place {MERGEFIELD City}}<ENTER>
    <ENTER>
    "}{MERGEFIELD Employee}<TAB>{MERGEFIELD Sales}<ENTER>
    "}<ENTER>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    I have tried this for about 4 hours and I just cannot get it to work. So I searched MS's knowledge base and found KB302665, which looks exactly like what I want. But, I can't get that to work either. I am feeling pretty frustrated, but not ready to give up. I'm attaching a screen shot of what I have gotten so far with following KB302665. Any ideas on what I'm doing wrong? When I do the merge, it doesn't show the property addresses (Address_1) listed. It does show just the landlord's name (Descr) one time for each letter.

    When I used your suggestion, Paul, I also got one landlord name (not the mailing address) for each letter, but no list of properties for the landlords.

    I really appreciate your patience with me. Do you have any suggestions for what I'm doing wrong? I want a letter for each landlord with the landlord's name (DESCR), landlord's address (Mail_Address, Mail_City, Mail_State, Mail_Postal) at the top and a list of all the properties for that landlord in the letter. It shouldn't be this hard. Of course, working with these merge codes like this is new to me. I've always used the wizard and been fine.

    Thank you for any help you throw my way.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Could you post a sanitized copy of your Excel workbook with a small number of records with dummy data (but with the real column headings)?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    I'm going to do a 2nd post to show you the letter also.

    Thank you.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    The letter
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    The attached zip file contains modified versions of the letter and workbook.

    I have added one dummy record to the workbook; this is necessary for the mail merge. You'll have to discard the last letter after merging.

    Open the Word document.
    Select Tools | Letters and Mailings | Mail Merge.
    In Step 1 of the mail merge wizard / task pane, select Directory, then click Next.
    In Step 2, just click Next.
    In Step 3, click Browse, then navigate to the Excel workbook and click Open.
    You can click OK in the dialogs that follow.
    Back in Word, click Next.
    In Step 4 and 5, just click Next.
    In Step 6, click Edit individual letters.
    Attached Files Attached Files

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Hi Melanie,

    Here's my version. It requires you to add a dummy last row to your data source, but otherwise does what I think you wanted.

    I'll leave you to sort out the document formatting, as you seem to have overriden a number of Styles.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Paul and Hans,

    Thank you so much. Both of your examples work perfectly for what I need. I have dissected Hans' version and recreated it on my own it to another document just to see if I could figure it out. I did it!! Woo ho. Thank you both soooo much!

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    Hans, this worked so well. I'm thrilled with it. The user has shown me, though, that if a letter extends to 2 pages, there is a header on the 2nd page. I can't see it until after the merge is done, so I can't edit it for all the records. The user has been deleting the header on each record that has one - a pretty tedious process for a 700-page merge. We either want a way to not have the header on the 2nd page or can you tell me how to edit it on the main document before we merge?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge letters to landlords with multiple prop (2003)

    That's easy:
    - Open the merge document (Landlordletter.doc).
    - Press Ctrl+End to go to the end of the document.
    - Press Ctrl+Enter to insert a page break.
    - Double click in the second page header (or select View | Header and Footer).
    - Press Ctrl+A to select the entire header.
    - Press Delete to remove the text.
    - Double click in the main document or click Close on the Header and footer toolbar.
    - Press Ctrl+End to make sure that you're at the end of the document.
    - Press Delete to remove the page break that you inserted earlier.
    - You'll now have one page again, but the second page (actually second section) page header is gone.
    - Perform the mail merge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •