Creating Unique Pie Charts In Mail Merge Document

In doing some quick searches here, it appears this question has come up however, still not seeing a definitive answer. Here is what I am trying to do. I have a large data file (approx 1500 rows of data). I want to merge parts of this
data into a mail merge document to make individualized statements. Some of the data needs to create unique pie chart for each data row. How is this best completed? I keep finding info pointing to Cindy Meister online, however, I am not versed in VBA and it seems like there are pieces of the process left out for us newbies.  Any help much appreciated. Seems like it would be a common request to do something like this...  Is this something more easily done with Access? Or will excel data and word (or even Publisher) work?
- Diane
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I can believe that it would require some (perhaps a lot of) VBA. If anyone knows then Cindy does. Can you post the link with the VBA that you need to understand?
zgrrlAuthor Commented:
Hi GrahamSkan,

Here is the info I found from Cindy at this url

Word mail merge doesn't provide any way to generate a chart for each record in a mail merge. There are four basic approaches you can use to create charts for mail merge:

    * Create a chart for each record in Excel. Add a column to the data table and enter the name of the appropriate chart for each record. Use this merge field in LINK field in the mail merge document.
    * Use a Database field in the main merge document to create a data table for each merge record (for details, see Select the table and link it to an MS Graph. Preview the merge data, one record at a time, and print as you go. (Executing the merge would remove the bookmark that links the table to the chart, so you'd get the same chart for all records.)
    * Create the chart for each record in the mail merge result document, after the mail merge has executed.
    * Create the charts on-the-fly, as the mail merge executes, in the main merge document.

The first three can be done manually, and are possible in all versions of Word; using VBA (macros) would make the process much faster, of course.

The fourth method relies on the Mail Merge Events introduced in Word 2002 to manipulate the main merge document as each record is merged. The sample files in provided here uses this method, automating an MS Graph object embedded in the main merge document. You can certainly derive the code necessary automating for the third method based on the code in this sample, as all the basics required for automating MS Graph are present.

I downloaded the zip files she provides. I read the directions file. When I open up the text main document, I see that it grabs the first record info to make the piechart, but I don't know where I would get any VBA code to make this continue for the additional records. I feel I must be missing something, perhaps staring me in the face!

I have tweaked the main document to remove all the fields and insert one: <<Employee>>. I have also put a bit of code in to call the ActivateEvents Sub.

You should now be able to set the 'recipients' list to the document with the table so that the merge source and the chart data are the same.

This is just a demo, but it should show what is possible.

I don't see any reason why you couldn't use Excel or Access as the Datasource, though the code to get the chart data would need modification.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:

Since you did not post any info on your source date, or the exact structure of the mail Merge (Report) here is a basic sample of how this can all be done in MS Access.

As almost any Expert will tell you, there are many way to do this depending on your exact needs.




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zgrrlAuthor Commented:
Thanks guys. I will check this out tonight and write back with any questions/comments. Appreciate it!
- Diane
Helen FeddemaCommented:
Another approach is to do the pie charts in Access (as PivotCharts), then output a custom report including the pie chart for each record in a recordset, using a variation of the code below, and (if you have Access 2007 or higher) save each custom report to a PDF file.  If you are interested in this approach, see my Access Archon #191, which includes a sample database.
Public Sub SendPDFEmails()
'Created by Helen Feddema 24-Jan-2010
'Last modified by Helen Feddema 24-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngEmployeeCount As Long
   Dim lngID As Long
   Dim msg As Outlook.MailItem
   Dim rpt As Access.Report
   Dim rstEmployees As DAO.Recordset
   Dim strAttachmentsPath As String
   Dim strBody As String
   Dim strEmployeeName As String
   Dim strEMailAddress As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReportFile As String
   Dim strReportName As String
   Dim strSQL As String
   Dim strSubject As String
   Dim strTitle As String
   strAttachmentsPath = GetProperty("AttachmentsPath", "") & "\"
   strSubject = GetProperty("MessageSubject", "Your custom report")
   strBody = GetProperty("MessageBody", "Your current report is attached as a PDF")
   strReportName = "rptEmployeeInvoices"
   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset("qryEMailEmployees")
   lngEmployeeCount = rstEmployees.RecordCount
   Debug.Print lngEmployeeCount & " employees need reports"

   If lngEmployeeCount = 0 Then
      strTitle = "No reports to send"
      strPrompt = "No employees need reports; canceling"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   Do While Not rstEmployees.EOF
      lngID = rstEmployees![EmployeeID]
      strEmployeeName = rstEmployees![Salesperson]
      strEMailAddress = rstEmployees![Email]
      strReportFile = strAttachmentsPath & "Employee Invoices" _
         & " for " & strEmployeeName & ".pdf"
      Debug.Print "PDF save name and path: " & strReportFile
      'Create filtered query as report record source
      strRecordSource = "qryInvoices"
      strQuery = "qryInvoicesPerEmployee"
      If lngID <> 0 Then
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[EmployeeID] = " & lngID & ";"
      End If
      Debug.Print "SQL for " & strQuery & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuery, strSQL)
      'Output customized report to PDF
      DoCmd.OutputTo objecttype:=acOutputReport, _
         objectname:=strReportName, _
         outputformat:=acFormatPDF, _
         outputfile:=strReportFile, _
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
      End With
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendPDFEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   Dim qdf As DAO.QueryDef
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window

zgrrlAuthor Commented:
Helen, just to clarify, are you saying that I would have to save each document with unique chart as a PDF? If so, that would not be practical for 1500 statements. I'm I understanding you correctly?

- Diane
zgrrlAuthor Commented:
Hello All,

I have kinda gone down another road to see if this can be done differently and now have another question. I've used an add-in called MicroCharts that creates in cell graphs for each record. This has a feature to convert the graphs to pictures. So now, I have a nice little unique pie chart for each record.
Now I have made a document in Publisher and want to merge this field (the field containing the unique pie chart) into my publisher document. Seems like almost there... however, Publisher does not "see" there is any content in the merge field (the cell I'm referencing from excel). I'm thinking there is a step I'm missing in excel to let it now that the cell contains the image/picture, but that's just a guess.

Any ideas here? Or should this be posted as new question? I'm attaching my excel file so you can take a look. This is after the microcharts have been converted to images.
- Diane
Jeffrey CoachmanMIS LiasonCommented:
If you have "gone down another road", then AFAICT, you have rendered your original question invalid.

It would not be so bad if you had at least replied to each proposal with why it was not viable.


You now have a couple of new problems.

In Word, you would need to use the IncludePicture field, and that expects a picture file, not a part of a spreadsheet. You would have to save the images as separate files and put their addresses in their place.

Expanded, the field codes would look like this.


The other problem is that Publisher doesn't have such a field.
zgrrlAuthor Commented:
Hi All,

Thanks for all your comments and suggestions. All of them have been useful in discovering what I can and can't do -- will be testing MS Access approach today and will respond to all as suggested by JeffCoachman above.

 - Diane
Jeffrey CoachmanMIS LiasonCommented:
Yes, not that I thought that my proposal was best, it was just that you had not really replied to: Helen, Graham, or me with what you thought of our suggestions, before you decided to: "go down another road"


zgrrlAuthor Commented:
Yes - am going to get back to all. Will experiment with all proposed. Awaiting getting updated version of MS Access on my machine....  in the meantime I was trying all kinds of things!
- Diane

zgrrlAuthor Commented:
Thanks everyone for helping out with this! I learned much!
- Diane
Helen FeddemaCommented:
There are always many ways to do things in Office, at least since all major Office components got object models that can be manipulated in VBA code.  I agree that generating 1500 PDFs would not be practical!
Danyell AlmanzaCommented:
Interesting post . BTW , if someone was looking for a permit search , my family filled a template document here "<a></a>".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.