View original (French)

Mail merge with separate PDF record

Solved
Marie7831 -  
Hello everyone,

I'm desperately looking for a macro that can save each sheet of a mail merge as a separate PDF, using a mail merge field name (<<City>>) as the file name.
I've tried several macros but have never succeeded in getting what I need.
If anyone has an idea :)

Thank you in advance,

have a great day,

Marie

3 answers

  1.  yg_be Contributor
    Hello, can you tell us more?
    2
    1. Marie7831 Author
      Hello :)

      Actually, I created a letter on Word that I would like to send to several addresses (different cities).
      When I perform the mail merge, it creates the usual number of records based on the number of rows in my Excel data source.
      I would then like to save each letter as a PDF, separately from the others, naming the PDF file with one of the merge fields, namely the city.
      I'm not sure if I'm being clear in my explanation :)
      -1
    2. yg_be Contributor
      @Marie7831You've tested several macros: where is it getting stuck? Is it the saving of each page, the filename, ...?
      Just in case: 
      ActiveDocument.ExportAsFixedFormat OutputFileName:= _ "D:StuffBusinessTempPDFName.pdf", _ ExportFormat:=wdExportFormatPDF, _ Range:=wdExportFromTo, From:=1, To:=1
      0
    3. Marie7831 Author
      Execution error 5852: The requested object is not available.
      And in debug mode, this is what blocks first:
      ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord

      Here is the macro:
      Sub SavePubliAsPDF()
      Dim LastRec As Integer
      Dim Path As String, Id As String

      Application.ScreenUpdating = False

      'Choice of the folder for saving files
      With Application.FileDialog(msoFileDialogFolderPicker)
      .Title = "Select a folder to save your files"
      .Show
      If Not (.SelectedItems.Count = 0) Then
      Path = .SelectedItems(1)
      Else
      Exit Sub
      End If
      End With

      'Counting the number of records in the mail merge
      ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord
      LastRec = ActiveDocument.MailMerge.DataSource.ActiveRecord
      ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord

      'Saving the files
      ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
      For i = 1 To LastRec Step 1
      Id = ActiveDocument.MailMerge.DataSource.DataFields(1).Value
      ActiveDocument.SaveAs2 Path & "\Letter " & Id & ".pdf", wdFormatPDF
      ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
      Next i

      MsgBox "The saving of your mail merge is complete." & vbLf & vbLf & LastRec & " files have been saved in the folder: " & Path, vbOKOnly + vbInformation, "Mail merge saving complete"

      Application.ScreenUpdating = True
      End Sub
      0
    4. yg_be Contributor
      @Marie7831Did you make sure to do the mail merge before running the macro?
      0
    5. Marie7831 Author
      Yes, I did the mail merge correctly, I have all the Word sheets that follow one another.
      I went through the mail merge wizard, could that be the source of the issue?
      0