locked
Mail Merge from Datatable RRS feed

  • Question

  • I'm building a Visual Basic application to pull a data set to a datatable, then iterate over each row of the datatable and merge that record ONLY with the appropriate template based on the values of some of the data.  Looking to create one output file collated by case so all documents belonging to the same case are sequential.  Each datatable record will require 1-3 separate mail merges using a different template each time.

    Having a hard time sorting out the how-to's of the logic here.  So far, I have been unable to find any documentation on using a datatable as a datasource for a mail merge, which leads me to believe perhaps datatables won't work as mailmerge sources.

    So now I'm trying to consider what method to use here and am having a terrible time. Here's some pseudo-code I've come up with so far:

    Generate data source into datatable

    For each row in datatable:

     For each document in DocumentsRequired:

      Open document template

      Merge document with specific datatable record & copy merged output

      Close Merge doc template

      Open source doc & paste

      Clear clipboard

     Next document

    Next Row

    Questions:

        Is it possible to mailmerge.OpenDataSource using one row from a datatable?

        Is the method described above in the pseudo-code the best approach to the issue (in your opinion)?

        Any advice / changes / corrections to the above?

    Any help is much appreciated!

    Tuesday, December 27, 2016 6:07 PM

Answers

  • See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

    The VBA code there will work with any mailmerge data source. As for outputting a single record, you could:
    • change the record range encompassed by 'For i = 1 To .MailMerge.DataSource.RecordCount'
    • add a test to the code to compare the current record against a criterion;
    • add a mailmerge filter to the mailmerge main document; or
    • add the required SQL code for filtering to the macro itself, via the OpenDataSource method.
    The macro there assumes the merge is being run from Word. Numerous changes would be required to automate the merge from another application.

    As for the "Each datatable record will require 1-3 separate mail merges using a different template each time", a single mailmerge main document can be configured to generate entirely different letters via the use of field coding that tests the value of one or more fields in the data source.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Isaac Asher Wednesday, January 11, 2017 8:08 PM
    Thursday, December 29, 2016 10:19 PM

All replies

  • Hi Isaac Asher,

    I have some confusion with the description that you had mentioned above.

    you had posted the issue in VSTO Forum.

    then in the first line you had mentioned that ,"I'm building a Visual Basic application".

    so are you developing with VSTO or VBA?

    from your first line I assume that you are working with VBA and by mistake posted the issue in VSTO forum.

    you had asked,"Is it possible to mailmerge.OpenDataSource using one row from a datatable?".

    you need to use loop to fetch each record from datatable. then you can perform your desire operation on that.

    please refer the example mentioned below.

    This example loops through the records in the data source and verifies that the postal code field (field six in this example) is not fewer  than five digits. If it is, it removes the record from the mail merge. If you want to make sure that the locator code is added to the postal code, you can change the length value from 5 to 10. Therefore, if a postal code is fewer than ten digits it will be removed from the mail merge.

    Sub ExcludeRecords() 
     
     On Error GoTo ErrorHandler 
     
     With ActiveDocument.MailMerge.DataSource 
     .ActiveRecord = wdFirstRecord 
     Do 
     
     'Counts the number of digits in the postal code field and if 
     'it is fewer than 5, the record is excluded from the mail merge, 
     'marked as having an invalid address, and given a comment 
     'describing why the postal code was removed 
     If Len(.DataFields(6).Value) < 5 Then 
     .Included = False 
     .InvalidAddress = True 
     .InvalidComments = "The ZIP Code for this record" & _ 
     "has fewer than five digits. This record will be" & _ 
     "removed from the mail merge process." 
     End If 
     If .ActiveRecord <> .RecordCount Then 
     .ActiveRecord = wdNextRecord 
     End If 
     Loop Until .ActiveRecord = .RecordCount 
    ErrorHandler: 
     
     End With 
     
    End Sub
    

    Reference:

    MailMergeDataSource.RecordCount Property (Word)

    this is just an example for you to understand the code and logic. you have to develop your own code based upon code above.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 28, 2016 1:02 AM
  • See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

    The VBA code there will work with any mailmerge data source. As for outputting a single record, you could:
    • change the record range encompassed by 'For i = 1 To .MailMerge.DataSource.RecordCount'
    • add a test to the code to compare the current record against a criterion;
    • add a mailmerge filter to the mailmerge main document; or
    • add the required SQL code for filtering to the macro itself, via the OpenDataSource method.
    The macro there assumes the merge is being run from Word. Numerous changes would be required to automate the merge from another application.

    As for the "Each datatable record will require 1-3 separate mail merges using a different template each time", a single mailmerge main document can be configured to generate entirely different letters via the use of field coding that tests the value of one or more fields in the data source.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Isaac Asher Wednesday, January 11, 2017 8:08 PM
    Thursday, December 29, 2016 10:19 PM