VBA Error - Mail Merge

[CarlUK]

For the life of me, I cannot fix this run time error-5273 (document name or path is not valid). on the code. Can someone with better eyes tell me what the issue is with the code below? Thank you.


Sub RunMerge()<br>
<br>
<br>
Dim wd As Object<br>
Dim wdocSource As Object<br>
<br>
<br>
Dim strWorkbookName As String<br>
<br>
<br>
On Error Resume Next<br>
Set wd = GetObject(, "Word.Application")<br>
If wd Is Nothing Then<br>
Set wd = CreateObject("Word.Application")<br>
End If<br>
On Error GoTo 0<br>
<br>
<br>
Set wdocSource = wd.Documents.Open("C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\HR_Email_One_Docs\Visa Mail Merge.docx")<br>
<br>
<br>
strWorkbookName = ThisWorkbook.Path &amp; "\" &amp; ThisWorkbook.Name &amp; "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"<br>
<br>
<br>
wdocSource.MailMerge.MainDocumentType = wdFormLetters<br>
<br>
<br>
wdocSource.MailMerge.OpenDataSource _<br>
Name:=strWorkbookName, _<br>
AddToRecentFiles:=False, _<br>
Revert:=False, _<br>
Format:=wdOpenFormatAuto, _<br>
Connection:="Data Source=" &amp; strWorkbookName &amp; ";Mode=Read", _<br>
SQLStatement:="SELECT * FROM `Visa$`"<br>
<br>
<br>
With wdocSource.MailMerge<br>
.Destination = wdSendToNewDocument<br>
.SuppressBlankLines = True<br>
With .DataSource<br>
.FirstRecord = wdDefaultFirstRecord<br>
.LastRecord = wdDefaultLastRecord<br>
End With<br>
.Execute Pause:=False<br>
End With<br>
<br>
<br>
wd.Visible = True<br>
wdocSource.Close SaveChanges:=False<br>
<br>
<br>
Set wdocSource = Nothing<br>
Set wd = Nothing<br>
<br>
<br>
End Sub<br>

[Paul_Hossler]

Once I formatted it to remove the HTML (below) I suspect it's this line




strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"








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 & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"




wdocSource.MailMerge.MainDocumentType = wdFormLetters




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




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

[CarlUK]

thanks Paul. Sadly it did not work. I think the error is due to this line, so it look correct to you?

strWorkbookName = ThisWorkbook.Path & "" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"

Once I formatted it to remove the HTML (below) I suspect it's this line




strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"








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 & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"




wdocSource.MailMerge.MainDocumentType = wdFormLetters




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




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

[Paul_Hossler]

No need to quote the entire message

I'm guessing that it should be something like one of these??





strWorkbookName = "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"


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


strWorkbookName = ThisWorkbook.Fullname

[CarlUK]

Thank you Paul. The first one worked a treat. Appreciate your time.