locked
How to mail merge one to many...HELP!!! RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,

    I have a tough problem that I have never tried before.  I am needing to create letters that are sent to lawyers.  The problem is that the letter will be sent to one lawyer but have multiple clients on the same letter.  Previously, that lawyer would get 30 of the same letters, but with different clients, sent to him.  I am trying to have one letter to the lawyer with many clients on it. In my excel file, each lawyer does have a unique id. 

    Any help is MOST appreciated!

    Thanks again,

    Steven
    Monday, February 20, 2012 3:25 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    In Graham Mayor's add-in, you can specify a "Key Field" - this will be the Lawyer ID in your situation. And you can specify "Child Fields" - those are the fields to be repeated for each client, such as ClientGender, ClientFirstName etc.

    All fields that you don't specify as either Key Field or Child Fields will be displayed only once per message (you still have to add them as merge fields).

    Please read the instructions on http://www.gmayor.com/ManyToOne.htm carefully.

    Regards, Hans Vogelaar

    Monday, February 20, 2012 9:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, February 22, 2012 9:55 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Word MVP Graham Mayor has created a free add-in for this purpose: http://www.gmayor.com/ManyToOne.htm.

    Regards, Hans Vogelaar

    Monday, February 20, 2012 3:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hey,

    Thank you very much for the replies.  Sorry for my complete ignorance on this but I am not sure how to use this new add in with my issue.  I wanted to give an example of the letter and then example of data that would go into letter hoping that this will help make more sense. Obviously the data would have every merge field listed but I could not get it to fit in this reply.

    <Date>                                                                                                                          


    Mr. <Lawyer Name>                                                              

    <Lawyer Address 1>

    <Lawyer Address 2>

    <Lawyer City, State, Zip>

     

    Dear Mr. <Lawyer Name>:

     

    As part of our ongoing commitment to provide our members the highest quality legal care, <Legal Product Name> recently performed a legal evaluation for your client(s):

     

    Client: <Mr./Ms. First Last Name>      ID: <Lawyer ID>     DOB:  <MM/DD/XXXX>

    Client: <Mr./Ms. First Last Name>      ID: <Lawyer ID>     DOB:  <MM/DD/XXXX>

     

    The purpose of this evaluation is to provide a single document that encompasses all of the client’s legal issues. We wanted to make you aware of our findings. We respect the fact that all legal decisions rest with you.


    ---------------------------------------------------------------------------------------------------------------------------------------------------------

    Sample data pasted from excel:

    ClientGender Mr_Ms ClientFirstName1 ClientLastName LawyerStreet LawyerStreet2 Lawyer City Lawyer State Lawyer Zip Client DOB
    M Mr Bill Watson 3105 Hillgate Rd   Columbus TX 43207 2/24/1976
    F Ms Betty Bolle 7218 Alma Terrace Dr   New Albany TX 43054 1/3/1952
    M Mr John Decarlo Jr 8236 Kathleen Circle   Reynoldsburg TX 43068 9/24/1987
    F Ms Mildred Pierce 5215 Groveport Rd Lot 97 Groveport TX 43125 12/13/1995
    M Mr Jerry Lowe 1138 Northlawn Dr   Springfield TX 45503 3/2/1941
    F Ms Gladys Buechner 145 S Cedar Ave   Lancaster TX 43130 11/15/1981
    F Ms Wanda Hunter 5247 Quincy Dr   Columbus TX 43232 2/26/2012
    M Mr Charles Haley 742 Midway Dr   Newark TX 43055 8/28/1976
    F Ms Charlotte Ramey 1606 Jackson Rd   Columbus TX 43223 3/4/1963
    F Ms Barbara Bush 2301 Ghent Ave   Kettering TX 45420 9/17/1972
    F Ms Mary Cadwallader 140 Imperial Dr Apt 243 Gahanna TX 43230 11/30/1975
    F Ms Dorothy Decarlo 8236 Kathleen Circle   Reynoldsburg TX 43068 10/25/1985
    F Ms Margaret Wright 2669 Thorndale Ave   Columbus TX 43207 12/1/1998
    F Ms Dena Ketter 3270 Penfield Rd   Columbus NY 43227 6/6/1976
    M Mr John Fouts 494 Westmoor Pl   Columbus OH 43204 2/1/1991
    M Mr Alan Thick 4343 Thomas Pl   Columbus CA 43207 7/21/1959
    F Ms Mary Martin 470 Hedges St   Ashville CA 43103 5/20/1984
    F Ms Jeannine Bahkta 12907 Staddens Bridge Rd   Heath CA 43056 8/18/1998
    M Mr Bob Tombs III Po Box 1125   Buckeye Lake CA 43008 4/14/2001
    M Mr James Buxton 625 Lismore Dr   Delaware CA 43015 10/5/1989
    F Ms Charlotte Aler 903 Danwood Drive   Columbus CA 43228 11/14/2005
    F Ms Phyllis Latham 2575 Gift St   Millersport CA 43046 5/9/1983
    F Ms Patricia Holland 5765 W Jefferson Kiousville Rd   London CA 43140 2/1/1949
    M Mr Charles Jones 228 N Lowell Rd   Columbus CA 43209 9/17/1956
    M Mr Thomas Crowne 955 Coonpath Rd Ne   Lancaster CA 43130 1/1/1982
    F Ms Lisa Landrum 521 E Johnstown Rd   Gahanna CA 43230 9/23/1995
    F Ms Kathleen Cummins 1051 S Main St Apt 209 Baltimore CA 43105 10/27/1982


    Monday, February 20, 2012 4:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The source data would have to be sorted by lawyer. It's not clear from your sample whether that's the case.

    Regards, Hans Vogelaar

    Monday, February 20, 2012 8:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Not sure if there is a way to attach the sample excel file to show you all fields.  Let me know if there is and I will get that done to make it easier.  Like the letter, there is a column for the lawyer and the lawyer has his own unique id.  So I am not sure how I can perform the mail merge with the single lawyer with many clients in the one letter.  Previously the lawyers were getting 50 letters all the same with a single client info.  This will allow it to be one letter with 50 clients.  Hope that helps clear it up a little.  Let me know if there is any other info you would need?

    Thanks for the reply,

    Steven

    Monday, February 20, 2012 8:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In Graham Mayor's add-in, you can specify a "Key Field" - this will be the Lawyer ID in your situation. And you can specify "Child Fields" - those are the fields to be repeated for each client, such as ClientGender, ClientFirstName etc.

    All fields that you don't specify as either Key Field or Child Fields will be displayed only once per message (you still have to add them as merge fields).

    Please read the instructions on http://www.gmayor.com/ManyToOne.htm carefully.

    Regards, Hans Vogelaar

    Monday, February 20, 2012 9:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In Graham Mayor's add-in, you can specify a "Key Field" - this will be the Lawyer ID in your situation. And you can specify "Child Fields" - those are the fields to be repeated for each client, such as ClientGender, ClientFirstName etc.

    All fields that you don't specify as either Key Field or Child Fields will be displayed only once per message (you still have to add them as merge fields).

    Please read the instructions on http://www.gmayor.com/ManyToOne.htm carefully.

    Regards, Hans Vogelaar


    Thanks!  I will try using his add-in first thing in the morning.  I tried another add in that seemed to be a little more complicated.  I will post back if I run into issues. 
    Wednesday, February 22, 2012 5:59 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, February 22, 2012 9:55 PM