vba 执行 Word 邮件合并
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1357121/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing Word Mail Merge
提问by Tom
I have an excel sheet with data and want to export it to a new word document.
我有一个包含数据的 Excel 工作表，想将其导出到一个新的 Word 文档中。
Is it possible to start MAIL MERGEfrom excel macro by clicking a button on the sheet?
是否可以MAIL MERGE通过单击工作表上的按钮从 excel 宏开始？
回答by dendarii
If your Word document is already configured with the merge fields, and you are running the macro from the workbook that contains the data you want to merge into the Word document, then try this:
如果您的 Word 文档已经配置了合并域，并且您正在从包含要合并到 Word 文档中的数据的工作簿中运行宏，请尝试以下操作：
Sub RunMerge()
Dim wd As Object
Dim wdocSource As Object
Dim strWorkbookName As String
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdocSource = wd.Documents.Open("c:\test\WordMerge.docx")
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
wdocSource.MailMerge.MainDocumentType = wdFormLetters
wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Sheet1$`"
With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
wd.Visible = True
wdocSource.Close SaveChanges:=False
Set wdocSource = Nothing
Set wd = Nothing
End Sub
回答by MattM
To get dendarii's solution to work I had to declare Word constants in Excel VBA as follows:
为了使 dendarii 的解决方案起作用，我必须在 Excel VBA 中声明 Word 常量，如下所示：
' Word constants
Const wdFormLetters = 0, wdOpenFormatAuto = 0
Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = -16
回答by Robert
If your word document is already configured with data source and merge fields layout then it becomes much simpler. In the example below MailMergeLayout.docis all setup ready to perform a merge. A button in Excel is linked to RunMailMerge()as below. All the code is contained in an Excel VBA module.
如果您的 Word 文档已经配置了数据源和合并字段布局，那么它会变得简单得多。在下面的示例中，MailMergeLayout.doc已准备好执行合并。Excel 中的按钮链接到RunMailMerge()，如下所示。所有代码都包含在 Excel VBA 模块中。
Sub RunMailMerge()
Dim wdOutputName, wdInputName As String
wdOutputName = ThisWorkbook.Path & "\Reminder Letters " & Format(Date, "d mmm yyyy")
wdInputName = ThisWorkbook.Path & "\MailMergeLayout.doc"
' 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
' show and save output file
wdDoc.Application.Visible = True
wdDoc.Application.ActiveDocument.SaveAs wdOutputName
' cleanup
wdDoc.Close SaveChanges:=False
Set wdDoc = Nothing
End Sub
回答by cyrus
Private Sub CommandButton1_Click()
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Documents and Settings\User\Desktop\mergeletter.doc"
wordapp.Visible = True
wrddoc = wordapp.documents("C:\Users\User\Desktop\sourceofletters.xls")
wrddoc.mailmerge.maindocumenttype = wdformletters
With wrddoc.activedocument.mailmerge
.OpenDataSource Name:="C:\Users\User\Desktop\sourceofletters.xls", _
SQLStatement:="SELECT * FROM `Sheet1`"
End With
End Sub
Above code is to open a word mailmerge document (with its source link and mergefield codes all setup ) all I want is for the message box "Opening the document will run the following SQL command "to be made available to the user , from that point forward the user could either select 'Yes'or 'No'.
上面的代码是打开一个 word mailmerge 文档（它的源链接和合并域代码都设置好了）我想要的只是让"Opening the document will run the following SQL command "用户可以使用消息框，从那时起用户可以选择'Yes'或'No'。
回答by user5500194
Dim opt As String
opt = MessageBox("Opening the document will run the following SQL command", vbYesNo)
If opt = vbYes Then
'execute query
End If
