Consulting

Results 1 to 3 of 3

Thread: Bypassing "SQL Command" dialog box in a macro to automate a mail merge

  1. #1
    VBAX Regular ShogunPatch's Avatar
    Joined
    Jan 2018
    Location
    London
    Posts
    14
    Location

    Bypassing "SQL Command" dialog box in a macro to automate a mail merge

    I have prepared a macro which opens a mail merge document and then runs the mail merge; however, each time I run it I get a dialog box saying "Opening this document will run the following SQL command... blah blah... Do you want to continue" and I have to select "Yes" for the macro to continue running successfully.
    Is there a way to avoid the user having to click on "Yes" for the macro to run? I have tried experimenting with switching Application.DisplayAlerts on and off and also with SendKeys, so far without success. Ideally, I don't want to switch off all dialogs, I just want the macro to run smoothly without interruption from that particular box.
    Here is my code:

    Sub CreateExpertiseFromBlank()
    '
    ' CreateExpertise Macro
    ' Uses mail merge template to create a formatted expertise list from an Excel source table into a blank doc
    '
        Documents.Open ("C:\Users\ShogunPatch\Dropbox\MyDesktop\ExpertiseList.docm")
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        Selection.WholeStory
        Selection.Fields.Update
    '    Application.DisplayAlerts = True
    End Sub
    Any help would, as ever, be much appreciated.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by ShogunPatch View Post
    Is there a way to avoid the user having to click on "Yes" for the macro to run? I have tried experimenting with switching Application.DisplayAlerts on and off and also with SendKeys, so far without success
    Setting:
    Application.DisplayAlerts = wdAlertsNone
    before you open the mailmerge main document will suppress the SQL prompt, but it will also kill the mailmerge - in which case you macro becomes responsible for re-establishing all the mailmerge parameters (mailmerge type, datasource, and so on).

    Alternatively, there is a registry edit, but that is user-specific and affects all mailmerge main documents opened by that user.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular ShogunPatch's Avatar
    Joined
    Jan 2018
    Location
    London
    Posts
    14
    Location
    That sounds like it might do exactly what I need... I shall investigate and revert back. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •