PortletPaul
asked on
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.
Thanks.
ps: I’ve not used VBA for a long while, but it’ll come back to me I’m assuming :)
Make-Docs-Embedded-Templates-Fro.xlsx
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.
Thanks.
ps: I’ve not used VBA for a long while, but it’ll come back to me I’m assuming :)
Make-Docs-Embedded-Templates-Fro.xlsx
ASKER
:) yes I know the usual method is start with Word, then Excel as data source
that's why I'm asking if the reverse is feasible
Neither of the embedded docs in the sample are "setup" - it's conceptual
I want to operate this way as there will be several (simple) word templates, but only one (complex) workbook: the information will come from the workbook so the workflow is more logically controlled in the workbook. Plus I need to distribute this - it will be far easier if all parts are in one file instead of something like a zipped folder (although if that's the best route please tell me so).
You can assume the word templates would have merge tags in them, what I'm really after is:
how to 'unhook' the embedded document to file, then
get that file to start "mail merge"
this looks hopeful
http://stackoverflow.com/questions/1357121/executing-word-mail-merge
that's why I'm asking if the reverse is feasible
Neither of the embedded docs in the sample are "setup" - it's conceptual
I want to operate this way as there will be several (simple) word templates, but only one (complex) workbook: the information will come from the workbook so the workflow is more logically controlled in the workbook. Plus I need to distribute this - it will be far easier if all parts are in one file instead of something like a zipped folder (although if that's the best route please tell me so).
You can assume the word templates would have merge tags in them, what I'm really after is:
how to 'unhook' the embedded document to file, then
get that file to start "mail merge"
this looks hopeful
http://stackoverflow.com/questions/1357121/executing-word-mail-merge
ASKER
a new file - now one template has merge tags in it
I would like to achieve this with buttons if possible, each button must invoke a different embedded document
(double-clicking on a document instead might work perhaps, less desirable)
Make-Docs-From-Embedded-Template.xlsx
I would like to achieve this with buttons if possible, each button must invoke a different embedded document
(double-clicking on a document instead might work perhaps, less desirable)
Make-Docs-From-Embedded-Template.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent, this is looking quite promising, just another dumb question or so:
how do I know which document is 1, 2 , 3 etc
i.e. is it determined the sequence I embed the documents?
what happens if I add (say) 5 documents, then remove the third, and add a replacement?
will the replacement be 3 or 6?
is referring to these by number the only way?
i.e. is it determined the sequence I embed the documents?
what happens if I add (say) 5 documents, then remove the third, and add a replacement?
will the replacement be 3 or 6?
is referring to these by number the only way?
ASKER
The macros provided are doing fine so far - thanks you - I'd just like to know a wee bit more about referencing those embedded objects (see above)
follow on question if you are interested:
https://www.experts-exchange.com/questions/28128386/Mail-Merge-Generated-Document-How-to-keep-field-in-a-paragraph.html
follow on question if you are interested:
https://www.experts-exchange.com/questions/28128386/Mail-Merge-Generated-Document-How-to-keep-field-in-a-paragraph.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Have to fine some method to 'name' these objects I suspect
even a list of them would be a good start
Regardless, thank you for the great start here - it's looking promising.
even a list of them would be a good start
Regardless, thank you for the great start here - it's looking promising.
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.
Sub DoMerge()
Dim wdApp As Word.Application
Dim wdMainDoc As Word.Document
Dim wdResultsDoc As Word.Document
ActiveWorkbook.Worksheets(
Set wdMainDoc = ActiveWorkbook.Worksheets(
Set wdApp = wdMainDoc.Application
With wdMainDoc.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
wdMainDoc.Close wdDoNotSaveChanges
End Sub