How to Save File with 2 different criteria - mail merge - individual documents vba

[ahdiethya]

Hii,
I am trying to automate mail merge to save each record individually in PDF output using VBA, but when i try to save file with 2 different criteria (FSName = .DataFields("Nomor Polis").Value & " - " & .DataFields("Nama Tertanggung").Value) it won't work.


Option Explicit
Const FOLDER_SAVED As String = "C:\Users\adyudh\Desktop\Testing eSertifikat\BPR Group\Output"
Const SOURCE_FILE_PATH As String = "C:\Users\adyudh\Desktop\Testing eSertifikat\BPR Group\Testing - Copy.xlsx"

Sub MailMergeToPDF()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String, FSName As String
Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument
With MainDoc.MailMerge

.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT*FROM [Production$]"

totalRecord = .DataSource.RecordCount

For recordNumber = 1 To totalRecord

With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber

FSName = .DataSource.DataFields("Nomor Polis").Value & " - " & .DataSource.DataFields("Nama Tertanggung").Value

End With

.Destination = wdSendToNewDocument
.Execute False

Set TargetDoc = ActiveDocument

TargetDoc.SaveAs FOLDER_SAVED & FSName & ".docx", wdFormatDocumentDefault
TargetDoc.ExportAsFixedFormat FOLDER_SAVED & FSName & ".pdf", exportformat:=wdExportFormatPDF
TargetDoc.Close False
Set TargetDoc = Nothing
Next recordNumber
End With
On Error Resume Next
Kill FOLDER_SAVED & "*.docx"
On Error GoTo 0
Set MainDoc = Nothing


End Sub

[Charles Kenyon]

I moved your post from the general Word forum to the Word vba forum.

[Guessed]

The FSName line is still inside the With .DataSource so you are saying .DataSource.DataSource.DataFields("Nomor Polis").Value

Try removing the red text from this chunk of your code

With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
FSName = .DataSource.DataFields("Nomor Polis").Value & " - " & .DataSource.DataFields("Nama Tertanggung").Value
End With

[macropod]

ahdiethya: Kindly don't ask the same question in multiple threads. You also posted here: https://www.msofficeforums.com/172565-post13.html