Solved

Mail Merge with expanding table

Posted on 2016-08-16
16
54 Views
Last Modified: 2016-08-29
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
Address
Suburb
Postcode
State

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.
Let---Bond-Review---Lessor---DRAFT-.docx
Copy-of-DATA---Test-Data-Bond-Review.xls
0
Comment
Question by:Angela Percy
  • 8
  • 7
16 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41758896
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
Copy-of-DATA---Test-Data-Bond-Revie.xlsm
0
 

Author Comment

by:Angela Percy
ID: 41758900
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?
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41758912
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.
Copy-of-DATA---Test-Data-Bond-Revie.xlsm
1
 

Author Comment

by:Angela Percy
ID: 41758917
It worked!  Thank you so much - I will try it on some real data and see how I go.

Thanks again.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41758929
I'll check back later
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41762608
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).

http://www.helenfeddema.com/Files/accarch44.zip

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
0
 

Author Comment

by:Angela Percy
ID: 41764640
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.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41764806
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Angela Percy
ID: 41764809
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.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41765683
You could do it all from Excel if you post a copy of the letter I'll see whether it can be done.
0
 

Author Comment

by:Angela Percy
ID: 41766152
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).
TEST-Data---Mailmerge.xlsx
Let---Bond-Review---Lessor---DRAFT-.docx
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41767289
Hi

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
0
 

Author Comment

by:Angela Percy
ID: 41767859
Roy thank you so much - I really appreciate you taking your time to have a look for me.
0
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41775123
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
TEST-Data---Mailmerge.xlsm
0
 

Author Closing Comment

by:Angela Percy
ID: 41775579
Thanks so much Roy - your assistance has been wonderful.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41775769
Pleased to help. If you want help tweaking this then post back.
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now