Perform Mail Merge And Not Show Word

Question

I am performing a mail merge from Excel, which works exactly as I need. My issue is that I am wanting to keep word hidden from the user, and that is not occurring. I end up with an empty instance of word on the screen that I do not want.

This is my syntax - why am I unable to completely hide and close word when the process is finished?

    Dim wdapp As Word.Application, wdDoc As Word.Document, wdMaiMerge As Word.MailMerge
'Setting refs
Set wdapp = CreateObject("Word.Application")
Set wdDoc = wdapp.Documents.Open(wdpath)
Set wdMailMerge = wdDoc.MailMerge
'hiding display from user
wdapp.Visible = False
'Setting mail merge
With wdMailMerge
 .OpenDataSourcexxxx, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False
 .Execute
End With
'Finishing
Set wdapp = Nothing
wdapp.Quit

Answer 1

You're not telling Word what query to use or what to do with either the document you're using for the mailmerge once you've used it or the mailmerge output! And, if the document you're opening is a mailmerge main document, your code will hang at that point - you need to suppress that and supply all the SQL code yourself. For example:

Sub MailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
With wdApp
  .Visible = False
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(Filename:=ThisWorkbook.Path & "\MailMergeMainDocument.docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentFiles:=False, Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM `Sheet1$`", SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      .Execute
      With wdApp.ActiveDocument
        'What do you want to do with the output document??? For example:
        .SaveAs2 Filename:=ThisWorkbook.Path & "\MailMergeOutputDocument.docx", _
          FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs Filename:=ThisWorkbook.Path & "\MailMergeOutputDocument.pdf", _
          FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        'Close the output document
        .Close False
      End With
      'Disconnect from the data source
      .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Quit Word
  .Quit
End With
End Sub