If you have sent an email with voting buttons, after recipients send you their replies, you may want to count and export the voting statistics to an Excel worksheet. So, in this post, we will teach you how to achieve it swiftly.
Outlook is so powerful that it even offers a “Voting” feature. My previous article “Skillful Use of MS Outlook for Voting” has taught how to utilize it effectively. Also, most of time, after voting, you may proceed to export the voting statistics to an Excel worksheet for some reasons, such as wish to print out the statistics. But Outlook doesn’t support this by default. Thus, in the followings, we will teach you how to realize this like a cork.
Export Voting Statistics in an Email to an Excel Worksheet
- At the very outset, launch your Outlook application.
- Then you ought to press “Alt + F11” key buttons in the main Outlook window.
- Next in the “Microsoft Visual Basic for Applications” window, you should enable “Microsoft Excel Object Library”. Click “Tools” > “Reference”. Then in the popup dialog box, mark the checkbox in front of the “Microsoft Excel Object Library” and hit “OK” lastly.
- After that, you can insert a new module or open a not-in-use one.
- Subsequently, copy and paste the following VBA codes into this module.
Sub ExportVotingStatistics _Excel () Dim objMail As Outlook.MailItem Dim objRecipient As Outlook.recipient Dim objVoteDictionary As Object Dim varVotingCounts As Variant Dim varVotingOptions As Variant Dim varVotingOption As Variant Dim i As Long Dim objExcelApp As Excel.Application Dim objExcelWorkbook As Excel.Workbook Dim objExcelWorksheet As Excel.Worksheet Dim nRow As Integer Set objMail = Application.ActiveExplorer.Selection(1) 'Create a new excel worksheet Set objExcelApp = CreateObject("Excel.Application") Set objExcelWorkbook = objExcelApp.Workbooks.Add Set objExcelWorksheet = objExcelWorkbook.Sheets(1) 'Fill in the predefined values With objExcelWorksheet .Cells.Font.Name = "Cambria" .Cells(1, 1) = "Voting Results for Email:" .Cells(1, 2) = Chr(34) & objMail.Subject & Chr(34) .Cells(3, 1) = "Voting Options" .Cells(3, 2) = "Voting Counts" End With Set objVoteDictionary = CreateObject("Scripting.Dictionary") 'get the default voting options varVotingOptions = Split(objMail.VotingOptions, ";") 'Add the voting responses to the dictionary For Each varVotingOption In varVotingOptions objVoteDictionary.Add varVotingOption, 0 Next 'Add a custom voting response - "No Reply" objVoteDictionary.Add "No Reply", 0 'Process the all voting responses For Each objRecipient In objMail.Recipients If objRecipient.TrackingStatus = olTrackingReplied Then If objVoteDictionary.Exists(objRecipient.AutoResponse) Then objVoteDictionary.Item(objRecipient.AutoResponse) = objVoteDictionary.Item(objRecipient.AutoResponse) + 1 Else objVoteDictionary.Add objRecipient.AutoResponse, 1 End If Else objVoteDictionary.Item("No Reply") = objVoteDictionary.Item("No Reply") + 1 End If Next 'Get the voting options and vote counts varVotingOptions = objVoteDictionary.Keys varVotingCounts = objVoteDictionary.Items 'Fill in the values in specific cells nRow = 4 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 'Save the new Excel file objExcelWorksheet.Columns("A:B").AutoFit strExcelFile = "E:\Voting Results " & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".xlsx" objExcelWorkbook.Close True, strExcelFile MsgBox "Complete!", vbExclamation End Sub
- After that, for convenient access, you had better add the new macro to Quick Access Toolbar.
- Eventually, you can have a try.
- Firstly, select a source email which contains voting buttons.
- Then click the macro button in Quick Access Toolbar.
- After this macro finishes running, you can browse to the predefined local folder, in which you will see a new Excel workbook.
- Open this Excel workbook. Now you will see the voting statistics, shown as the image below:
No One Can Avert Outlook Errors Thoroughly
Although Outlook comes endowed with a variety of capabilities, there is no such a feature to help us avoid Outlook errors. All kinds of factors may cause Outlook errors. For instance, if you often exit Outlook improperly, you’ll be likely to suffer PST corruption. In such a scenario, you have no choice but to wield some potent tools to rescue your PST file, like DataNumen Outlook Repair. It has earned a lot of kudos due to its high recovery rate.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt mdf and outlook repair software products. For more information visit www.datanumen.com
Dim objMail As Outlook.MailItem
Dim objRecipient As Outlook.recipient
getting error when run it
Error : user defined type not defined