The below code is from a mail merge VBA and the files (excel and word) are stored on my One Drive. The work the StrWorkbookName is a URL (per the below) which has to be and use a local path, as other users need to access the file outside of my network. The code cannot find the file if I use the URL, however, if I put the local C:Users/ path address rather than the URL, it works for me but not other users (as they do not have direct access). Anyone know of a fix? Thank you.
Code
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("https://carnivalcorp-my.sharepoint.com/:w:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/HR_Email_One_Docs/Visa%20Mail%20Merge.docx")
**strWorkbookName = "https://carnivalcorp-my.sharepoint.com/:x:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/Tracker%20-%20New.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 `V$`"
With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = 1
End With
.Execute Pause:=False
End With
wd.Visible = True
wdocSource.Close SaveChanges:=False
Set wdocSource = Nothing
Set wd = Nothing
End Sub