1

The below code is from a mail merge VBA and file is stored on my One Drive, and the work the StrWorkbookName is a URL (per the below), however, the code cannot find the file, however, if I put the 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.

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
CarlS
  • 23
  • 5
  • 4
    One line of code doesn't give us much idea of what you mean by "doesn't work", but maybe check here: https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive – Tim Williams Dec 03 '21 at 01:53
  • Full code added Tim. Thank you. – CarlS Dec 06 '21 at 16:38
  • What's the exact error message you get? – Tim Williams Dec 06 '21 at 16:45
  • Why not use the "local" path (eg. C:\Users\userNameHere\OneDrive) for `strWorkbookName` (assuming it's not an online-only file)? – Tim Williams Dec 06 '21 at 16:47
  • The local path does not work for other people outside of my network, and why I think it needs to be the URL....I am fairly newish to this, so I could be wrong. – CarlS Dec 06 '21 at 22:28
  • OK I see. In that case you may need to have your code save a temporary local copy of the workbook and query that instead of the copy on your OneDrive ? – Tim Williams Dec 06 '21 at 22:31
  • It is weird how the Set wd.Doc Source = wd.Documents code line is able to open the word doc through a URL but the strWorkBookName command does not. – CarlS Dec 06 '21 at 22:39

0 Answers0