Run-time error '5922' - Mail Merge

[dirtychinch]

Hey guys,

I've made some advancement going on what snb provided. This is my code now:

Function NewMerge()

On Error GoTo ErrHandler:


    ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls"


        With GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")                 
            .Mailmerge.Execute
            .Close 0
        End With
    
    Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")
    
'Skip error handler
Exit Function


ErrHandler:
    Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")
    MsgBox ("Run-time Error " & Err.Number)
    Exit Function
    
End Function

I changed the sub to be a function, which meant that I could call the function from elsewhere, and assign the calling macro to a button.

This code works beautifully, for the most part. Having it both open and delete the temp file is great; this wasn't possible before because I was using mailmerge.opendatasource, leaving the files connected, and as such the temp file was always in use, whilst the word document was open.

However, the problem I now have is that I really need to be able to set the data source for the mail merge to be this temp file. I had done that in Word, but because I need this to work on other people's computers and the document must be referring to my own temp file on my desktop, I cannot fully execute the merge. Does anyone know a way round this?

Edit:

I tried a different bit of code that I found online, modified as follows:

Function MergeIt()

Dim objWord As Word.Document
Set objWord = GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")


' Make Word visible.
    objWord.Application.Visible = True
    
' Set the mail merge data source.
    objWord.MailMerge.OpenDataSource _
    Name:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls", _
    LinkToSource:=True, _
    Connection:="sConnection", _
    SQLStatement:="SELECT * FROM `MailMerge`"


' Execute the mail merge.
    objWord.MailMerge.Execute


End Function

This also works and allows me to determine the data source, but then when it carries out the execution, it leaves the original word document open, and then creates the merged document. It's just a Frankenstein combo of the previous two methods...and doesn't work perfectly either!