Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2022, 08:21 AM
Fredstar Fredstar is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2019
Novice
Automate Mailmerge for multiple documents from one datasheet
 
Join Date: Feb 2022
Posts: 1
Fredstar is on a distinguished road
Default Automate Mailmerge for multiple documents from one datasheet

Hi!

I've created a mailmerge for about 20 different Word documents that all use the same data from my single data sheet. Currently, I have to go into each Word document and save the mailmerge as a PDF. This is fine but gets tiring when it needs to be done 20 times.



I've tried to automate this with VBA however when I open the Word document using VBA, it does not recognise it as being mailmerged and only opens the template file, which makes the whole mailmerge pointless. The mailmerge link is just removed.

Does anyone know why VBA does not open the mailmerged documents and if this can be fixed?

Are there even any other, better ways to automate the creation of 20 documents all using data from a single excel sheet?

For info, each document is just a clients personal information and using a mailmerge is just quicker than copying/editing/amending each document!

Many thanks for any help.

Fred
Reply With Quote
  #2  
Old 02-02-2022, 10:19 PM
gmayor's Avatar
gmayor gmayor is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,910
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

While it is not exactly clear what you are doing, DocBundler (Create multiple documents from commond data sheet) may work for you.

Alternatively you may be able to use Merge and Split to create individual merge documents in PDF format.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 02-05-2022, 01:01 PM
Charles Kenyon Charles Kenyon is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 8,388
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

This is written to give Graham a better idea of what it is I think you want to be doing. I use templates of primary merge documents like this to produce draft pleadings and letters for cases.

  1. The templates are not stored as merge documents but as normal Word documents.
  2. My vba creates a new document based on the template and then makes it a merge document linking it to the database.
  3. It then has me pick the client and previews the merge to that new document. Note that this is still a primary merge document. (This method is frowned on by most experienced users.)
  4. I edit and then switch the preview document back to a normal Word document with a macro. That macro also locks all the merge fields. You would not be editing first so this process could be included in the main macro.
This process could be done with a set of primary merge documents. I would likely use Graham's Document Batch Processes. I suppose there really is no need to use a template, just merge from the primary merge documents.

However, I name and save documents based on a combination of the document property "Title" and the date. It should also be possible to save a merge field or two as document variables in the new document and use those as well. I am unsure how I would do that. The processes for that are a Save intercept it the templates. If a completed merge were done rather than a preview, I believe the link to the intercept macro would be lost. It should still be possible to do but I have not tried it.

I am only doing one to three documents at a time, and seldom the same ones, so I do not batch process.
Reply With Quote
  #4  
Old 02-28-2022, 03:29 AM
Peter Talbot Peter Talbot is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2021
Novice
 
Join Date: Feb 2022
Posts: 1
Peter Talbot is on a distinguished road
Default Automating Mail Merge

Hi Fredstar
I recently produced some scripts to automate mail merge and the code could well be adapted to suit your purpose. Head to Automating Mail Merge with Microsoft Word to see if this could help.
Reply With Quote
  #5  
Old 03-03-2022, 01:54 AM
gmayor's Avatar
gmayor gmayor is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,910
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
Originally Posted by Fredstar View Post
Are there even any other, better ways to automate the creation of 20 documents all using data from a single excel sheet?
Fred
I have been thinking about this for the past few days and felt it worth producing an add-in to merge a group of documents to document or PDF format and including the option to merge to e-mail attachments, each using the same worksheet. I will make it available from my web site in due course, but if you want to try it in the meantime, contact me via my web site.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 03-06-2022, 05:35 PM
macropod's Avatar
macropod macropod is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you combine all the mailmerge main documents into a single document, with a separate Section for each of your present source documents, you could use the code from the Send Mailmerge Output to Individual Files example in the Mailmerge Tips & Tricks 'Sticky' thread at the top of this forum (https://www.msofficeforums.com/mail-...ps-tricks.html) with the following modifications:
1. Add
, Sctn As Section
to the 'Dim' line at the top of the code

2. Replace:
Code:
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
with:
Code:
        For Each Sctn In .Sections
          Sctn.Range.Select
          With Selection
            Do While .Characters.Last = Chr(12)
              .End = .End - 1
            Loop
          End With
          .ExportAsFixedFormat OutputFileName:=StrFolder & StrName & "_" & Sctn.Index & ".pdf", _
            ExportFormat:=wdFormatPDF, OpenAfterExport:=False, _
            OptimiseFor:=wdExportOptimizeForPrint, Range:=wdExportSelection
        Next
With these changes, you will get 20 separate documents for each record in your data source.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 03-06-2022, 09:37 PM
gmayor's Avatar
gmayor gmayor is offline Automate Mailmerge for multiple documents from one datasheet Windows 10 Automate Mailmerge for multiple documents from one datasheet Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,910
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
Originally Posted by gmayor View Post
I have been thinking about this for the past few days and felt it worth producing an add-in to merge a group of documents to document or PDF format and including the option to merge to e-mail attachments, each using the same worksheet. I will make it available from my web site in due course, but if you want to try it in the meantime, contact me via my web site.
Nowavailable - Multiple Document Mail Merge
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Tags
mailmerge automate

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting an excel datasheet AydanAr Excel 5 03-15-2019 02:50 AM
Automate Mailmerge for multiple documents from one datasheet Best way to automate combining of multiple workbook data on trigger? dylansmith Excel Programming 4 02-13-2017 04:33 AM
How to automate opening multiple URLs from an Excel Spreadsheet? rishumehra Excel Programming 0 01-28-2016 01:49 AM
Automate Mailmerge for multiple documents from one datasheet Mailmerge Individual Documents DaddyMac Mail Merge 4 06-06-2013 09:22 PM
Merge Word documents using a mailmerge field Concertina Mail Merge 3 02-18-2013 04:12 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft