Mail Merge using VBA

Question

I have a document that I have "connected" to an Access table. I want to COMPLETELY AUTOMATE the merge process for the user. When I first open Word, this dialog opens:

I have to select YES manually.

I have an OnOpen module that completes the rest of the merge:

Is there a way to AUTOMATE the first dialog box? I tried Sendkeys {LEFT}~

but Word did not like that command (it would not Debug) and I could not find an event to use for it.

Please help. Have already tried escalating this to Level 2 but they could not help.

• ****Moved from:*Microsoft 365 and Office/Word/Windows 10/
• Microsoft 365 Family

Answer 1

I think you'll be stuck with SENDKEYS, even though it's notoriously unreliable. The parameter must be a string, so put double quotes around it: SENDKEYS "{LEFT}~"

If that doesn't work, use the accelerator of the Yes button: SENDKEYS "%Y"

Answer 2

Yes, I can add the SENDKEYS command to the VB module's OnOpen. However, it doesn't work. I need an event BEFORE the OnOpen to attach that to. Is there one???

Answer 3

Download the MergeSQLMessageEliminator.zip file from the following page of my One Drive:

http://bit.ly/1hduSCB

Then extract the files from the archive and read the

“READ ME – MergeSQLMessageEliminator.docx

to see how to make use of it.

Answer 4

One way of dealing with this would be to make the first line of code:

Application.DisplayAlerts = wdAlertsNone

However, you would then need to implement the SQL as part of the same macro. For example:

Private Sub Document_Open()

Application.DisplayAlerts = wdAlertsNone

Application.ScreenUpdating = False

Dim StrMMSrc As String

StrMMSrc = "FilePath\Filename.extension"

With ActiveDocument

With .MailMerge

.MainDocumentType = wdFormLetters

.OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _

LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _

"Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _

SQLStatement:="SELECT * FROM TodaysRenList"

.Destination = wdSendToNewDocument

.SuppressBlankLines = True

With .DataSource

.FirstRecord = wdDefaultFirstRecord

.LastRecord = wdDefaultLastRecord

End With

.Execute Pause:=False

End With

.Saved = True

End With

Application.DisplayAlerts = wdAlertsAll

Application.ScreenUpdating = True

End Sub

where the 'StrMMSrc' variable gives the full path & name of the mailmerge source.