Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word Directory Mail Merge Multiple Records into an Existing Table

    Hi,
    I've researched and followed the recommendations for mail merging multiple records on to a single Word page as defined in Microsoft's KB294686 . However, I may have a slightly unique twist in my requirements. We have a Word form created as a table to be used for creating a profile of our managers for the purpose of succession planning. The form will be distributed with basic employee information and history, and will include multiple dropdown boxes to rate the employee's readiness in different areas.

    I am able to perform a mail merge to fill in the basic information, but I also need to include past positions held within the company. When I use the directory merge method, I am able to obtain the information I need, but each historical job position is printing on a new page instead of within the table cell on the form. I believe that because I am attempting to use this logic within a table, it is not behaving as desired.

    Does anyone have a suggestion to allow me to achieve printing multiple records within one cell?

    My Excel data is formatted similar to this:
    Excel SS.gif

    The form looks like this:
    Merge Form.gif

    Here is a view of the form showing formatting:
    Merge Form wth formatting.gif

    Form Code used within the table cell:
    { IF { MERGESEQ } = "1" "{ MERGEFIELD BeforePosition } { MERGEFIELD BeforePositionStartDate }
    " "" }{ SET Place1 { MERGEFIELD FullName }}
    { If { Place2 } <> { Place1 }"{ MERGEFIELD BeforePosition } { MERGEFIELD BeforePositionStartDate }" "{ MERGEFIELD BeforePosition } { MERGEFIELD BeforePositionStartDate }" }{ SET Place2 { MERGEFIELD FullName }}

    I hope there is a way to get this done using Word/Excel as I don't have the time to try to create an Access application and those skills are very rusty.

    Thanks,
    Tom
    Last edited by TomA13; 2016-08-19 at 11:10. Reason: Adding an additional image

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Did you read my Microsoft Word Catalogue/Directory Mailmerge Tutorial, in the 'Sticky' thread at the top of this forum?:
    http://windowssecrets.com/forums/sho...merge-Tutorial

    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. An example of this approach can be found at: http://answers.microsoft.com/en-us/o...1-1996c14dca5d
    The DATABASE field can even be used without recourse to a mailmerge. An example of such usage can be found at: http://www.msofficeforums.com/mail-m...html#post67097
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul,
    Yes, I read your tutorial a few times. It contains a significant amount of information and I think I can follow along for the most part, but to be honest I feel a bit overwhelmed as I read it and am challenged as to which option I should be attempting. In my case, basically adding table rows within an existing table, which option do you recommend? I have spent an embarrassing number of hours trying to get this right.

    Of the alternative options regarding using a DATABASE field you mentioned in your reply above, I believe that the first one more closely resembles my desired outcome than the second one, however, the example provided outputs the results to email. What command would I need to change to have the result output to a file that I could then forward as necessary to the appropriate recipient? They will need to complete the form and return it to another person.

    I'm attaching my draft document and data so you can have a closer look and, hopefully, recommend what you believe is the best option.

    I appreciate the time you are spending to share your wisdom.

    Thanks,
    Tom
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The tutorial example you'd use for your Talent Development Review is from the Using an Extra Field to Generate the Trailing Content Per Group portion. However, it's by no means apparent from your document as to which parts of the output require the variable number of rows. Indeed, it appears you're wanting to output multiple records into the same cell (Current Company Experience (Title/Dates)). The tutorial content isn't designed for that - you'd get a separate row for each entry, which also means your overall table height will vary from person to person. If that's acceptable, you'll need split your table up as per the example under Calculating Group and Sub-Group Totals.

    If you want to go down the DATABASE field route, you'll still need to split the table, inserting the DATABASE field where the grouped data are to appear. You could then use a macro such as:
    Code:
    Sub Merge_Groups_To_Individual_Files()
    Application.ScreenUpdating = False
    Dim MainDoc As Document, StrFolder As String, StrName As String, i As Long
    Set MainDoc = ActiveDocument
    With MainDoc
      StrFolder = .Path & Application.PathSeparator
      For i = 1 To .MailMerge.DataSource.RecordCount
        With .MailMerge
          .MainDocumentType = wdFormLetters
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
          End With
          If Trim(.DataFields("Last_Name")) = "" Then Exit For
          If .DataSource.DataFields("Last_Name") & "_" & .DataSource.DataFields("First_Name") <> StrName Then
            StrName = .DataSource.DataFields("Last_Name") & "_" & .DataSource.DataFields("First_Name")
            .Execute Pause:=False
            With ActiveDocument
              .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
              ' and/or:
              .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
              .Close SaveChanges:=False
            End With
          End If
        End With
      Next i
    End With
    Application.ScreenUpdating = True
    End Sub
    Note that this will output and save one file per group to the mailmerge main document's folder.

    Finally, I note your mailmerge main document contains formfields. These are not compatible with a mailmerge and will be deleted by whatever mailmerge approach you take.
    Last edited by macropod; 2016-08-20 at 18:27.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •