Running a MS Word mail merge from excel

Question

I know this has been asked before and I tried to update the other thread but was not allowed.

I have a spreadsheet that has data that is used in a MS mail merge. I have a Word mail merge document that is all set up to run. The fields are already set up. When I open the word doc I get a prompt that says "Opening this document will run the following SQL command", etc, etc. What I want to do is have the Excel spreadsheet run the mail merge without any user intervention when I click a button. I put together the following code from examples on this forum but is not working. When run Word opens and I get the same prompt. How do I get this to work?

Public Sub RunMailMerge(MMFileName As String)

    Dim wdDoc As Object

'   open the mail merge layout file

    Set wdDoc = GetObject(MMFileName, "Word.document")
    wdDoc.Application.Visible = True

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

'   cleanup

    wdDoc.Close SaveChanges:=False
    Set wdDoc = Nothing

End Sub

Also all of the examples I have seen use late binding. Wouldn't it better to use early bind of the word object?

Thanks Don

It depends - Late binding is very useful if you arent sure what version the user has, or you need it to work with every version. — braX, Dec 22 '19 at 06:23
You can't use word constants if late binding. Look up the value in Object Browser and use the value. Or early bind to Word in References dialog. — , Dec 22 '19 at 06:50
You want the value of 1 - https://apireference.aspose.com/net/words/aspose.words.settings/mailmergemaindocumenttype — braX, Dec 22 '19 at 07:21
Good point about the version. The word constants are working. The bigger question is how to get the MM working? It launches but then pops up a dialog box waiting for an answer. I want no user interaction at all. User clicks a button and the mail merge documents print. — Don, Dec 22 '19 at 15:46

score 1 · Answer 1 · answered Dec 22 '19 at 23:21

How about this concept?

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

objWord.activedocument.variables("FirstName").Value = Range("FirstName").Value
objWord.activedocument.variables("LastName").Value = Range("LastName").Value

ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Add DocVariables to your word document, and name them appropriately. Run the code from Excel.

score 0 · Answer 2 · answered Dec 23 '19 at 03:17

https://support.microsoft.com/en-au/help/825765/you-receive-the-opening-this-will-run-the-following-sql-command-messag

The prompt that is described in the "Symptoms" section is by design.

This prompt exists in all later versions of Microsoft Office and was introduced in Office XP Service Pack 3. This prompt was added in the products listed in the "Applies to" section to make mail merge more secure. If you click Yes when you receive the prompt, you let code run on your computer. A malicious user may be able to craft a SQL query that is designed to steal or to destroy data that you have access to. If you click No when you receive the prompt, you do not let code to run.

Note Setting DisplayAlerts=none through VBA suppresses the prompt when the file is opened by using VBA, but uses the NO option to open the document and no data is attached to the mail merge main document.

score 0 · Answer 3 · edited Jan 06 '20 at 11:46

There are two basic approaches that can be used to run a mail merge without triggering the prompt. Note that the prompt is a security measure since the SQL a mail merge runs could cause harm to a system. The prompt ensures that the user is aware of this and (supposedly) only runs the mail merge if the document is from a "trusted source".

Remove the data source from the main merge document, then add it explicitly using VBA every time document is opened.

The advantage to this approach is that it upholds Word's security setting. If the user allows the VBA to run, then supposedly the entire project is trusted, so attaching the data source using code is allowed.

