?
Solved

Drive Mail Merge from excel Macros (using Embedded Docx Templates)

Posted on 2013-05-13
8
Medium Priority
?
1,021 Views
Last Modified: 2013-05-15
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
0
Comment
Question by:PortletPaul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39164208
In Word mail merge, the usual procedure is to create a 'main' document that contains the path to the datasource and the appropriate merge fields. There is a mail merge wizard to help you through the steps. This is then saved, so that whenever the necessary, the document can be opened and a fresh  set of letters, emails or faxes can be produced.

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(1).OLEObjects(1).Activate
    Set wdMainDoc = ActiveWorkbook.Worksheets(1).OLEObjects(1).Object
    Set wdApp = wdMainDoc.Application
   
    With wdMainDoc.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    wdMainDoc.Close wdDoNotSaveChanges
End Sub
0
 
LVL 49

Author Comment

by:PortletPaul
ID: 39164597
:) 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
0
 
LVL 49

Author Comment

by:PortletPaul
ID: 39164692
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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 39165245
The code that you have shown on the sheet expects the main documents  to be in the same folder as the workbook.

Your worksheet has two embedded workbooks and two buttons, so you could have a generic merge procedure called by individualised macros, each assigned to the appropriate button.
Sub DoMerge1 () 'assigned to Button1
     DoMerge 1
End Sub

Sub DoMerge2() 'assigned to Button2
     DoMerge 2
End Sub

Sub DoMerge(iObjNo As Integer)
    Dim wdApp As Word.Application
    Dim wdMainDoc As Word.Document
    Dim wdResultsDoc As Word.Document
    ActiveWorkbook.Worksheets(1).OLEObjects(iObjNo).Activate
    Set wdMainDoc = ActiveWorkbook.Worksheets(1).OLEObjects(iObjNo).Object
    Set wdApp = wdMainDoc.Application
   
    With wdMainDoc.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    wdMainDoc.Close wdDoNotSaveChanges
End Sub

Open in new window

0
 
LVL 49

Author Comment

by:PortletPaul
ID: 39166646
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?
0
 
LVL 49

Author Comment

by:PortletPaul
ID: 39167140
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:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28128386.html
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 total points
ID: 39167365
You have several options to match the button to the object.

As you supposed the shapes indexing, while conforming to the rule that the numbering follows the order in which the shapes were added, is not easy to manage.

You could use the shapes names or ID, but you need further coding to determine or to set them. For this it would be helpful to select the object and use
Debug.Print Selection.ShapeRange(1).Name

Another suggestion is to walk through the shapes and to get their positions to see which Word object shape is to the left of the button.
0
 
LVL 49

Author Closing Comment

by:PortletPaul
ID: 39167544
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question