How to Quickly Export Voting Statistics from an Outlook Email to an Excel Worksheet

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

1. At the very outset, launch your Outlook application.
2. Then you ought to press “Alt + F11” key buttons in the main Outlook window.
3. 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.
4. After that, you can insert a new module or open a not-in-use one.
5. 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

5. After that, for convenient access, you had better add the new macro to Quick Access Toolbar.
6. 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