The data source can be removed from the document by choosing the option to open the document without allowing the mail merge (the button labelled "No" in the version of Word I'm looking at). Save the document in that state. Add a line to your code that links in the data source before executing the mail merge.

In order to figure out the necessary syntax, you can either record a macro while connecting to the data source (this is most certain and fastest), or you can query the data source from the document before disconnecting it. In Word, in the Immediate Window (Ctrl+G) of the VBA Editor enter the ? lines and press Enter, one-by-one:

?ActiveDocument.MailMerge.DataSource.Name
C:\Users\Cindy Meister\Documents\Personal\klpFeb00.mdb
?Document.MailMerge.DataSource.QueryString
SELECT * FROM `Q_MailMergeInvite`

Then

?ActiveDocument.MailMerge.DataSource.ConnectString

After pressing Enter for this one you should see something along the lines of

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[username]\Documents\Personal\Text.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

These, in double-quotes, are assigned to the Name, SQLStatement and Connection parameters of Document.MailMerge.OpenDataSource.

Change the Registry setting to disable the prompt

This is described in the Knowledge Base article about the prompt.

The exact Registry key varies, depending on the version of Word, but follows the pattern

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

The above is for Word 2013, version 15. Change it to 14 for Word 2010; 16 for Word 2016, etc.

A new Registry entry of the type DWord needs to be created in this location, named SQLSecurityCheck. Assign it the value 00000000.

@Don Since you're relatively new to the site: The accepted way to "thank" people for contributions that aren't the answer to your question is to upvote by clicking the up-arrow next to the contribution. Upvoting is to let people know this (in your opinion) is a useful/helpful contribution to the site. It's possible for questions as well as answers. You should be able to do this everywhere (although it may be you need a bit more reputation before it's enabled). Later, you'll also be able to downvote things you consider to be poor quality. — Cindy Meister, Dec 31 '19 at 22:34

score -1 · Accepted Answer · answered Dec 23 '19 at 06:21

-1

For example:

Sub MergetoPrint()
Dim wrdObj As Object, wrdDoc As Object, strFile As String
strFile = ThisWorkbook.FullName
Const wdFormLetters As Long = 0
Const wdSendToNewDocument As Long = 0
Const wdSendToPrinter = 1
Const wdSendToEmail As Long = 2
Const wdMergeSubTypeAccess As Long = 1
Const wdMailFormatHTML As Long = 1
Set wrdObj = CreateObject("Word.Application")
With wrdObj
  .Visible = False
  .DisplayAlerts = False
  Set wrdDoc = .Documents.Open(ThisWorkbook.Path & "\MailMergeMainDocument.docx", False, True, False, , , , , , , , False)
  With wrdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .Destination = wdSendToPrinter
      .SuppressBlankLines = True
      .OpenDataSource Name:=strFile, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strFile;Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1"";", SQLStatement:="SELECT * FROM [Sheet1$]", _
        SQLStatement1:="", SubType:=wdMergeSubTypeAccess
      .Execute
    End With
    .Close 0
  End With
End With
Set wrdDoc = Nothing: Set wrdObj = Nothing
End Sub

answered Dec 23 '19 at 06:21

macropod

12,757
2
9
21

This worked, sort of. I stepped through the code and the when the .opendatasource is executed the mail merge runs and prints the letters. VBA execution then stops. No errors. It doesn’t execute the .execute statement or proceed any further. – Don Dec 26 '19 at 15:58
This actually worked. When I tested on my home machine it worked fine. When tested at work it did not work. I believe it is not working in the office because there are different versions of Office installed. Now I need to get the IT department to fix Office. Thanks! – Don Dec 31 '19 at 21:37
The solution has nothing to do with the Office version - unless you're using something from the 1980s... More likely it has everything to do with restrictions imposed by your IT department. So why the downvote??? – macropod Jan 01 '20 at 09:14
I didn’t downvote. If I did it was by accident. I actually checked this as the solution. On my office PC there are multiple recent versions of office installed. Something is not installed correctly because when you click on a spreadsheet 2016 version launches. Click on a word doc and 2013 launches. I don’t think my problem is that there are multiple versions. I think the problem is is that something is not installed correctly. – Don Jan 02 '20 at 01:22
A little late but I wanted to update. IT uninstalled Office and re-installed and it started working. It was some sort of version incompatibility issue. – Don Jan 24 '22 at 21:38

Running a MS Word mail merge from excel

4 Answers4

Linked