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