Mail Merge from Excel active sheet without saving workbook

[alohc]

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

[macropod]

A mailmerge can only work with a saved file. If you don't want to do that, you'll need to roll your own process that populates a document (mergefields could be used as placeholders) directly from Excel, not using mailmerge.

[snb]

If the users are not allowed to save a workbook, the code in the worbook could save a copy of the workbook using Thisworkbook.SavecopyAs
That workbook can be linked to a main mailmerge document.