Opening recipient list from Mail-Merge, for colleagues with different path, in Microsoft Teams folder

We have a mail-merge set-up between an Excel document and Word document. I saved the documents in the same folder on a Synced Teams folder for everyone to access.

If I open the Word document, I can click Yes on the SQL command and it opens.
For my colleagues it prompts to find the recipient list every time they open the Word file. They even have to choose the Excel file twice in a row before it connects.

Is there is a way I can specify for Word which file it should always use, that also works for my colleagues?

The location of the folder is different for every colleague due to the path location always starting with "C:\Users\username" for synced Teams folders.

Private Sub Document_Open()
    
    Dim fs As Object
    Dim filename As String
    
    ' Create a FileSystemObject
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    ' Set the path to the folder containing the data source file
    Dim folderPath As String
    folderPath = ThisDocument.Path
    
    ' Look for the data source file in the folder
    Dim file As Object
    For Each file In fs.GetFolder(folderPath).Files
        If file.Name Like "*General Template.xlsx" Then
            filename = file.Path
            Exit For
        End If
    Next file
    
    If filename = "" Then
        MsgBox "Could not find the data source file.", vbExclamation, "Error"
        Exit Sub
    End If
    
    ' Use the file path in the Mail Merge
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:=filename, _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & filename & _
        ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1""", _
        SQLStatement:="SELECT * FROM `General$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
    
End Sub

Solution

So with some help from the internet and from jonsson I have managed to get together a code that solves this issue. The environ 2 function gets the local file path which can differ from user to user and with the replace function you can remove and replace any unwanted parts of this path. At the 'XXX' specify the rest of your path location after the username. Also do not forget to replace the 'General Template.xlsx' to your file name and the SQLStatement to your Excel Tab name:

Private Sub Document_Open()

Dim fs As Object
Dim filename As String

' Create a FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")

' Set the path to the folder containing the data source file
Dim folderPath As String
folderPath = Replace(Replace(Environ(2), "APPDATA=", ""), "AppData\Roaming", "XXX")

' Look for the data source file in the folder
Dim file As Object
For Each file In fs.GetFolder(folderPath).Files
    If file.Name Like "*General Template.xlsx" Then
        filename = file.ShortPath
        Exit For
    End If
Next file

If filename = "" Then
    MsgBox "Could not find the data source file.", vbExclamation, "Error"
    Exit Sub
End If

' Use the short file path in the Mail Merge
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=filename, _
    SQLStatement:="SELECT * FROM `General$`"

ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle 
    
End Sub

To make sure this still works when saving as template, add the exact same code in Private Sub Document_New() and now it will trigger the event everytime the template is opened!

Thanks a lot jonsson for guiding me in the right direction to solve the issue!