Word VBA code for Mail Merge export

[Dawn1985]

Hello, I am new to Word VBA and am having trouble with a mail merge code.
Everything works great on the code with the exception of where the files are being saved/exported to.

for this line: Const FOLDER_SAVED As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test"
No matter what directory I input it always automatically saves to My documents on the C drive.
Any help with this would be greatly appreciated!




Option Explicit
Const FOLDER_SAVED As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test\"
Const SOURCE_FILE_PATH As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test\Security Assessment - Positive Result.docx"
Sub TestRun()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument
With MainDoc.MailMerge




.OpenDataSource Name:="\\DAVINCI-1\USERDATA\carla\Desktop\Test\Internal Controlled Goods Security Assesment.xlsx", sqlstatement:="SELECT * FROM [Letter List$]"

totalRecord = .DataSource.RecordCount
For recordNumber = 1 To totalRecord
With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
End With
.Destination = wdSendToNewDocument
.Execute False
Set TargetDoc = ActiveDocument
TargetDoc.ExportAsFixedFormat OutputFileName:=FOLDER_SAVED & .DataSource.DataFields("First_Name").Value & "_" & .DataSource.DataFields("Last_Name").Value & ".pdf", exportformat:=wdExportFormatPDF
TargetDoc.Close False
Set TargetDoc = Nothing
Next recordNumber
End With
Set MainDoc = Nothing
End Sub

[gmayor]

Try the following instead:

Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 16 Jul 2021
Sub TestRun()
Dim FOLDER_SAVED As String
Dim SOURCE_FILE_PATH As String
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long
Dim FSO As Object

FOLDER_SAVED = Environ("USERPROFILE") & "\Desktop\Test\"
SOURCE_FILE_PATH = FOLDER_SAVED & "Security Assessment - Positive Result.docx"

Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(FOLDER_SAVED) = False Then
MsgBox "The folder '" & FOLDER_SAVED & "' does not exist!", vbCritical
Set FSO = Nothing
Exit Sub
End If

Set MainDoc = ActiveDocument
With MainDoc.MailMerge

.OpenDataSource _
Name:=FOLDER_SAVED & "Internal Controlled Goods Security Assesment.xlsx", _
sqlstatement:="SELECT * FROM [Letter List$]"

totalRecord = .DataSource.RecordCount
For recordNumber = 1 To totalRecord
With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
End With
.Destination = wdSendToNewDocument
.Execute False
Set TargetDoc = ActiveDocument
TargetDoc.ExportAsFixedFormat _
OutputFileName:=FOLDER_SAVED & _
.DataSource.DataFields("First_Name").value & "_" & _
.DataSource.DataFields("Last_Name").value & ".pdf", _
exportformat:=wdExportFormatPDF
TargetDoc.Close False
Set TargetDoc = Nothing
Next recordNumber
End With
Set MainDoc = Nothing
End Sub

See also https://www.gmayor.com/MergeAndSplit.htm

[Dawn1985]

Try the following instead:

Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 16 Jul 2021
Sub TestRun()
Dim FOLDER_SAVED As String
Dim SOURCE_FILE_PATH As String
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long
Dim FSO As Object

FOLDER_SAVED = Environ("USERPROFILE") & "\Desktop\Test\"
SOURCE_FILE_PATH = FOLDER_SAVED & "Security Assessment - Positive Result.docx"

Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(FOLDER_SAVED) = False Then
MsgBox "The folder '" & FOLDER_SAVED & "' does not exist!", vbCritical
Set FSO = Nothing
Exit Sub
End If

Set MainDoc = ActiveDocument
With MainDoc.MailMerge

.OpenDataSource _
Name:=FOLDER_SAVED & "Internal Controlled Goods Security Assesment.xlsx", _
sqlstatement:="SELECT * FROM [Letter List$]"

totalRecord = .DataSource.RecordCount
For recordNumber = 1 To totalRecord
With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
End With
.Destination = wdSendToNewDocument
.Execute False
Set TargetDoc = ActiveDocument
TargetDoc.ExportAsFixedFormat _
OutputFileName:=FOLDER_SAVED & _
.DataSource.DataFields("First_Name").value & "_" & _
.DataSource.DataFields("Last_Name").value & ".pdf", _
exportformat:=wdExportFormatPDF
TargetDoc.Close False
Set TargetDoc = Nothing
Next recordNumber
End With
Set MainDoc = Nothing
End Sub

See also https://www.gmayor.com/MergeAndSplit.htm


Thank you very much! I made some modifications because I intended to move the files to a shared drive but still worked perfectly!

Thank you again! :joy: