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
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