Call Word document (with mergefields ready) and MailMerge it from Excel (all hosted on Sharepoint)

[herman925]

I have to setup a series of mail merged forms for my school to invite guest speakers to our classrooms. For that, I have setup a table called Guest_Speaker_MM.
I've already created all the merge fields and tested them using manual mail merge and they worked fine. However, when these files are shared via Sharepoint (One Drive), the mail merge always unlinks even when I made sure the filepaths are identical on all other computers.
In frustration I looked for VBA to help me point to the proper path when I press a button on Excel.
These are the things I have so far but I am unable to achieve anything since I'm just a beginner of VBA

Private Sub ReminderButton_Click()
Dim WordObject As Object
Dim DocSource As Object
Dim RealWorkbookPath As String

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

Set DocSource = WordObject.Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\05 - Reminder of Collection & Payment needed for Invited Guest Speaker.docx")
RealWorkbookPath = ThisWorkbook.Path & "/" & ThisWorkbook.Name

DocSource.MailMerge.MainDocumentType = wdFormLetters

DocSource.MailMerge.OpenDataSource _
Name:=RealWorkbookPath, _
Connection:="Data Source=" & RealWorkbookPath & ";Mode=Read", _
SQLStatement:="SELECT * FROM 'Guest Speakers$' where Status = Signed"

Unload Me
End Sub

I need to achieve these functions:

• When a button is pressed, automatically open the specified word document (it's in a sub-folder of the workbook's path)
• Execute the MailMerge function, with the WorkSheet Name being Guest Speakers and the Table Name is Guest_Speaker_MM
• Apply a filter, where Column Header Status = Reminder Needed (I have no idea how to execute SQL Commands here)

Please help me

 

[herman925]

Getting something like this now, which is in similar structure i suppose, but not working with error 91: "Object Variable or With block variable not set"

Private Sub CommandButton1_Click()
    Dim WordApp As Word.Application
    Dim OpenDocument As Word.Document
    Dim wordMailMerge As Word.Mailmerge
    Dim wordMergeFields As Word.MailMergeDataFields
    Dim wordPath As String
    Dim excelPath As String
    
    WordApp.DisplayAlerts = wdAlertsNone
    
    CurrentWorksheet = ActiveSheet.Name
    excelPath = ThisWorkbook.FullName

                wordPath = ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx"
                Set WordApp = CreateObject("Word.Application")
                Set OpenDocument = WordApp.Documents.Open(wordPath)
                Set wordMailMerge = OpenDocument.Mailmerge
                
                wordMailMerge.OpenDataSource Name:=excelPath, SQLStatement:="SELECT * FROM `'Guest Speakers$'` where `Status`= 'Reminder Needed'"
                wordMailMerge.Execute
                OpenDocument.Close
                WordApp.Visible = True
        
    Set wordMailMergeFields = Nothing
    Set wordMailMerge = Nothing
    Set OpenDocument = Nothing
    Set WordApp = Nothing
    WordApp.DisplayAlerts = wdAlertsAll
    
    Sheets(CurrentWorksheet).Select
End Sub