Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / September 2005

Tip: Looking for answers? Try searching our database.

Form letter

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BTC - 14 Sep 2005 19:37 GMT
I have a database that contains landlord names and their mailing addresses and their rental property addresses. Some landlords own one rental property and some own multiple properties.

My database is in excel. It simply lists all the rental properties.

Such as:

Landlord        Mailing Address                       Rental Property

COL1            COL2        COL3 COL4  COL5           COL6 COL7    COL8  COL9  COL10
John Brown      PO Box 123  City State Zip            1111 Street  City  State Zip
John White      PO Box 456  City State Zip            2222 Street  City  State Zip
John Black      PO Box 789  City State Zip            3333 Street  City  State Zip
John Appleseed  PO Box 999  City State Zip            2131 Street  City  State Zip
John Appleseed  PO Box 999  City State Zip            2233 Street  City  State Zip
John Appleseed  PO Box 999  City State Zip            2333 Street  City  State Zip

In the example above, I have 4 landlords and 6 properties. I would like to send out four letters. Each letter would reference the property. I know how to set up my form letter to merge 1 property, but how can I set it up to list all three properties for John Appleseed in my example.

Any help will be greatly appreciated.

Bruce
Doug Robbins - 14 Sep 2005 21:04 GMT
Word does not really have the ability to perform a "multiple items per condition (=key field)" mailmerge.

See the "Multiple items per condition" item under the "Special merges" section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/MergFram.htm

Or take a look at the following Knowledge Base Article

How to Work Around Duplicate Names in Mail Merge Data

http://support.microsoft.com/default.aspx?scid=kb;en-us;302665



Or, if you create a Catalog (on in Word XP and later, it's called Directory) type mailmerge main document with the mergefields in the cells of a one row table in the mailmerge main document with the keyfield in the first cell in the row and then execute that merge to a new document and then run the following macro, it will create separate tables with the records for each key field in them.  With a bit of further development, you may be able to get it to do what you want.

' Macro to create multiple items per condition in separate tables from a directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1, numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
   Set tcat = ttab.Cell(j, 1).Range
   tcat.End = tcat.End - 1
   Set scat = stab.Cell(i, 1).Range
   scat.End = scat.End - 1
   If scat <> tcat Then
       ttab.Rows.Add
       j = ttab.Rows.Count
       ttab.Cell(j, 1).Range = scat
       ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
       ttab.Rows.Add
       ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
       For n = 2 To k
           Set data = stab.Cell(i, n).Range
           data.End = data.End - 1
           ttab.Cell(ttab.Rows.Count, n - 1).Range = data
       Next n
   Else
       ttab.Rows.Add
       For n = 2 To k
           Set data = stab.Cell(i, n).Range
           data.End = data.End - 1
           ttab.Cell(ttab.Rows.Count, n - 1).Range = data
       Next n
   End If
Next i

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis.

Doug Robbins - Word MVP

 I have a database that contains landlord names and their mailing addresses and their rental property addresses. Some landlords own one rental property and some own multiple properties.

 My database is in excel. It simply lists all the rental properties.

 Such as:

 Landlord        Mailing Address                       Rental Property

 COL1            COL2        COL3 COL4  COL5           COL6 COL7    COL8  COL9  COL10
 John Brown      PO Box 123  City State Zip            1111 Street  City  State Zip
 John White      PO Box 456  City State Zip            2222 Street  City  State Zip
 John Black      PO Box 789  City State Zip            3333 Street  City  State Zip
 John Appleseed  PO Box 999  City State Zip            2131 Street  City  State Zip
 John Appleseed  PO Box 999  City State Zip            2233 Street  City  State Zip
 John Appleseed  PO Box 999  City State Zip            2333 Street  City  State Zip

 In the example above, I have 4 landlords and 6 properties. I would like to send out four letters. Each letter would reference the property. I know how to set up my form letter to merge 1 property, but how can I set it up to list all three properties for John Appleseed in my example.

 Any help will be greatly appreciated.

 Bruce
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.