excel VBA to Automatically select Yes when prompted during mail merge

Question

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

Sometimes, the alert message opens up behind the open excel book and I have to manually minimize the book to select "Yes". This doesn't really work while it's stuck in the middle of execution waiting for me to select the message box option, so I have to Alt+D to minimize everything THEN select excel and click the message box. As you can imagine, this is not very practical. Thanks for help. — Ashton Sheets, Jul 02 '12 at 22:03
ScreenUpdating = False didn't work. Code executes just fine, just doesn't push through the message box like I need it to. — Ashton Sheets, Jul 02 '12 at 22:04
@Tim Williams - Where, and how, should I implement this within the code? I modified the Original post to show the code I'm currently using. — Ashton Sheets, Jul 02 '12 at 22:46
If all else fails, throw a `MsgBox` just before, saying "Dear user, please click OK now and on the next message as well, regards, Ashton." — Jean-François Corbett, Jul 03 '12 at 07:38

score 2 · Answer 1 · answered Jul 02 '12 at 23:19

2

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

answered Jul 02 '12 at 23:19

Tim Williams

154,628
8
97
125

+1 Yes this will work with a slight modification. `wdAlertsNone` is a word constant. Change it to `0` or declare this at the top `Const wdAlertsNone = 0` Also we do not need the `Application` after `wdDoc` as it is already an application so `wdDoc.DisplayAlerts = 0` would suffice. – Siddharth Rout Jul 03 '12 at 09:40
@Jean-FrançoisCorbett: Yes it is a Boolean but also has an Integer equivalent of 0,-1,-2 depending on what is currently set. (`wdAlertsAll -1 , wdAlertsMessageBox -2 , wdAlertsNone 0 `) – Siddharth Rout Jul 03 '12 at 09:45
I assumed from Ashton's use of other Word constants that he had set a reference to the Word library. That might have been a mistake on my part, but in that case it's worth pointing out that using those without defining them might lead to problems. – Tim Williams Jul 03 '12 at 15:01
I'm having an issue with placement. When I have wdDoc.DisplayAlerts = 0 set BEFORE set wdDoc = GetObject(wdInputName, "Word.document") it obviously doesn't work because wdDoc isn't set. But if I put it immediately following the line it is too late because the word is only THEN opening and that's exactly when the message appears so it's too late. – Ashton Sheets Jul 03 '12 at 15:04
You should create an instance of word.application first, then use that to open the document. – Tim Williams Jul 03 '12 at 15:07
@TimWilliams: He is using Late Binding `Dim wdDoc As Object` – Siddharth Rout Jul 03 '12 at 20:36
@Siddharth - I did see that, but he's also using Word constants without having defined them (unless they're defined elsewhere as global variables/constants), so based on the code alone there's no definitive interpretation. Note there's nothing to stop you having both a reference to Word **and** using a late-bound object... – Tim Williams Jul 03 '12 at 20:44

score 2 · Answer 2 · answered Apr 08 '14 at 16:04

http://support.microsoft.com/kb/825765

Word 2013

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Word\Options