Mail Merge with expanding table

I am trying to put together a letter that takes data from a large table of real estate agents that have tenants who have received financial assistance for their Bond.  

I have a letter that is the first page, that needs to include the field:
Landlord Name

Then a table on the 2nd page that needs to include the fields:
Tenant Name
Property Street Address
Property Suburb
Property Bond Number
Bond Amount
Date Paid

I have tried to use formulas to get this to work, but I can only get a separate letter for each landlord and each tenant.

I need a letter for each landlord with a list of their tenants in each letter - so some may have one tenant and some may have twenty tenants.

Any help would be greatly appreciated.

I've attached the letter (basic mail merge only) and the file I've been using of test data.
Angela PercyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I think the most efficient way would be to provide the list of tenants in a separate form to send with the letter.

Simply run the macro in the attached workbook. I have added an option to print each sheet if you want. A simple mail merge with word can produce the letters
Angela PercyAuthor Commented:
Roy_Cox, thank you.  I ran the macro but it only populated each worksheet with the headings - not the actual data.  Have I done something wrong?
Roy CoxGroup Finance ManagerCommented:
Soryy, I must have uploaded before saving my final code. Try this

I'm off to work now, but if you need any further adjustments I'll look at it later.

If you emai the reports we could save each sheet to a separate workbook and email it.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Angela PercyAuthor Commented:
It worked!  Thank you so much - I will try it on some real data and see how I go.

Thanks again.
Roy CoxGroup Finance ManagerCommented:
I'll check back later
Helen FeddemaCommented:
This would be much easier to do in Access, if it is possible to store the data in Access tables.  My Access Archon #44  has an example of creating Word documents from tables in a one-to-many relationship (the "many" items are placed into a Word table, which can expand to as many rows as needed).

However, it is an old format database, and you may not be able to open it.  I am attaching a slightly updated (Access 2000) version of this database; you will need to edit the templates path for your computer.  Or for an updated and more sophisticated version, see the 4th edition of my Working with Word ebook, available from Office Watch.

here is a screen shot of the Word document:
Northwind invoiceModified-Northwind-2000.mdb
Angela PercyAuthor Commented:
Thank you Helen for your suggestion, it certainly is what I want to achieve, however we are not permitted to use Access within our workplace.   Thanks again.
Roy CoxGroup Finance ManagerCommented:
Unfortunately, many places of work do not use Access.

Did the code that I provided work with real data, providing that the data was laid out as in the example you should have had no problem
Angela PercyAuthor Commented:
It did work thank you.  I'd still really like to incorporate it into a one step mail merge - but it doesn't look like that is possible.

Many thanks for your assistance Roy and Helen.
Roy CoxGroup Finance ManagerCommented:
You could do it all from Excel if you post a copy of the letter I'll see whether it can be done.
Angela PercyAuthor Commented:
Hi Roy - there has been a slight change in the information within the report we are getting the data for the letters from.  I've highlighted the cells where we need to extra the data from into the letter (attached).
Roy CoxGroup Finance ManagerCommented:

I'll see what I can do with these for you. I'm busy at work but I'll take a look as soon as I can
Angela PercyAuthor Commented:
Roy thank you so much - I really appreciate you taking your time to have a look for me.
Roy CoxGroup Finance ManagerCommented:
Hi Angela

Sorry about the delay but I have been so busy at work.

I have set this all up to work from Excel. The basics are there and it just needs some tweaking to make sure that the report is correct

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angela PercyAuthor Commented:
Thanks so much Roy - your assistance has been wonderful.
Roy CoxGroup Finance ManagerCommented:
Pleased to help. If you want help tweaking this then post back.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.