Mail Merge - Doc Save Name

[jwilson]

The KB article "Automate saving the new document produced during a mail merge" was very helpful.
Is there any way to pick-up the save name for the document from the data file (e.g. "data.txt")? The desired save directory and file name is contained in the data file (data.txt).
Thanks.

[fumei]

Open the data.txt file and get it.

[jwilson]

Thanks. The code opens a "save box" when the automatic merge takes place and shows a default file. How can I get the default file to be the file name that is included as a field in the .txt (data) file? The .txt file contains the fields "DocumentName" and "DocumentFolder".

The current VBA code is:

'//In ThisDocument module

Private Sub Document_Open()
DocSave "Correspondence"
End Sub

'DocName is the InputBox default if approriate for a standard letter etc.
Sub DocSave(DocName)

Dim Rec As Integer
Dim ToBeSaved As String, MergeFileName As String, JobNo As String, Direct As String
Dim Project As String, SaveName As String, SaveAsName As String
Dim Length As Integer
Dim Message As String, Title As String, Default As String, MyValue As String
Dim DataF As Variant, DRecord As String

'Set Drive\Folder to save to
Const SaveRoot = "C:\ERMA\"

'Close custom toolbar, delete if not required
On Error Resume Next
CommandBars("Merge File").Visible = False

'Input box for filename
Message = "Enter Sub-Folder/Filename for saving" & Chr(13) & _
"NB - New sub-folders will not be created" & Chr(13) & Chr(13) & _
"Press Cancel to proceed without saving." ' Set prompt.
Title = "Document File Name" ' Set title.

'Create name for document
SaveName = InputBox(Message, Title, DocName)

'Remember name to close it later
MergeFileName = ActiveDocument.Name

'Skip process if save not required
If SaveName = "" Then
ToBeSaved = "No"
Goto MailMergeLine
End If


'Close standard toolbars if visible
On Error Resume Next
CommandBars("MailMerge").Visible = False
On Error Resume Next
CommandBars("Web").Visible = False

'Obtain SavePath data from first two merge fields
Rec = 0
For Each DataF In _
Documents(MergeFileName).MailMerge.DataSource.DataFields
Rec = Rec + 1
DRecord = DataF.Value
If Rec = 1 Then JobNo = DRecord
If Rec = 2 Then Project = DRecord
If Rec = 3 Then Goto MailMergeLine
Next DataF

'Create new merged document
MailMergeLine:
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

'Go to end if save not required
If ToBeSaved = "No" Then Goto LastLine

'Create SavePath from obtained data
Direct = SaveRoot & JobNo & " " & Project & "\"

'Trim file extension if entered in input box
Length = Len(SaveName)
If Right(SaveName, 4) = ".doc" Then
SaveName = Left(SaveName, Length - 4)
End If

'Create name for file
SaveAsName = SaveName & ".doc"

'Open Directory for saving
ChangeFileOpenDirectory Direct

'Check for previous instances of the SaveName, if found add incremental suffix
With Application.FileSearch
.NewSearch
.LookIn = Direct
.SearchSubFolders = False
.FileName = SaveName & "*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
SaveAsName = SaveName & "-" & .FoundFiles.Count & ".doc"
End If
End With

'ChangeFileOpenDirectory Direct
ActiveDocument.SaveAs FileName:=SaveAsName

'Confirm path and filename used

[TonyJollans]

I haven't studied the code but are you saying you have the save file name in the Merge data? If so, is it duplicated on every record and do you want to save each merge result as a separate document, or what?

[jwilson]

Yes. Each Merge would be saved as a separate document and every record has the save filename and path included in it. Thanks.

[TonyJollans]

I'm not a mail merge expert but, depending on your version of Word (I think it came in 2002), you should be able to run arbitrary code in the MailMergeAfterMerge event - and in that code you could save each merge result and pick up merge field results and use them for any purpose, including generating a filename.

If I have time I'll try and knock up an example later.

[jwilson]

Thanks. I appreciate the help...a mock-up would be a real life-saver.

[TonyJollans]

Here's the best I can do at the moment.

Unzip the attached to a folder. It contains a template - MMSave.dot - and a simple merge source - SimpleSource.dot.

Before running anything, edit the data source and, if nothing else, change the file path, on each record, to one of your own, and make sure it ends with a backslash.

Double click on the template and it will open a new document, attach the source, run the merge, saving each merged record to the file name supplied in the source, give you a message and close the document.

To look at what it does, right click the template and open it. I'll give you some more details of it later but I'm off to bed now.

[jwilson]

Thanks Tony. This is great you have saved me a lot of grief.

[TonyJollans]

A little more explanation. The merge is set up with an extra paragraph at the beginning, containing the two merge fields that make up the full document name.

Using the MailMergeAfterRecordMerge event which fires after each merged record, the section break added by Word at the end of the record is removed, the extra first paragraph is extracted and removed, the resulting document saved as the supplied name, and then all content is removed from the merge result document ready for the next record to be merged, ... except that ...

... deleting document content in the MailMergeAfterRecordMerge event means that at the end of the merge there is nothing left and Word throws an error. I got round this by deleting the content in the MailMergeBeforeRecordMerge event; first time through this does nothing, all other times there really isn't any difference doing something after one record or before the next, and at the end the last record merged is still in the document so Word is happy. As a side note, this isn't a problem in Word 2007 but when I switched to 2002 I found it.

Finally in the MailMergeAfterMerge event, the temporary documents are tidied up.

The whole thing is driven from some trivial code in the Document_New event. The template contains the merge skeleton in its body and this is copied to any new document based on it. The merge source is attached to the document as part of the code, so the template is not itself merge-ready.

I hope you can change the bits you need to, to use your own data. If not, do come back.

[jwilson]

Thanks again Tony. I made the changes to match my files and it works great. If I wanted to keep the merged Word document open after the merge, what would I need to change. Generally, there will only be one Word Doc from each merge. Thanks.

[TonyJollans]

There are two Document.Close statements in the AfterMerge event - the first should close the merge result, the second the merge skeleton. Comment them both out.