excel VBA to Automatically select Yes when prompted during mail merge

Ashton Sheets picture Ashton Sheets · Jul 2, 2012 · Viewed 14.2k times · Source

I'd like the system to be as automated for my users as possible. Right now, I have code that runs when the user clicks a button. The code takes data with the intention of applying it to a word document via mail merge.

Everything works as intended except there's always a message that pops up saying

Opening this document will run the following SQL command:

Select * FROM 'TAGS$'

Data from your database will be placed in the document. Do you want to continue?

I need to keep this as simple as possible without risking users selecting "No" because they're confused. How can VBA automatically proceed and accept the data placement, as it would had they selected "Yes"?

I tried just using the following code to block alerts in hopes it would default "Yes" and proceed, but it didn't work.

Application.DisplayAlerts = False

This is what I have

Sub RunMailMerge()

    Application.ScreenUpdating = False

    Dim wdOutputName, wdInputName As String
    wdOutputName = ThisWorkbook.Path & "\nametags - " _
        & Format(Date, "d mmm yyyy")
    wdInputName = ThisWorkbook.Path & "\nametags.docx"

    ' open the mail merge layout file
    Dim wdDoc As Object
    Set wdDoc = GetObject(wdInputName, "Word.document")
    wdDoc.Application.Visible = True

    With wdDoc.MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
         .Execute Pause:=False
    End With

    'Application.ScreenUpdating = True

    'show and save output file
    wdDoc.Application.Visible = True
    wdDoc.Application.ActiveDocument.SaveAs wdOutputName

    ' cleanup
    wdDoc.Close SaveChanges:=False
    'activedoc.Close
    Set wdDoc = Nothing

End Sub

Answer

Tim Williams picture Tim Williams · Jul 3, 2012

Try setting the DisplayAlerts property in Word (if that's where the alert is coming from):

Dim tmp as Long

tmp = wdDoc.Application.DisplayAlerts 

wdDoc.Application.DisplayAlerts = wdAlertsNone
'do the action which causes the prompt
wdDoc.Application.DisplayAlerts = tmp