Multiple Files - Printing

[thalantyrdsl]

Hi all. First time poster. Very new to mail merge. Please bear with me.



I'm currently working in a new role where the staff are using mail merge to insert information info specific fields throughout around about 100 different documents.

- They've set up an XLS document with the information.
- Each new staff member creates their own copy of the 100 documents, opens them individually and re-points the mail merge to their own copy of the XLS document
- For each project they open each document individually, hit Finish & Merge, update Individual Document, then hit Print to the actual printer

This is obviously an extremely lengthy process. There are some things I can do in the background to minimise the work required but the main issue is trying to find a way to make this more efficient.

So, my questions are:

a) Is there a way that you can open 100 documents in bulk and have them all update at once using the mail merge feature? This would mean we don't have to update them individually. (Note: At this point they don't save the documents, only print them, but saving would be helpful, unless too difficult).

b) As a closing step to the above point-A, is there a piece of software or a trick in Word/Windows where I can select say 75/100 documents and then mass-print them to the printer? (Sometimes we only need to print 75% of the documents).

Please let me know if I've explained this properly.

Thank you in advance.

[macropod]

First, I'd have to question why you'd have 100 separate mailmerge main documents if they so often need processing as a group. Why not combine as many as possible into a single document?

Second, you could use macros to automate both the linking of the copies to the user's mailmerge data source workbook and to drive the merge process. Such a macro can even save the files - the Send Mailmerge Output to Individual Files topic in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum (https://www.msofficeforums.com/mail-...ps-tricks.html) contains a macro for doing that kind of thing.

Third, you can select multiple files in File Explorer, then right-click and choose Print - without having to open the files. This won't be particularly helpful for mailmerge main documents, though, as that won't necessarily result in the relevant records being printed.

[thalantyrdsl]

Thank you Paul.

The documents probably could be put into a single document, and it may again show my lack of experience with Word, but the documents are all sorts of safety documents, each one of them has it's own expiry date, accreditation, etc. We keep them separate at the moment as not every document applies to every project, so we'd cherry pick which we use to create our hard copy project pack.

I did have a quick look at the sticky output macro and will have another look now as I didn't quite understand what I was looking at. I'll come back after looking at that.

Printing from File Explorer will be fine, especially if we're saving a copy of them with the macro above as that should then be printing the mail merged documents?

Thank you. Please bear with me.

[thalantyrdsl]

I guess to expand on why we have 100 documents again, it's because we're using the mail merge feature to insert information into the documents, such as the Project ID, Project Manager, Project Company, Project Location, etc. It's really only ONE row in an XLS document, not 500 names & addresses like I believe mail merge was created for.

So the XLS document would have maybe 30 fields and the 100 documents would use those same 30 fields to insert that information into each document.

I'm not sure if there's an easier way to achieve what we're doing but it works a treat at the moment but I know that there are easier ways to automate what we're doing.

The main thing with the saving of the files is that they need to be named the same as the document is originally called. So if Document #1 is called 'Fire Sealant.docx' then I need that document to remain called 'Fire Sealant.docx' and save into that project folder, and even a sub folder, for that specific job.

[macropod]

When you say it's only one row in the Excel file, is it always the same row?

[thalantyrdsl]

Yes, that's correct. Two rows I should say. Row-1 & Row-2.

One row has the field name, the second row has the information.
For each project we change the information and then merge it into each document.

This means we don't have to edit the information (which is about 30 fields) manually for the 100 documents, but it does mean we have to finalise & merge the 100 documents individually.

[macropod]

Try the following macro. It should be saved in a new macro-enabled (docm) document in the same folder as the original mailmerge main documents. When you've created the Excel Project Data File for the project (assumed to be named 'ProjectDataFile.xlsx' for all projects), simply run the macro and let it create the 100 or so project documents with the merged output in the same folder that Excel file is in. The macro includes a folder browser, so all you need do is select the folder containing the Excel file.

With this approach, there's no need to copy all 100 or so mailmerge main documents, individually link them to the Excel file, merge to a new file, and so on. All you might need to do after running the macro is to delete any unwanted output documents for the particular project.

Sub GenerateProjectDocuments()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim strFolder As String, StrSrc As String, strPath As String, strFile As String
Dim strDocNm As String, wdDoc As Document, FlFmt As Long
strDocNm = ActiveDocument.FullName: strPath = ActiveDocument.Path
strFolder = GetFolder: If strFolder = "" Then Exit Sub
StrSrc = strFolder & "\ProjectDataFile.xlsx"
strFile = Dir(strPath & "\*.doc", vbNormal)
While strFile <> ""
  If strPath & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strPath & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      FlFmt = .SaveFormat
      With .MailMerge
        .MainDocumentType = wdFormLetters
        .Destination = wdSendToNewDocument
        .OpenDataSource Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
          Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
          "Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
          SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
        .DataSource.ActiveRecord = wdFirstRecord
        .DataSource.LastRecord = .DataSource.ActiveRecord
        .Execute Pause:=False
      End With
      .Close SaveChanges:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=strFolder & "\" & strFile, FileFormat:=FlFmt, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  End If
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.DisplayAlerts = wdAlertsAll
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm

[thalantyrdsl]

That's fantastic. Thank you. I'll give it a try now.

In terms of the deleting, that part doesn't matter as it's really only the physical printing of the documents that we pick and choose. We generally mail merge them all at the start just in case, as jobs can change mid-way.

I'll come back shortly after testing. Thank you.

[thalantyrdsl]

Ok, a few quick notes for this one.

