Results 1 to 13 of 13
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Automate a Word Mail Merge From Access


    I have a customer who needs to print thank-you letters when they receive donations. Currently, the letters are generated as an Access report. Unfortunately, they need to be in Word format so the customer can "tweak" the content. Using Access' export functionality, I can convert them to Word documents, but the documents are not constructed properly. Each line in each paragraph converts to a separate word paragraph. Some components like images do not export properly. For these reasons, I want to instead create a mailmerge directly to Word.

    Most of the components in the letter can be simple merge fields. Names, addresses and paragraphs will populate properly from the data source table. The challenge is a list of details in the middle of the letter body. Example:

    Code:
    Date    Check#      Amount   Description
    12/1    1234        $25.00   xxxxx
    12/2    2345        $50.00   yyyyy
    I was thinking of having a Word table in the middle of the document template, but it is unclear to me how to populate this properly in the context of a mail merge. There may be multiple details for each record (letter). In the Access report version, I simply had a sub-report for the details, but this doesn't seem to be valid for a mail merge.

    Any idea how to populate a mail merge with multiple child records for each generated letter?

    Thanks...

    Edit: I suppose I could add some text fields to my output table, one field for each column shown above, and populate them before-hand with all the rows of detail data (so date 1 + CrLf + date 2, etc., goes into the "dates" output field, and similar for the other 3 output fields). Then I could use simple merge fields to show all the data. Would that be too complex?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Forms could be designed to allow users to 'tweak' content.

    Bing: access word mail merge with table

    Maybe this will help https://www.techrepublic.com/blog/mi...ord-documents/


    Last edited by June7; 12-12-2019 at 12:11 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I use this:

    http://www.gmayor.com/individual_merge_letters.htm

    Its never let me down and has clear instructions.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can highly recommend Albert Kallal's super easy mail merge.
    Its available in many places e.g. https://hitechcoach.com/downloads-ma...asy-word-merge

    Albert's code is complex but using it is indeed 'super easy' - just one line of code: MergeSingleWord
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks June7. Appreciate the reply. I added a "tweaking" ability to the Access app, but that wasn't versatile enough for customer. Merging into Word is what they want. The issue isn't how to merge from Access, but how to merge multi-level data to Word. Each record in my table is equivalent to a single letter in the mail merge. But as mentioned above, there is a detail table that I want to show in each letter, showing details about the donations made. In Access, this is so easy using a sub-report. Still working on this...

  6. #6
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks for the reply, Homegrownandy. My Malwarebytes blocked the site you suggested due to possible trojan. If it was describing how to do the merge via VBA, I've got that part covered. The difficult part for me is having merge letters with 2 levels of data, such as an Access report with a sub-report embedded. This doesn't seem to be easy in mail merge. I'm still thinking I need to run a process on my source table to read the detail records and chain them together with CrLf's and add that to my source table. Unless there is a better way that I haven't found yet. Thanks again...

  7. #7
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks for the quick reply Colin. I've actually used Albert's code in the past, and it's very good. Still doesn't solve the main problem, which is how to have each merge letter also contain "detail" records, such as an Access report with an embedded sub-report. Unless I can find a better way, I think I have to go with my idea of gathering the details for each record in the main merge data table and combining them with CrLf's into single fields, which I can update into the merge data records. Then add merge fields for those detail columns. Can you think of any better way to accomplish this requirement? Thanks again...

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That link I provided seems to show a details table. But I have not explored further.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    2
    Quote Originally Posted by RMittelman View Post
    Thanks for the quick reply Colin. I've actually used Albert's code in the past, and it's very good. Still doesn't solve the main problem, which is how to have each merge letter also contain "detail" records, such as an Access report with an embedded sub-report. Unless I can find a better way, I think I have to go with my idea of gathering the details for each record in the main merge data table and combining them with CrLf's into single fields, which I can update into the merge data records. Then add merge fields for those detail columns. Can you think of any better way to accomplish this requirement? Thanks again...
    I've used Albert's code many times where I've included data related to individual recipients as fields.
    For example in my schools apps, letters listing details of individual students activities and payments for an enrichment week, student timetables including classes and teachers.
    To do so, I created template letters placing fields inside a table grid. It works perfectly.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by isladogs View Post
    2

    I've used Albert's code many times where I've included data related to individual recipients as fields.
    For example in my schools apps, letters listing details of individual students activities and payments for an enrichment week, student timetables including classes and teachers.
    To do so, I created template letters placing fields inside a table grid. It works perfectly.
    Thanks Colin. I'm not sure I correctly stated my problem. The example in my OP was not complete. The thank-you letter contains name, address, salutation and paragraphs. The example above is a list of donations in the middle of the letter. The name, address, etc. will be placed into merge fields on the merge document. So I have main/parent data for those items, then I have detail/child data for the list of donations. So each merge letter must contain heirarchical data.

    If I do a join query, then of course all fields are repeated for each different detail field values, right? This doesn't seem good to use that as a merge data document. So I decided to create the 3-4 detail fields in the table as single fields with their individual values separated by CrLf's. That gives me a single record for each letter. Those detail fields will be popped into merge fields inside of a word table in the main merge document template. In this case I'm only using a 2-row word table, row 1 for titles and row 2 for data, which contains the merge fields. If all goes right, the single row 2 in the table should end up looking like multiple rows on the word document.

    Are you saying that I don't need this type of arrangement, and somehow Albert's code can populate heirarchical data into a merge document? Or multiple merge documents (I seem to remember his code works for a single record or multiple records)? So each letter can contain both top-level data (salutation, paragraphs, etc.) and child-level data (list of donation info for that person)?

    Sorry to be so unclear.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    AFAIK you can only base a mail merge letter on a single table or query. Therefore from your description it would be necessary to do one of the following:
    a) duplicate the address details in each record
    b) add your tabulated data as a single text block formatted the way you want it to appear
    I've done both methods but in general prefer b)

    In case it helps, I've attached two random Word templates used in conjunction with Albert Kallal's code.
    Also a sample text file merge.888 used to populate templates like thise
    The fields are shown as <<....>> in the letters
    NOTE: All school/student data is for a 'dummy' school used in DEMO versions of the database
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Hi Colin,
    I went with your obtion B with slight modifications. Instead of newline characters (vbCrLf), I export my data to CSV using "\n" where I want the line breaks, then after completing the merge, I do a search and replace in Word, finding "\n" and replacing with Chr(13), which is the equivalent of Word paragraph mark. This is because embedding CrLf's in data plays havoc with the CSV file.

    This works fine, but exposes another challenge: My rich text fields are exported with all the html markup as expected, but Word doesn't present them as formatted text like you'd expect. Instead, the merged Word document shows those fields with html tags. I googled about this, and there doesn't seem to be a fix except exporting the marked-up text to an html file, then importing this into Word. I tried editing the CSV file and surrounding field data containing tags with <html>.....</html>, but that just messes up the mail merge, and Word can't complete it.

    I was able to export the source table to html, then when I open that in Excel, it shows the rich-text fields with the desired formatting. However, when I do the merge in Word using the html file as the data source, the formatting is missing. So that won't work.

    I can change all my Access fields to plain text and let the Word template do the formatting. Less desirable, because the customer likes being able to bold some words, but this may be the only option.


    Can you think of any way to get Word to do the mail merge and actually see the formatted text when it's all done?

    Thanks...

  13. #13
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    For simple Word Mail-Merge don't need any Code: Invoking Mail-Merge from Access

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Mail merge from Access to Word
    By Matthew7 in forum Access
    Replies: 1
    Last Post: 02-18-2015, 07:46 AM
  2. automate mail merge from access
    By sssandhya89 in forum Access
    Replies: 1
    Last Post: 04-23-2013, 01:15 PM
  3. Mail merge from from Access to Word
    By williamgladstone in forum Access
    Replies: 1
    Last Post: 03-22-2011, 12:00 PM
  4. Access / Word mail merge problem.
    By PD1117 in forum Access
    Replies: 0
    Last Post: 07-06-2010, 09:41 AM
  5. Mail Merge from Access to Word
    By Rachelkm2 in forum Programming
    Replies: 1
    Last Post: 05-29-2009, 02:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums