VBA Mail Merge Error

[CarlStephens]

Hello All,

I have the below code for simply producing a mail merge and the code is erroring out on the bold text below and I cannot see what is causing the issue. The merge fields have been set up and I think that I have linked the word document correctly as well as the sheet (name Visass). What would I need to add to the code to be able to print to pdf after the merge and save to a specific location? Thank you for your anticipated help.

Sub RunMerge()

Dim wd As Object
Dim wdocSource As Object

Dim strWorkbookName As String

On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdocSource = wd.Documents.Open("C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\HR_Email_One_Docs\Visa Mail Merge.docx")

strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Visass$`"

With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

wd.Visible = True
wdocSource.Close SaveChanges:=False

Set wdocSource = Nothing
Set wd = Nothing

End Sub

 

[larryjfoster]

Try replacing Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
with Connection:="Data Source=strWorkbookName;Mode=Read", _