- I grabbed x20 documents and put them in a folder.
- Assigned one of the documents (the index file) as the macro host file.
- Renamed the XLSX file to the same as was in the script just to make it easy.
- Ran the macro.

The result was that it did about x5 of the x20 files. The other ones are still looking for a link back to the original XLSX filename as that's what we linked each mail merge to.

Do I need to clear out the mail merge linking back to the original XLSX filename? If so, how do you go about clearing any link for Mail Merge?

(I think the x5 files that worked were ones that I hadn't opened, clicked on 'Use An Existing List' and then saved without actually Finalising).

The second part is in regards to the saving location, etc.

It looks like the 'merged' files are saving into the same location as the XLSX document, not the actual macro file location. So I guess the best way to make this work would be create a new folder with the 'Project Name' and drop the XLSX document into that folder. Go back into the TEMPLATE folder, run the macro, when prompted with the folder browser, point it at the new Project Folder with the XLSX document and then it should save all the documents in there?

EDIT: Sorry, you did mention the files would save to the same location as the XLSX document

As an expansion of the above, is there a way to make this all work with subfolders? The x100 files are in about 15 different folders so I'm trying to keep it as neat as possible.

Thank you again.

[macropod]

What I said you should do is to save the document with the macro to the same folder as the original Word documents; there is no need to move any of them to another folder. Only the Excel file (named 'ProjectDataFile.xlsx') needs to be in the output folder. Once the macro has been run, the output folder (i.e. the folder containing the Excel data file) will contain the merged versions of the original Word documents. Those files won't contain any merge links to any Excel file or other copies of your mailmerge main documents.

Please confirm that you're able to get the results as described when you implement the process as specified.

Yes, I can re-write the macro to work with sub-folders, but it would have been helpful had you mentioned their existence beforehand. Do you need the output files to be in sub-folders also?

[thalantyrdsl]

Thanks Paul.

Yep, I edited the post as I hadn't read what you had typed well enough. The macro works fine and as intended.

The problem I'm having is that we've previously gone into each of the x100 mail merge MAIN files and clicked on Select Recipients > Use An Existing List, then selected the XLSX file that I used to store in My Documents. Then we saved each mail merge MAIN file with that link in place.

When I ran the script, those files did not work as I'm assuming the linked list is overwriting your macro or something similar? Is there a way to delete the link from the mail merge MAIN files so that they work the same as the ones that didn't have an existing saved link to the XLSX file?

============

In regards to the subfolders, sorry for not bringing this up at the start.

Basically the folder directory is like this:

-FOLDER ROOT
-#File-Index (now with macro)
---FOLDER S1
---#Files-x10 (estimate)
---FOLDER S2
---#Files-x10 (estimate)
---FOLDER S3
---#Files-x10 (estimate)
---FOLDER S4
---#Files-x10 (estimate)
---FOLDER S5
---#Files-x10 (estimate)
---FOLDER S6
---#Files-x10 (estimate)
---FOLDER S7
---#Files-x10 (estimate)
---FOLDER S8
---#Files-x10 (estimate)
---FOLDER S9
---#Files-x10 (estimate)
---FOLDER S10
---#Files-x10 (estimate)
---FOLDER S11
---#Files-x10 (estimate)
---FOLDER S12
---#Files-x10 (estimate)

That's how the template folder is and that's how the output needs to be. Basically it would need to be a like-for-like scenario. However it's laid out in the template folder that we copy for each project would be how we'd like the structure to end up.

In order to test the macro I'm currently moving x10 files from S1 and x10 files from S2 into the ROOT directory with the macro.

Thank you, and apologies if I'm a bit vague, I'm quite new to the mail merge side.

[thalantyrdsl]

I've figured out how to remove the 'link' to the old XLSX file. They all work as intended now. The only other part would be the subfolder structure. Thank you.

EDIT: No I didn't. The macro doesn't work with the files once I turn them into a normal document unfortunately. Still need assistance here.

[macropod]

Try the following revision to the macro. I haven't addressed the sub-folder issue yet; at this stage I'm just focussing on getting all the files in the 'base' folder processed.

Sub GenerateProjectDocuments()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim strFolder As String, StrSrc As String, strPath As String, strFile As String
Dim strDocNm As String, wdDoc As Document, FlFmt As Long
strDocNm = ActiveDocument.FullName: strPath = ActiveDocument.Path
strFolder = GetFolder: If strFolder = "" Then Exit Sub
StrSrc = strFolder & "\ProjectDataFile.xlsx"
strFile = Dir(strPath & "\*.doc", vbNormal)
While strFile <> ""
  If strPath & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strPath & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      FlFmt = .SaveFormat
      If .MailMerge.Fields.Count > 0 Then
        With .MailMerge
          .MainDocumentType = wdNotAMergeDocument
          .MainDocumentType = wdCatalog
          .Destination = wdSendToNewDocument
          .OpenDataSource Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
            SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
          .DataSource.ActiveRecord = wdFirstRecord
          .DataSource.LastRecord = .DataSource.ActiveRecord
          .Execute Pause:=False
        End With
        .Close SaveChanges:=False
      End If
    End With
    With ActiveDocument
      .SaveAs2 FileName:=strFolder & "\" & strFile, FileFormat:=FlFmt, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  End If
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.DisplayAlerts = wdAlertsAll
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

[thalantyrdsl]

Hi Paul,

I moved a group of files into the same directory as the macro (updated) and ran the macro. Unfortunately none of the files processed. They were ones that were still 'linked' back to the old XLSX file.

What was the update to the macro above?

[macropod]

Why are you moving any files? That has never been part of the process I developed and described. You can't expect to get the right results if you ignore the way things are designed to work.