Remove prompts from automated mail merge

Sade · Oct 16, 2017

I'm using Excel vba to automate an existing Word mail merge from an Excel spreadsheet. Everything is working fine except that it prompts me to confirm the mail merge. I want to get rid of these prompts as they lock the process until they are answered.

If calling Word from Excel vba, is there a command to accomplish this? I've been trying this:

<code>
Dim wd As Word.Application

wd.Application.DisplayAlerts = wdAlertsNone
stuff
wd.Application.DisplayAlerts = wdAlertsAll
</code>

Kenneth Hobson · Oct 16, 2017

That should work...

e.g.

Code:

'If Word found locked fields shows after a merge, see this workaround:
'http://support.microsoft.com/kb/292155 - due to inline text in Autoshape layout.

'Requires Tools > References > Microsoft Word 11.0 Object Library
Sub MergeRun(frmFile As String, datFile As String, _
  SQL As String, _
  Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
  Optional iNoCopies As Integer = 1)
  
  Dim wdApp As Word.Application
  Dim myDoc As Word.Document
  
  'Tell user what file is missing and exit.
  If Dir(frmFile) = "" Then
    MsgBox "Form file does not exist." & vbLf & frmFile, _
      vbCritical, "Exit - Missing Form File"
  End If
  If Dir(datFile) = "" Then
    MsgBox "Data file does not exist." & vbLf & datFile, _
      vbCritical, "Exit - Missing Data File"
  End If
  If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
  
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then
      Set wdApp = CreateObject("Word.Application")
  End If
  On Error GoTo errorHandler
  
  With wdApp
   On Error GoTo errorHandler
    wdApp.Application.DisplayAlerts = wdAlertsNone
    
    'Open form file and associate data file
    Set myDoc = .Documents.Open(frmFile, False, True, False)
    .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    .ActiveDocument.MailMerge.OpenDataSource Name:=datFile, _
      ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
      AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
      WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
      Format:=wdOpenFormatAuto, Connection:="", SQLStatement:=SQL, SQLStatement1 _
      :="", SubType:=wdMergeSubTypeOther
    'Merge to a new document
    With wdApp.ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    .Visible = True
    
    If bPrint = True Then
      .Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
    End If
    
    If bClose = True Then
      .ActiveDocument.Close False
      .ActiveDocument.Close False
    End If

    wdApp.Application.DisplayAlerts = wdAlertsAll
  End With
     
errorExit:
    On Error Resume Next
    myDoc.Close False
    Set myDoc = Nothing
    Set wdApp = Nothing
    Exit Sub
 
errorHandler:
    MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
    Resume errorExit
End Sub

Macropod · Oct 16, 2017

See, for example:
https://www.mrexcel.com/forum/gener...osted-sharepoint-post4896255.html#post4896255
https://www.mrexcel.com/forum/gener...e-excel-2010-vba-post4872807.html#post4872807
https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4796480.html#post4796480

Do note that setting .DisplayAlerts = wdAlertsNone not only suppresses the SQL prompt; it also kills off the mailmerger data connection, rendering your mailmerge main document nothing more than an ordinary document with mergefields. Hence, the code must re-establish the mailmerge connection and SQL query. The posts in the above links all do that; the last one includes code to find out how your SQL query should be coded if you currently have filtering applied.

Kenneth Hobson · Oct 16, 2017

For kicks, here are a few other related links.

' http://www.excelforum.com/excel-programming/796614-mail-merge-from-excel.html
' http://www.excelforum.com/excel-programming/798299-print-mail-merge-document.html
'Word 's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). _
To see how to group records with any mailmerge data source supported by Word, _
check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
' http://lounge.windowssecrets.com/index.php?showtopic=731107
' or
' http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
' Kenneth Hobson, Mail Merge from Excel to MSWord: ElephantsRus
' https://app.box.com/s/0zlydxinl10t23mifkrr
' or, https://www.dropbox.com/s/fx4r06xq4mnlan6/ElephantsRusDocs.zip?dl=0

The last two are the same zip file. I posted example Excel and Word files to show the way that I used my routine.

Sade · Oct 16, 2017

Aha. Macropod's response above would explain my problem below. Looking through all the links now. ... Thank you all!!!

Thanx a lot for getting back so quickly, and for the sample code. I've been running in circles with my own code for so long, I decided to just take yours, and try and do the mail merge of static data (I mean just do the mail merge portion and exclude all the pre-manipulation of it), then I'd start bringing in all the user input stuff. But it keeps looking for a table in the folder where my Excel data is. A window pops up behind everything asking to choose the table. It prompts with the address to the correct folder but the file I have, "Mail Merge Creator.xlsm" is not included. It goes not further till this is resolved, then errors out for not finding data.

So, just the mail merge code:

Code:

'If Word found locked fields shows after a merge, see this workaround:
'http://support.microsoft.com/kb/292155 - due to inline text in Autoshape layout.

Sub Source()

    Dim wdocOutput As String
    Dim wdocForm As String
    Dim xlData As String
    Dim strSql As String
    
    wdocOutput = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Macros\ATT_Looper.txt"
    wdocForm = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx"
    xlData = "\\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm"
    strSql = "SELECT * FROM 'NonExempt$'"

Call MergeRun(wdocForm, xlData, strSql)

