Excel Mail Merge - Runtime error 429

[dirtychinchilla]

Hi guys,

So ages ago some helpful chaps here helped me work on this code, which gives users a button in Excel that they can press to transer a quote automatically into a word document via a mail merge. This has been working beautifully for ages, but one user (new) is now getting runtime error 429 when he reaches the "With CreateObject" line. Could anyone give me an insight into what's going wrong?? I'd really appreciate it.

Thanks in advance,

Jonathon

Sub FSMerge()

    Dim objDoc As String, B As Integer
    
    objDoc = "Q:\Temp\FSTemp.xls"
     
On Error GoTo ErrHandler:

    If MsgBox("Do you want to continue with the mail merge? If nothing happens, check whether a dialog box has opened in the background, or wait 30 seconds and try again. When the dialog box opens, please press yes to accept the mail merge.", vbYesNo, "Confirm") = vbNo Then
        Exit Sub
    End If
    
    ThisWorkbook.SaveCopyAs Filename:=objDoc
    
    With CreateObject("Q:\Index\Documents\Quotation.dotm")
        DoEvents
        .Application.Visible = True
        .MailMerge.Execute
        .Close 0
    End With

    Kill objDoc
     
     'Skip error handler
    Exit Sub
          
ErrHandler:
    Kill objDoc
    MsgBox ("Run-time Error " & Err.Number)
    Exit Sub

End Sub

 

[dirtychinchilla]

That's wrong, apologies. One thing you'd missed out before is this saving of a temporary file, so I've added it in giving this, which gives error 4605.

Sub FSMerge()

Dim wrdObj As Object
Dim wrdDoc As Object
Dim objDoc As String, B As Integer

objDoc = "Q:\Temp\FSTemp.xls"
     
On Error GoTo ErrHandler:

    Set wrdObj = CreateObject("Word.Application")

    wrdObj.Visible = True

    ThisWorkbook.SaveCopyAs Filename:=objDoc
    
    Set wrdDoc = wrdObj.Documents.Add(Template:="Q:\Index\Documents\Quotation.dotm")

    wrdDoc.MailMerge.Execute

    wrdDoc.Close 0

    Kill objDoc
     
     'Skip error handler
    Exit Sub
          
ErrHandler:
    Kill objDoc
    MsgBox ("Run-time Error " & Err.Number)
    Exit Sub

End Sub

 

[Norie]

If you remove On Error Goto ErrHandler what's the error message?

 

[dirtychinchilla]

It gives me:

Run-time error '4605':

This method or property is not available because the document is not a mail merge main document."

 

[Norie]

Oops I thought the file you were working with was a template.

Try this.

Sub FSMerge()

Dim wrdObj As Object
Dim wrdDoc As Object
Dim objDoc As String, B As Integer

objDoc = "Q:\Temp\FSTemp.xls"
     
On Error GoTo ErrHandler:

    Set wrdObj = CreateObject("Word.Application")

    wrdObj.Visible = True

    ThisWorkbook.SaveCopyAs Filename:=objDoc
    
    Set wrdDoc = wrdObj.Documents.Open("Q:\Index\Documents\Quotation.dotm")

    wrdDoc.MailMerge.Execute

    wrdDoc.Close 0

    Kill objDoc
     
     'Skip error handler
    Exit Sub
          
ErrHandler:
    Kill objDoc
    MsgBox ("Run-time Error " & Err.Number)
    Exit Sub

End Sub

 

[dirtychinchilla]

Haha not to worry! Same error, I'm afraid.

The Excel document is a standard document, 1997-2003 format though.

The Word template is a template for 1997-2003 I believe.

 

[Norie]

Has the document been changed?

If you do a manual mail merge with the document in Word with the macro recorder turned on what code, if any, is produced?

 

[dirtychinchilla]

Nothing has changed in the document, it still works for everyone except this one guy!!

Sub Macro1()
'
' Macro1 Macro
'
'
ActiveDocument.MailMerge.OpenDataSource Name:="Q:\Temp\FSTemp.xls", _
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=Q:\Temp\FSTemp.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glo" _
, SQLStatement:="SELECT * FROM `MailMerge`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
End Sub

 

[dirtychinchilla]

Hi guys,

Just wondering whether you have any ideas?

 

[Norie]

Sorry, can't really think of anything other than there being a problem with the one user's setup.

 

[dirtychinchilla]

Fair enough - any idae of what the issue with his setup could be? Anything I can check?