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

 

[Macropod]

You said earlier that the affected user is a new one and "is one of about 3 with the latest version of office". Either or both of those might have something to do with the problem, regarding user profiles & read/write permissions and/or faults in Office 2016 (there are numerous still being ironed out).

I note also that you're using a dotm template for the mailmerge. As such it cannot be "a template for 1997-2003"; it has to have been created for Word 2007 & later. Moreover, if that template is configured for a mailmerge, every time a new document is created from it, you're liable to get two SQL prompts - which would cause havoc with a macro. There is a registry patch for suppressing those so, if your new user hasn't had that registry patch applied, that could be contributing to the problem. Regardless, you really shouldn't use a template for mailmerges - you should use a mailmerge main document instead. Opening a mailmerge main document would, for example, generate only a single SQL prompt. Even without the registry patch, the SQL prompt can be suppressed when instancing Word through VBA, but doing that also kills the document's mailmerge connections. So, instead of trying to drive the mailmerge with a dotm file, you may as well just use a docx file and let your Excel macro manage the whole process. For example:

Sub FSMerge()
Dim wrdObj As Object, wrdDoc As Object
Const strFile As String = "Q:\Temp\FSTemp.xls"
Const wdFormLetters As Long = 0
Const wdSendToNewDocument As Long = 0
Const wdMergeSubTypeAccess As Long = 1
On Error GoTo ErrHandler:
ThisWorkbook.SaveCopyAs Filename:=strFile
Set wrdObj = CreateObject("Word.Application")
With wrdObj
  .Visible = True
  .DisplayAlerts = False
  Set wrdDoc = .Documents.Open("Q:\Index\Documents\Quotation.docx", False, True, False, , , , , , , , False)
  'Set wrdDoc = Documents.Add("Q:\Index\Documents\Quotation.dotm", , , False)
  With wrdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      .OpenDataSource Name:=objDoc, 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=objDoc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
        SQLStatement:="SELECT * FROM `MailMerge`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
      .Execute
    End With
    .Close 0
  End With
End With
Set wrdDoc = Nothing
Kill strFile
'Skip error handler
Exit Sub
ErrHandler:
Kill objDoc
Set wrdDoc = Nothing
MsgBox ("Run-time Error " & Err.Number)
End Sub

Note the use of .Documents.Open and the change in filename to 'Quotation.docx'. You could stick with your template usage code, though, if you prefer, by using the commented-out code instead.

 

[dirtychinchilla]

Hi Macropod,

Thanks for the very comprehensive response. I had thought that I was putting the template into the older format, but clearly I was wrong!! I have changed the format to .dot now and, apart from some formatting issues, it seems to have fixed the problem!

With regards to the SQL prompts, I only ever received one and it was to confirm that I wanted to connect to our temp xls file.

Having said that, I tried your code and it falls down on the .OpenDataSource step, and moves to the error line. I'm afraid I don't understand enough about it to try and fix it, but would appreciate any suggestions. Moving away from the SQL prompts and making the process more robust would be great.

 

[Macropod]

A few corrections and further code simplification -

Sub FSMerge()
Dim wrdObj As Object, wrdDoc As Object
Const strFile As String = "Q:\Temp\FSTemp.xls"
Const wdFormLetters As Long = 0
Const wdSendToNewDocument As Long = 0
Const wdMergeSubTypeAccess As Long = 1
Const wdOpenFormatAuto As Long = 0
On Error GoTo ErrHandler:
ThisWorkbook.SaveCopyAs Filename:=strFile
Set wrdObj = CreateObject("Word.Application")
With wrdObj
  .Visible = True
  .DisplayAlerts = False
  'Set wrdDoc = .Documents.Open("Q:\Index\Documents\Quotation.doc", False, True, False, , , , , , , , False)
  Set wrdDoc = .Documents.Add("Q:\Index\Documents\Quotation.dot", , , False)
  With wrdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      .OpenDataSource Name:=strFile, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strFile;Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1"";", SQLStatement:="SELECT * FROM [MailMerge$]", _
        SQLStatement1:="", SubType:=wdMergeSubTypeAccess
      .Execute
    End With
    .Close 0
  End With
End With
Set wrdDoc = Nothing
Kill strFile
'Skip error handler
Exit Sub
ErrHandler:
Kill strFile
Set wrdDoc = Nothing
MsgBox ("Run-time Error " & Err.Number)
End Sub

Although now coded to use a template (.dot format), I'd still recommend the use of a document (.doc format, for which I've included some commented-out code) - because that's what a mailmerge is meant to work with. For one thing, you can both run a mailmerge from a document and edit the document somewhat more easily than you can with a template.

 

[dirtychinchilla]

Oh man, it works!!! Couple of extremely minor mods to the code and it works perfectly!! Thanks man. It even works with the .docx file!! Awesome :D

For completeness, this is the code I have now:

Sub FSMerge()
Dim wrdObj As Object, wrdDoc As Object
Const strFile As String = "Q:\Temp\FSTemp.xls"
Const wdFormLetters As Long = 0
Const wdSendToNewDocument As Long = 0
Const wdMergeSubTypeAccess As Long = 1
Const wdOpenFormatAuto As Long = 0
On Error GoTo ErrHandler:
ThisWorkbook.SaveCopyAs Filename:=strFile
Set wrdObj = CreateObject("Word.Application")
With wrdObj
  .Visible = True
  .DisplayAlerts = False
  Set wrdDoc = .Documents.Open("Q:\Index\Documents\Quotation.docx", False, True, False, , , , , , , , False)
  With wrdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      .OpenDataSource Name:=strFile, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strFile;Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1"";", SQLStatement:="SELECT * FROM [MailMerge]", _
        SQLStatement1:="", SubType:=wdMergeSubTypeAccess
      .Execute
    End With
    .Close 0
  End With
End With
Set wrdDoc = Nothing
Kill strFile
'Skip error handler
Exit Sub
ErrHandler:
Kill strFile
Set wrdDoc = Nothing
MsgBox ("Run-time Error " & Err.Number)
End Sub