MailMerge Separation FileNaming

[thomasfidy]

I am having an issue completing a VBA macro. I am building MailMerge Document and I want the filename, when it saves, to be the first line or paragraph of the document. When I attempt to use oDoc.Paragraphs(1) or ActiveDocumentParagraphs(1) I get a type matching error. If I use ActiveDocumentWords(1) Active DocumentsWords(2) it fails if the amount of words in the doc don't match. So if a company name is 2 words great, if it's one word there is an error.

I am trying to find a way to do this effectively so any help or insight would be much appreciated. I am not a VBA master by any means, but I understand the logic enough to work through it if given some guidance around possible options.

The code I have pieced together is below:


Sub Splitter()
' Based on a Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.

Dim Mask As String

Dim Letters As Long
Dim Counter As Long
Dim DocName As String
Dim oDoc As Document
Dim oNewDoc As Document
ChangeFileOpenDirectory "Z:\MailMerge\Separated\"
Options.DefaultFilePath(wdDocumentsPath) = CurDir
Set oDoc = ActiveDocument

oDoc.save
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Mask = "MMDDYYY"
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
DocName = "Z:\MailMerge\Separated\" & Activedocument.Words(1) & " " & Activedocument.words(2) & ".doc"
oDoc.save
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.Add

With Selection
.PasteAndFormat (wdFormatOriginalFormatting)
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ChangeFileOpenDirectory "Z:\MailMerge\Separated\"
oNewDoc.saveas FileName:=DocName, FileFormat:=wdFormatDocument, AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Wend

oDoc.Close wdDoNotSaveChanges
End Sub

[macropod]

Try changing:
DocName = "Z:\MailMerge\Separated\" & Activedocument.Words(1) & " " & Activedocument.words(2) & ".doc"
to:
DocName = "Z:\MailMerge\Separated\" & Split(oDoc.Paragraphs.First.Range.Text, vbCr)(0) & ".doc"

Do note that, should the nominated name contain characters that are illegal in a filename (e.g. "*./\:?|), the save will still fail.

A better approach is to actually generate the individual documents as part of the mailmerge process. To do that, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks threads at:
http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
&:
http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
Those threads also contain my own macro for splitting mailmerge output (see under Split Merged Output to Separate Documents).

[thomasfidy]

Try changing:
DocName = "Z:\MailMerge\Separated\" & Activedocument.Words(1) & " " & Activedocument.words(2) & ".doc"
to:
DocName = "Z:\MailMerge\Separated\" & Split(oDoc.Paragraphs.First.Range.Text, vbCr) & ".doc"

Do note that, should the nominated name contain characters that are illegal in a filename (e.g. "*./\:?|), the save will still fail.


DocName = "Z:\MailMerge\Separated\" & Split(oDoc.Paragraphs.First.Range.Text, vbCr) & ".doc" gave me a mismatch error, so I switched it to DocName = "Z:\MailMerge\Separated\" & oDoc.Paragraphs.First.Range.Text & ".doc" which gives me the correct value for the filename, however it gives me a Run-time Error '5096' with the company name in brackets e.g. Run-time Error '5096' (Northrup) or Run-time Error '5096' (Frank's Franks).

Getting closer. Unfortunately, I am not able to install the add-on software you mentioned. We have very strict compliance policies at work, so although I'm sure it would make it ridiculously easy, I have to do it the semi-old fashioned way.

[macropod]

DocName = "Z:\MailMerge\Separated\" & Split(oDoc.Paragraphs.First.Range.Text, vbCr) & ".doc" gave me a mismatch error
My bad - there was a (0) missing from the code. I've updated the post. Try it now.

I am not able to install the add-on software you mentioned.
I made no mention of an addin - what I pointed you to is a set of macros, just like what you're already using.

[thomasfidy]

My bad - there was a (0) missing from the code. I've updated the post. Try it now.

I made no mention of an addin - what I pointed you to is a set of macros, just like what you're already using.

I apologize, I saw the addin link at the top of the page and just assumed the whole thread was a reference to that. I actually found this macro in the thread you sent, but I can't figure out exactly how it works. My best guess from reading the code is that it is supposed to execute when you run a MailMerge? However, when I tried it nothing happened. Once the MailMerge is executed the file wouldn't have the form fields that the macro would reference, but this would be the best case scenario for what I am trying to do if you have any insight. I appreciate you getting back to me so quickly macropod.


Sub Merge_To_Individual_Files()
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
StrFolder = .Path & Application.PathSeparator
For i = 1 To .MailMerge.DataSource.RecordCount
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = i
.LastRecord = i
.ActiveRecord = i
If Trim(.DataFields("Last_Name")) = "" Then Exit For
'StrFolder = .DataFields("Folder") & Application.PathSeparator
StrName = .DataFields("Buyer_Name") & "_" & .DataFields("Date_")
End With
.Execute Pause:=False
End With
For j = 1 To Len(StrNoChr)
StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
Next
StrName = Trim(StrName)
With ActiveDocument
.SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
.Close SaveChanges:=False
End With
Next i
End With
Application.ScreenUpdating = True

[macropod]

I apologize, I saw the addin link at the top of the page and just assumed the whole thread was a reference to that.
I have no idea what you mean by that. Nothing in this thread or either of the links I gave you refers to an addin.


I actually found this macro in the thread you sent, but I can't figure out exactly how it works. My best guess from reading the code is that it is supposed to execute when you run a MailMerge? However, when I tried it nothing happened.
You would have to add the Merge_To_Individual_Files macro to your mailmerge main document and run it from there. You can even make it run without having to use Developer|Macros or Alt-F8 just by changing its name to MailMergeToDoc. It would then run when you click on Finish & Merge>Edit individual documents. Obviously, you'll also need to adjust the field references on the:
StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
line to suit the field names in your mailmerge data source that you want to use for the filenames.


Once the MailMerge is executed the file wouldn't have the form fields that the macro would reference, but this would be the best case scenario for what I am trying to do if you have any insight.
Now you're introducing formfields into the mix. Formfields don't survive a mailmerge (content controls do, however). So, if your existing merged output documents have formfields in them, you must already be using some other process (probably an addin) to achieve that and this whole discussion has been pointless.