Mailmerge Selected Range to Individual PDFs

[N Prakash]

Try:

Sub merge1record_at_a_time()
Application.ScreenUpdating = False
Dim StrPath As String, StrName As String, MainDoc As Document
StrPath = "C:\File path\"
Application.ScreenUpdating = False
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrName = .DataFields("ID")
      End With
      .Execute Pause:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Naturally, you'll need to use your own path for the StrPath variable and, if you're not saving docx files, change the '.SaveAs2' line to:

      .ExportAsFixedFormat OutputFileName:=StrPath & StrName & ".pdf", ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, CreateBookmarks:=wdExportCreateNoBookmarks, _
        KeepIRM:=True, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False

Dear Macropad,

I used the above code to generate .pdf file and it is working perfectly well. For my testing I created 10 .pdf files and it worked as expected. But this has also created 37 word document I do not know how but I need to delete each word file one by one and it is not even allowing me to close the word application. Then from Task manager I had to select all word files and kill the process. Will you please help me the word document created should get closed without intervention and my .pdf files alone should be in the folder.

Regards,

Prakash

 

[Macropod]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

Oops! Change:
If .DataFields("Sl_No") = strRcrd Then
to:
If StrName = strRcrd Then

 

[N Prakash]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

Thank you so much Mr. Paul Edstein.

At present it is generating single file but I need range of files say 11 to 20. Input screen should accept the range of Sl_No to print. Will you please give me the modified code. Thanks in advance.

Regards,
Prakash

 

[Macropod]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

Try:

Sub MergeRecordSeries()
Application.ScreenUpdating = False
Dim StrPath As String, StrName As String, MainDoc As Document, strRcrd As String, i As Long, bRslt As Boolean
StrPath = "C:\File path\"
strRcrd = Trim(InputBox("Which Sl Range to merge (e.g. 11-20)?"))
If strRcrd = "" Then Exit Sub
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrName = .DataFields("ID")
      End With
      bRslt = False
      If StrName >= Split(strRcrd, "-")(LBound(Split(strRcrd, "-"))) Then
        If StrName <= Split(strRcrd, "-")(UBound(Split(strRcrd, "-"))) Then
        .Execute Pause:=False
        bRslt = True
        End If
      End If
    End With
    If bRslt = True Then
      With ActiveDocument
        .SaveAs FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    End If
  Next i
End With
Application.ScreenUpdating = True
End Sub

 

[N Prakash]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

Thanks for your immediate response.

Now it is prompting to input the range but files are not getting generated. I am not seeing any reference to data field Sl_No which is one of the data fields in my excel file. No error is also returning.

 

[Macropod]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

What does your "ID" field actually contain - just a number, or something else? How are you inputting the range - 11-20, for example, or with the full start & end IDs (if they're more than just a number) separated by hyphens?

 

[N Prakash]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

In Excel file, Sl_NO is a column heading in A column where on entering the data in C column the next serial number populates. I have put a formula =IF(C17<>"",A16+1,"") in column A. Each row contains data and PDF has to be created getting data from each row on selection of range of SL_No from A column. Say 11 - 15 means 5 row data is to be populated in the .pdf file created.

 

[Macropod]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

If it's a field named 'Sl_No' that you're testing, try:

Sub MergeRecordSeries()
Application.ScreenUpdating = False
Dim StrPath As String, StrID As String, MainDoc As Document, StrRcrd As String, StrRcrdRng As String, i As Long, bRslt As Boolean
StrPath = "C:\File path\"
StrRcrdRng = Trim(InputBox("Which Sl Range to merge (e.g. 11-20)?"))
If StrRcrdRng = "" Then Exit Sub
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrID = .DataFields("ID")
        StrRcrd = .DataFields("Sl_No")
      End With
      bRslt = False
      If StrRcrd >= Split(StrRcrdRng, "-")(LBound(Split(StrRcrdRng, "-"))) Then
        If StrRcrd <= Split(StrRcrdRng, "-")(UBound(Split(StrRcrdRng, "-"))) Then
        .Execute Pause:=False
        bRslt = True
        End If
      End If
    End With
    If bRslt = True Then
      With ActiveDocument
        .SaveAs FileName:=StrPath & StrID & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    End If
  Next i
End With
Application.ScreenUpdating = True
End Sub

I can't really tell from what you've posted what the correct field name is, as you keep referring to it in different ways:
• Sl No.
• Sl.No
• sl. no
• Sl_No
• Sl_NO
• SL_No

 

[N Prakash]

Re: Word 2007/2010 Mail Merge to save to individual PDF files

Thanks a lot Mr.Paul Edstein. It met my requirements. Field name is Sl_No and it is working well.

Regards,
Prakash