Word：邮件合并以生成PDF和控制文件 [英] Word: Mail merge to generate a PDF and a control file
问题描述
我有一个Word字母模板和一个Excel文件作为数据源来在Word中进行邮件合并。我想要做的是打开Word信件模板文件（其中包含代码），运行maro进行邮件合并（从Excel获取数据源），打印为PDF文件（连接所有邮件的
）将字母合并为1个单独的PDF），并生成一个控制/审计跟踪文件，其中包含总页数和没有记录。
$
我尝试使用下面的代码，可以成功生成合并的PDF文件。但是有些问题：
（1）这将关闭原始的Word模板文档，但会生成一个新的Word文档（包含所有邮件合并字母的Word文件，PDF的来源）。如何在运行代码后保持原始Word文档打开并关闭生成的Word文件？
$
（2）如何将新的页面总数和没有记录写入新的
生成的文件，如* .txt作为控制/审计跟踪文件？
< VBA>
公共 Sub MergePDF （）
'这会尝试加速宏。
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'这会将邮件与文档一词合并。
使用 GetObject（" U：\ wordletter.docm"）
;&NBSP;&NBSP; 使用 .MailMerge
&NBSP; .MainDocumentType = wdFormLetters
.OpenDataSource名称：= _
"U：\ excelsource.xlsx"，_
ConfirmConversions：= False ，ReadOnly：= True ，LinkToSource：= 正确，_
AddToRecentFiles：= False ，PasswordDocument：=""，PasswordTemplate：=""，_
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; WritePasswordDocument：=""，WritePasswordTemplate：=""，Revert：= False ，_
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;格式：= wdOpenFormatAuto，连接：= _
" Provider = Microsoft.ACE.OLEDB.12.0; User ID = Admin; Data Source = U：\ excelsource.xlsx; Mode = Read; Extended Properties =""" HDR = YES; IMEX = 1;"" ;;喷射OLEDB：系统数据库=""" ;;;喷射OLEDB：注册表路径="""" ;;> ;;&n;> ;;&n; _
，SQLStatement：=" SELECT * FROM`Pheet1 $`"，SQLStatement1：=""，SubType：= _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines =
True
;&NBSP; .Execute
False
结束
'这会打印到PDF文件并将其保存到指定的文件夹。
&NBSP; pdfname =" TestMergePDF-" &安培;格式（日期，"mm-dd-"）&格式（时间，"hhmmss"）& " .PDF"
.Application.Documents（1）.ExportAsFixedFormat pdfname，17
。关闭0
结束
End Sub
< / VBA>
请尝试以下方法：
Public Sub MergePDF（）
Dim pdfName As String
Dim oDoc As Document
Dim oMerged As Document
Dim oRng As Range
Dim iPages As Long
Dim iRecords As Long
'这会试图加速宏。
Application.ScreenUpdating = False
Application.DisplayAlerts = False
设置oDoc = GetObject（" U：\ wordletter .docm"）
使用oDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource名称：= _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; " U：\excelsource.xlsx" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP; ConfirmConversions：=假_
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;只读：=真，_
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; LinkToSource：= True，_
&NBSP; AddToRecentFiles：=假_
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; PasswordDocument：="" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; PasswordTemplate：="" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; WritePasswordDocument：="" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; WritePasswordTemplate：="" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;还原：=假，_
&NBSP;格式：= wdOpenFormatAuto，连接：= _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; " Provider = Microsoft.ACE.OLEDB.12.0; User ID = Admin; Data Source = U：\ excelsource.xlsx; Mode = Read; Extended Properties =""" HDR = YES; IMEX = 1;"" ;;喷气OLEDB：系统
数据库= QUOT;""英寸;喷气OLEDB：注册表路径= QUOT;""英寸;喷气OLEDB：发动机类型= 37;喷射OLE" ;, _
的SQLStatement：=" SELECT * FROM`Sheet 1中
`" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; SQLStatement1：="" ;, _
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;子类型：= wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
。执行错误
结束用于
设置oMerged = ActiveDocument
设置oRng = oMerged.Range
oRng.Start = oRng.End
iPages = oRng.Information（wdActiveEndPageNumber）
iRecords = ActiveDocument.Sections.Count - 1个
'这打印到PDF文件并将其保存到指定的文件夹。
pdfName =" U：\ TestMergePDF-" &安培;格式（日期，"mm-dd-"）&格式（时间，"hhmmss"）& ？" .PDF"
oMerged.ExportAsFixedFormat pdfName，17"路径
oMerged.Close 0
打开" U：\LogFile .TXT"附加为＃1
写入＃1，"页面 - " &安培; iPages& "&NBSP;记录 - " &安培; iRecords&NBSP; '写入数据。'\\ n
关闭＃1'关闭文件。
Documents.Open oDoc.FullName
结束子
Hi,
I have a Word letter template and an Excel file as a data source to do mail merge in Word. What I want to do is open the Word letter template file (which has code inside), run the maro to do mail merge (get data source from an Excel), print as a PDF file (concatenate
of all the mail merge letters into 1 single PDF), and also generate a control/audit trail file which contains the total no of pages and no of records.
I tried to use below code and can successfully generated a merged PDF file. However some problems:
(1) This will close the orginial Word template document, but generate a new Word document (a Word file with all the mail merge letters, the source of PDF). How can I keep the orginial Word doc open and close the generated Word file after running the code?
(2) How can I write the total no of pages and no of records to a newly
generated file, like *.txt as a control/audit trail file?
<VBA>
PublicSub MergePDF()
' This tries to speed up the macro.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' This does a mail merge with the word Document.
With GetObject("U:\wordletter.docm")
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:= _
"U:\excelsource.xlsx", _
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\excelsource.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Engine Type=37;Jet OLE" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines =
True
.Execute
False
End With
' This prints to a PDF file and saves it to a designated folder.
pdfname = "TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
.Application.Documents(1).ExportAsFixedFormat pdfname, 17
.Close 0
End With
End Sub
</VBA>
Try the following:
Public Sub MergePDF()
Dim pdfName As String
Dim oDoc As Document
Dim oMerged As Document
Dim oRng As Range
Dim iPages As Long
Dim iRecords As Long
' This tries to speed up the macro.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set oDoc = GetObject("U:\wordletter.docm")
With oDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:= _
"U:\excelsource.xlsx", _
ConfirmConversions:=False, _
ReadOnly:=True, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\excelsource.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLE", _
SQLStatement:="SELECT * FROM `Sheet1
`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute False
End With
Set oMerged = ActiveDocument
Set oRng = oMerged.Range
oRng.Start = oRng.End
iPages = oRng.Information(wdActiveEndPageNumber)
iRecords = ActiveDocument.Sections.Count - 1
' This prints to a PDF file and saves it to a designated folder.
pdfName = "U:\TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
oMerged.ExportAsFixedFormat pdfName, 17 'Path?
oMerged.Close 0
Open "U:\LogFile.txt" For Append As #1
Write #1, "Pages - " & iPages & " Records - " & iRecords ' Write data."
Close #1 ' Close file.
Documents.Open oDoc.FullName
End Sub
这篇关于Word：邮件合并以生成PDF和控制文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
