In Outlook, after receiving related voting responses from the recipients of a voting email, you may wish to create a pie chart for the voting statistics and insert it into the original email. This post will help you to achieve it with Outlook VBA.
After reading the article “How to Quickly Export Voting Statistics from an Outlook Email to an Excel Worksheet“, you may come up with some other ideas, such as creating a pie chart for the voting statistics, which will be more Intuitive than the statistics. In response to this requirement, here we’ll introduce you a quick way to get it.
Create & Insert the Pie Chart of Voting Statistics into the Original Email
- For a start, access Outlook VBA editor according to the article – “How to Run VBA Code in Your Outlook“.
- Then, in the “Microsoft Visual Basic for Applications” window, copy the VBA code below into a project or module.
Sub CreateInsertPieChart_EmailVotingStatistics() Dim objMail As Outlook.MailItem Dim objRecipient As Outlook.Recipient Dim objDictionary As Object Dim varVotingCounts As Variant Dim varVotingOptions As Variant Dim i As Long Dim objExcelApp As Object Dim objExcelWorkbook As Object Dim objExcelWorksheet As Object Dim nRow As Integer Dim strChartImage As String Dim objAttachImage As Outlook.Attachment 'Get the source email Set objMail = Application.ActiveInspector.CurrentItem 'Export the voting statistics into Excel Set objDictionary = CreateObject("Scripting.Dictionary") varVotingOptions = Split(objMail.VotingOptions, ";") For Each varVotingOption In varVotingOptions objDictionary.Add varVotingOption, 0 Next objDictionary.Add "No Reply", 0 For Each objRecipient In objMail.Recipients If objRecipient.TrackingStatus = olTrackingReplied Then If objDictionary.Exists(objRecipient.AutoResponse) Then objDictionary.Item(objRecipient.AutoResponse) = objDictionary.Item(objRecipient.AutoResponse) + 1 Else objDictionary.Add objRecipient.AutoResponse, 1 End If Else objDictionary.Item("No Reply") = objDictionary.Item("No Reply") + 1 End If Next varVotingOptions = objDictionary.Keys varVotingCounts = objDictionary.Items Set objExcelApp = CreateObject("Excel.Application") Set objExcelWorkbook = objExcelApp.Workbooks.Add Set objExcelWorksheet = objExcelWorkbook.Sheets(1) objExcelApp.Visible = True objExcelWorksheet.Activate nRow = 1 For i = LBound(varVotingOptions) To UBound(varVotingOptions) With objExcelWorksheet .Cells(nRow, 1) = varVotingOptions(i) .Cells(nRow, 2) = varVotingCounts(i) End With nRow = nRow + 1 Next 'Create the pie chart for voting statistics strChartImage = "E:\Voting Pie Chart.jpg" objExcelWorksheet.UsedRange.Select objExcelWorksheet.Shapes.AddChart(xlPie).Select ActiveChart.SetSourceData Source:=ActiveSheet.UsedRange ActiveChart.Export strChartImage, "JPG" 'Insert the pie chart image into the email objMail.Attachments.Add strChartImage Set objAttachImage = objMail.Attachments.Add(strChartImage) objAttachImage.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001E", "myident" objMail.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/id/{00062008-0000-0000-C000-000000000046}/8514000B", True objMail.HTMLBody = "<HTML>" & "<head>" & "</head>" & "<BODY>" & "Voting Statistics:" & "<br /><img align=baseline border=1 hspace=0 src=cid:myident width='400'/>" & "</BODY></HTML>" & objMail.HTMLBody objExcelWorkbook.Close False objExcelApp.Quit Kill strChartImage End Sub
- After that, close the current window.
- Subsequently, add the macro to Quick Access Toolbar.
- Eventually, you can try this macro right now.
- First off, select the source voting email.
- Then, hit the macro button in the Quick Access Toolbar.
- Lastly, when the macro completes, you will see that a pie chart has been inserted into the email body, like the following image.
Deal with Occasional Outlook Errors
Though Outlook is feature rich, it still cannot be exempt from errors and damage. In face of vexing troubles in Outlook, you must be going to make use of a variety of solutions to get rid of them. For instance, you can first utilize the inbuilt tool to try PST fix. Also, if you’ve kept a powerful external tool, like DataNumen OUltook repair, it would be your last resort.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf repair and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply