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

 

[baitmaster]

runtime error 429 is one of those that's hard to pin down what the issue actually is. See https://support.microsoft.com/en-us/kb/828550 for details including suggested diagnosis approach

Essentially the machine can't create the object from the template. My first point would be, is the address correct? You're using the Q drive shortcut instead of the full UNC path (Universal Naming Convention), there's a good chance this is your problem

 

[dirtychinchilla]

Hi baitmaster,

Yeah I've had a look at loads of things about the error code and it basically just seems to mean "it won't work." I've been on that link before and nothing seems to work.

Regarding the address, it's the same for maybe 10 PCs here, so it's weird that that's causing an error. I don't know what the UNC path is - could you explain more please?

dirtychinchilla

 

[dirtychinchilla]

baitmaster, I've just done a bit of research on the UNC path and got that, so pardon me for being lazy. I've now amended the code to use the UNC path and it's made no difference.

 

[daverunt]

Quick check here. a dotm file is 2007 or late. Anyone running anything older?

 

[dirtychinchilla]

Nope, the guy who's having the problem is one of about 3 with the latest version of office. This is causing minor issues with the references, which I thought might be the problem, but I matched my settings (even though he has a newer version of office) and that didn't make a difference. Others with the same version seem to have no issue. It's very strange.

 

[Norie]

Does this work?

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

    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

 

[dirtychinchilla]

Hi Norie,

Thanks very much for that. I'm afraid it has an error 4605 when it gets to wrdDoc.MailMerge.Execute!

dirtychinchlila

 

[Norie]

What's the error message?

 

[dirtychinchilla]

Sorry Norie, wrong number this time. It's 5174.