End Sub
'Requires Tools > References > Microsoft Word 11.0 Object Library
Sub MergeRun(frmFile As String, datFile As String, _
  SQL As String, _
  Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
  Optional iNoCopies As Integer = 1)
  
  Dim wdApp As Word.Application
  Dim myDoc As Word.Document
  
  'Tell user what file is missing and exit.
  If Dir(frmFile) = "" Then
    MsgBox "Form file does not exist." & vbLf & frmFile, _
      vbCritical, "Exit - Missing Form File"
  End If
  If Dir(datFile) = "" Then
    MsgBox "Data file does not exist." & vbLf & datFile, _
      vbCritical, "Exit - Missing Data File"
  End If
  If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
  
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then
      Set wdApp = CreateObject("Word.Application")
  End If
  On Error GoTo errorHandler
  
  With wdApp
   On Error GoTo errorHandler
    wdApp.Application.DisplayAlerts = wdAlertsNone
    
    'Open form file and associate data file
    Set myDoc = .Documents.Open(frmFile, False, True, False)
    .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    .ActiveDocument.MailMerge.OpenDataSource Name:=datFile, _
      ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
      AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
      WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
      Format:=wdOpenFormatAuto, Connection:="", SQLStatement:=SQL, SQLStatement1 _
      :="", SubType:=wdMergeSubTypeOther
    'Merge to a new document
    With wdApp.ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    .Visible = True
    
    If bPrint = True Then
      .Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
    End If
    
    If bClose = True Then
      .ActiveDocument.Close False
      .ActiveDocument.Close False
    End If

    wdApp.Application.DisplayAlerts = wdAlertsAll
  End With
     
errorExit:
    On Error Resume Next
    myDoc.Close False
    Set myDoc = Nothing
    Set wdApp = Nothing
    Exit Sub
 
errorHandler:
    MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
    Resume errorExit
End Sub

Kenneth Hobson · Oct 16, 2017

I don't use UNC paths. I guess that I should modify it to use FSO to check for file existence rather than Dir.

Did you use the special slant quote characters to encapsulate the sheet name? e.g.

Code:

 MergeRun frmDoc, datFile, "SELECT * FROM `Sheet1$`"

I guess that you added the Word Object Library reference? As you can see, I did this years ago in v11.

Macropod · Oct 16, 2017

Try:

Code:

Sub Source()
Const wdocOutput As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Macros\ATT_Looper.txt"
Const wdocForm As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx"
Const xlData As String = "\\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm"
Const StrSQL As String = "SELECT * FROM 'NonExempt$'"

Call MergeRun(wdocForm, xlData, StrSQL)
End Sub

'Requires Tools > References > Microsoft Word # Object Library
Sub MergeRun(frmFile As String, datFile As String, StrSQL As String, _
  Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
  Optional iNoCopies As Long = 1)
  
  'Tell user what file is missing and exit.
  If Dir(frmFile) = "" Then
    MsgBox "Form file does not exist." & vbLf & frmFile, vbCritical, "Exit - Missing Form File"
  End If
  If Dir(datFile) = "" Then
    MsgBox "Data file does not exist." & vbLf & datFile, vbCritical, "Exit - Missing Data File"
  End If
  If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
  
  Dim wdApp As New Word.Application, myDoc As Word.Document
  
  With wdApp
    .DisplayAlerts = wdAlertsNone
    
    'Open form file and associate data file
    Set myDoc = .Documents.Open(Filename:=frmFile, ReadOnly:=True, AddToRecentFiles:=False)
    With myDoc
      With .MailMerge
        .MainDocumentType = wdFormLetters
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        .OpenDataSource Name:=datFile, ConfirmConversions:=False, _
          ReadOnly:=True, LinkToSource:=False, Revert:=False, _
          AddToRecentFiles:=False, PassWordDocument:="", _
          PasswordTemplate:="", WritePassWordDocument:="", _
          WritePasswordTemplate:="", Format:=wdOpenFormatAuto, _
          SubType:=wdMergeSubTypeAccess, Connection:= _
          "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
          "Data Source=datFile;Mode=Read;Extended Properties=""HDR=YES;IME", _
          SQLStatement:=StrSQL, SQLStatement1:=""
        With .DataSource
          .FirstRecord = wdDefaultFirstRecord
          .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
      End With
      .Close False
    End With
    .DisplayAlerts = wdAlertsAll
    If bPrint = True Then .ActiveDocument.PrintOut Copies:=iNoCopies
    If bClose = True Then
      .ActiveDocument.Close False
      .Quit
    End If
  End With
  Set myDoc = Nothing: Set wdApp = Nothing
End Sub

Note: Your StrSQL names the NonExempt worksheet as the data source. If that sheet doesn't exist, OpenDataSource will fail at that point.

PS: I've simplified/cleaned-up some aspects of the code.

Remove prompts from automated mail merge

Sade

Board Regular

Excel Facts

Kenneth Hobson

Well-known Member

Macropod

Retired Moderator

Kenneth Hobson

Well-known Member

Sade

Board Regular

Kenneth Hobson

Well-known Member

Macropod

Retired Moderator

Similar threads

Forum statistics

Share this page

Remove prompts from automated mail merge

Sade

Board Regular

Excel Facts

Kenneth Hobson

Well-known Member

Macropod

Retired Moderator

Kenneth Hobson

Well-known Member

Sade

Board Regular

Kenneth Hobson

Well-known Member

Macropod

Retired Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock