Creating Unique Pie Charts In Mail Merge Document

Hello,
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
zgrrlAsked:
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.

GrahamSkanRetiredCommented:
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?
0
zgrrlAuthor Commented:
Hi GrahamSkan,

Here is the info I found from Cindy at this url http://homepage.swissonline.ch/cindymeister/MergFram.htm

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 http://www.knowhow.com/Guides/DatabaseInfo/DatabaseInfo.htm). 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 MrgChart.zip 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!

THanks,
Diane
0
GrahamSkanRetiredCommented:
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.
MailMergePieChartLetter.doc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
FWIW,

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.

;-)

JeffCoachman


Access-EEQ26651706MailMergeRepor.mdb
0

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
0
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.

http://www.helenfeddema.com/Files/accarch191.zip
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, _
         Title:=strTitle
      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, _
         autostart:=False
      
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
         .Send
      End With
   
NextEmployee:
      rstEmployees.MoveNext
   Loop
   
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

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

Open in new window

0
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
0
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
 dummydata-test-micrographs.xls
0
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.

JeffCoachman

0
GrahamSkanRetiredCommented:
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.

{ INCLUDEPICTURE { MAILMERGE CharTest } }

The other problem is that Publisher doesn't have such a field.
0
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
0
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"

;-)

Jeff
0
zgrrlAuthor Commented:
Jeff,
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

0
zgrrlAuthor Commented:
Thanks everyone for helping out with this! I learned much!
- Diane
0
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!
0
Danyell AlmanzaCommented:
Interesting post . BTW , if someone was looking for a permit search , my family filled a template document here "<a>https://goo.gl/ZpmKBs</a>".
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.