MS Word 2010 Mailmerge to Individual Files

[JohnCNR]

Hi

I'm currently using an MS Word Mailmerge template that extracts data from MS Excel 2010 and outputs to individual files based upon the MAILMERGE FIELD values. All is well except that it processes all rows, even those that have an empty cell in column A. I only want to mailmerge the rows that have a Y in column A but so far have been unable to accomplish this.

The MS Word Macro that I'm currently using is:

Sub MergeAndSaveAsIndividualFiles()
'
' Merge and save as individual files Macro
'
'
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 = "H:\My Documents\MailMerge\"
For i = 1 To .MailMerge.DataSource.RecordCount
On Error Resume Next
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = i
.LastRecord = i
.ActiveRecord = i
If Trim(.DataFields("Review_Plan_No_Field")) = "" Then Exit For
StrName = .DataFields("Review_Plan_No_Field") & "_" & .DataFields("Plan_Issue")
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
End Sub

Column A in the spreadsheet has the Label "ACTIVE".

Any help will be appreciated

John

 

[macropod]

Your macro appears to be based on my Send Mailmerge Output to Individual Files code. See, for example:

http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

What you need to do is add a filter to the mailmerge main document, so that only records with Y in the Active field are processed.

Cheers
Paul Edstein
[MS MVP - Word]

 

[JohnCNR]

Hi Paul

Thanks for the reply, I've solved the issue by creating a new sheet in the source Excel Workbook that is automatically populated when the file is saved with all of the data from the other sheets where the cell in column A "ACTIVE" has the value "Y". I've them changed the Mailmerge source for the MS Word template to use the new sheet.

John