Drive Mail Merge from excel Macros (using Embedded Docx Templates)
I have an idea for a productivity aid which involves embedding Word (2010) Documents into Excel (2010), then behind a macro button the macro chooses the relevant Word Template and proceeds to populate it with data.
Basically I want to drive Word’s “mail merge” from Excel so that the correct Word templates are retained in a single Excel workbook for distribution.
Is this possible?
Can someone point me to (or provide) a sample macro that will grab an embedded document, then start a “mail merge” using that document as the format template.
Conceptual only Excel workbook attached. Assuming what I’m after is possible I’m not after a fully developed macro, just a stub to show how I would get started.
This code anticipates this, though I notice that the embedded documents aren't actually set up as merge documents, so the code aborts when the MailMerge bit is reached.
Dim wdApp As Word.Application
Dim wdMainDoc As Word.Document
Dim wdResultsDoc As Word.Document
Set wdMainDoc = ActiveWorkbook.Worksheets(
Set wdApp = wdMainDoc.Application
.Destination = wdSendToNewDocument