alohc
04-09-2014, 10:27 PM
Hi there, I'm new to mail merge
Basically I'm creating an excel tool for user to generate PDF using mail merge.
However, the merged result is using the data previously saved, instead of using the user's input on the worksheet
But users are not allowed to save the file, so I cannot just do thisworkbook.save everytime before the merge.
Can anyone suggest a way out please?
Dim wdApp As Word.Application, wdDoc As Word.Document, wdOutput As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open(wdTemplatePath)
wdDoc.Activate
Dim strSQL As String strSQL = "SELECT * FROM [" & ActiveSheet.name & "$]"
wdDoc.MailMerge.MainDocumentType = wdFormLetters
wdDoc.MailMerge.OpenDataSource Name:=Thisworkbook.Fullname, LinkToSource:=True, SQLStatement:=strSQL, SQLStatement1:=""
With wdDoc.MailMerge
.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Set wdOutput = wdDoc.Application.ActiveDocument
wdOutput.ExportAsFixedFormat wdOutputPath, wdExportFormatPDF
wdDoc.Close SaveChanges:=wdDoNotSaveChanges 'Close the template
Basically I'm creating an excel tool for user to generate PDF using mail merge.
However, the merged result is using the data previously saved, instead of using the user's input on the worksheet
But users are not allowed to save the file, so I cannot just do thisworkbook.save everytime before the merge.
Can anyone suggest a way out please?
Dim wdApp As Word.Application, wdDoc As Word.Document, wdOutput As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open(wdTemplatePath)
wdDoc.Activate
Dim strSQL As String strSQL = "SELECT * FROM [" & ActiveSheet.name & "$]"
wdDoc.MailMerge.MainDocumentType = wdFormLetters
wdDoc.MailMerge.OpenDataSource Name:=Thisworkbook.Fullname, LinkToSource:=True, SQLStatement:=strSQL, SQLStatement1:=""
With wdDoc.MailMerge
.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Set wdOutput = wdDoc.Application.ActiveDocument
wdOutput.ExportAsFixedFormat wdOutputPath, wdExportFormatPDF
wdDoc.Close SaveChanges:=wdDoNotSaveChanges 'Close the template