Mail Merge letters to landlords with multiple prop (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Ok thanks. It looks ugly, but doable.

Reply | Quote

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 “” ”
“}{MERGEFIELD City}{SET Place {MERGEFIELD City}}

“}{MERGEFIELD Employee}{MERGEFIELD Sales}
“}

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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)?

Reply | Quote

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

Thank you.

Reply | Quote

The letter

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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!

Reply | Quote