How to export Voting Results with user names and their responses

Status
Not open for further replies.

marimar02

New Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Hello,

I came across an article named "How to Quickly Export Voting Statistics from an Outlook Email to an Excel Worksheet" and it's almost what I'm looking for.

I'd like the macro to actually list the user names and/or email addresses and their responses. I realize I can do this manually but would like to automate if possible since I send emails with voting buttons daily as part of audit requirements.

Thanks much...
 
Do you have a link to the article? Manually getting the stats includes the names and responses so getting the name should not be a problem:
Diane Poremsky Yes: 1/28/2020 7:55 AM
EMO Maybe: 1/28/2020 7:57 AM
 
I wanted to test it in an easier-to-do simple list. Will write it to Excel next.

For this format:

2020-01-28_23-00-32-0000.png


I'm only getting the name right now, because with Exchange accounts, you need to look up the SMTP, else you get an ugly x500:
EMO - /o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=99b63d44b5d04d57b797537286c1e165-emo - Maybe

Using this line:
strStatus = objRecipient.Name & " - " & objRecipient.Address & " - " & objRecipient.AutoResponse & vbCrLf & strStatus

If you need the time they voted: objRecipient.TrackingStatusTime

Code:
Sub GetVoteResults()
    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 strStatus As String
Dim ListVotes As MailItem

    Set objMail = Application.ActiveExplorer.Selection(1)

    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
                 strStatus = objRecipient.Name & " - " & objRecipient.AutoResponse & vbCrLf & strStatus
           End If
        Else
           objVoteDictionary.Item("No Reply") = objVoteDictionary.Item("No Reply") + 1
          strStatus = objRecipient.Name & " - No Reply" & vbCrLf & strStatus

        End If
    Next
   
Set ListVotes = Application.CreateItem(olMailItem)
With ListVotes
.Subject = "Vote Results: " & objMail.Subject
  .Body = strStatus & vbCrLf
  .Display

End With

End Sub
 
An excel version - the code code probably be a little cleaner, but it works in my test. And there is probably a better way to grab the names and votes instead of creating a string then splitting it into rows and columns.

2020-01-29_01-40-15-0000.png


Code:
Sub ExportVotingStatisticsExcel()
    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
  Dim strStatus As String
  Dim strExcelFile As String

    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(1, 1) = "Voting Results for Email:"
         .Cells(1, 2) = objMail.Subject
         .Cells(3, 1) = "Voting Options"
         .Cells(3, 2) = "Count"
    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
               strStatus = objRecipient.Name & ";" & objRecipient.AutoResponse & vbCrLf & strStatus

           Else
              objVoteDictionary.Add objRecipient.AutoResponse, 1
              strStatus = objRecipient.Name & ";No Reply" & vbCrLf & strStatus

           End If
        Else
           objVoteDictionary.Item("No Reply") = objVoteDictionary.Item("No Reply") + 1
            strStatus = objRecipient.Name & ";No Reply" & vbCrLf & strStatus
        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
    
    nRow = nRow + 1


' split the recipients and their votes

Dim varStatus() As String
Dim varCol As Variant
Dim varRow  As Variant
Dim InxSplit As Long
Dim nCol As Long
 
 varStatus = VBA.Split(strStatus, vbLf)
 
   If UBound(varStatus) > 0 Then
    
    For InxSplit = 0 To UBound(varStatus)
    nRow = nRow + 1
    
' split each row
    varRow = VBA.Split(varStatus(InxSplit), ";")
    nCol = 1

    For Each varCol In varRow
      objExcelWorksheet.Cells(nRow, nCol).Value = varCol
      nCol = nCol + 1
    Next varCol

     Next
   End If
        

    'Save the new Excel file
    objExcelWorksheet.Columns("A:B").AutoFit
    strExcelFile = "l:\Voting Results " & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".xlsx"
    objExcelWorkbook.Close True, strExcelFile

    MsgBox "Complete!", vbExclamation
End Sub
 
Both are Perfect! Thank you Diane. I can use Outlook and Excel version for different reasons and this provides both. THANK YOU...

Lastly, is there a way to loop through all items in a specific Outlook Mail folder and grab above information in a mass? No worries if too complicated. I'm grateful about the above options.
 
Lastly, is there a way to loop through all items in a specific Outlook Mail folder and grab above information in a mass? No worries if too complicated. I'm grateful about the above options.
I don't think it will be complicated - just need to check each each message. I'll take a look at it.
 
BTW, it will be fairly easy to run it on a selection of messages that you know are voting. Checking a large folder will be slower.
 
I can work with selection of messages. That would be a better option so I don't have to separate the messages to different folders. I could just select. Is there an additional code to do so?
 
You need to add a few lines - 3 at the top, then 5 after the last of the DIM statements. At the very end, before the End Sub, add Next.
Code:
Sub GetVoteResults()
  Dim currentExplorer As Explorer
  Dim Selection As Selection
  Dim obj As Object
  
    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 strStatus As String
    Dim ListVotes As MailItem
    Dim strCounts As String

    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection

    For Each obj In Selection
        strStatus = ""
    Set objMail = obj  ' Application.ActiveExplorer.Selection(1)
 
Export the voting responses to Excel
  1. Open the original message with the voting buttons that you sent. This message is usually located in the Sent Items folder.
  2. On the Message tab, in the Show group, click Tracking.
  3. Select the responses that you want to copy.
  4. Press CTRL+C.
  5. Start Excel.
  6. Select a cell, and then press CTRL+V.
 
This is Great! THANK YOU...

I can now export to a folder and run an Excel Macro (which I'm familiar programming with) to summarize all by day onto a single sheet.

Thank you, Thank you...

You need to add a few lines - 3 at the top, then 5 after the last of the DIM statements. At the very end, before the End Sub, add Next.
Code:
Sub GetVoteResults()
  Dim currentExplorer As Explorer
  Dim Selection As Selection
  Dim obj As Object
 
    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 strStatus As String
    Dim ListVotes As MailItem
    Dim strCounts As String

    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection

    For Each obj In Selection
        strStatus = ""
    Set objMail = obj  ' Application.ActiveExplorer.Selection(1)
 
Thank you for this but I was looking for a more of an automated solution using VBA. I appreciate the response anyways.

Export the voting responses to Excel
  1. Open the original message with the voting buttons that you sent. This message is usually located in the Sent Items folder.
  2. On the Message tab, in the Show group, click Tracking.
  3. Select the responses that you want to copy.
  4. Press CTRL+C.
  5. Start Excel.
  6. Select a cell, and then press CTRL+V.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
P How to export voting results using VBA? Outlook VBA and Custom Forms 2
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
Owl Export Outlook PDF Attachment as JPG? Outlook VBA and Custom Forms 4
K Disabling import/export button to restrict PST creation Using Outlook 3
K Export Folder to PST Outlook VBA and Custom Forms 2
R Outlook 2010 How do you export 2 email Accounts' 2010 Inbox Files to new computer (2019)? Using Outlook 0
O Export Outlook calendar appointments by filters and date range Outlook VBA and Custom Forms 1
M Export-Import .pst file problems Using Outlook 2
J Export Zimbra to Outlook PST Using Outlook.com accounts in Outlook 1
S How to export urls from email to excel and check the status of the url ? Using Outlook 5
C Export NSF Lotus Notes files in Outlook PST format? Using Outlook 1
C How to export Outlook data to csv? Using Outlook 0
T How to Export mailbox from Outlook 2019 to MBOX format? Using Outlook 1
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
R How To Repair OST File & Export OST File to Outlook PST ? Using Outlook 3
JohnViuf Export task list view settings to other pc Outlook VBA and Custom Forms 16
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
N Export details to a excel spreadsheet using macros Using Outlook 0
avant-guvnor Export Calendar, Birthdays and Holidays Outlook VBA and Custom Forms 0
Diane Poremsky Export (Save) Outlook Contact photos Using Outlook 0
K Macro to search emails between two time slots on public folders with excel export Outlook VBA and Custom Forms 12
A How to export one email data to excel Using Outlook 1
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
W macro to export outlook emails to excel Outlook VBA and Custom Forms 6
E Exchanger service export detection Exchange Server Administration 1
R Correct a wrong csv export file from WLM 2012 Using Outlook 0
Q Outlook 2016\365 export specific rules to import in another system Exchange Server Administration 1
H How to export *all* fields from Outlook Using Outlook 2
K Export Organizational Forms Library Exchange Server Administration 1
N Export Outlook custom forms fields to excel Outlook VBA and Custom Forms 1
E RSS export as OPML problem Using Outlook 4
A export free / busy from exchange Exchange Server Administration 0
C Export to excel missing items, but a larger total? BCM (Business Contact Manager) 6
M Outlook 2013/365 won't export 99% of my contacts to csv Using Outlook 2
T Outlook 2007 Export tasks to Excel Using Outlook 2
J Opening/Archiving/Import/Export PST files Using Outlook 4
H Select one of Contact-Mailadesses to Export > Excel or Winword Outlook VBA and Custom Forms 2
Sarge USMC Cannot import/export Outlook 13 Using Outlook 6
L Outlook 2007 Codes Export and Import Using Outlook 9
L Outlook 2007 Toolbar Export and Import Using Outlook 7
S Requried a VBA Code to export Calander details to excel... Outlook VBA and Custom Forms 4
Jennifer Murphy Can I export individual emails? Using Outlook 1
R Export Address Book VBA Using Outlook 0
W How to export additional fields in contacts Using Outlook 1
R Recover/export rules from a backup .PST Using Outlook 2
J Formatting Contacts to Export for iPhone Using Outlook 0
B No export way Using Outlook 4
F Export Outlook contacts by One Category Using Outlook 1
Jennifer Murphy Difference between export and archive? Using Outlook 10
J How can I EXPORT my email address bok? Using Outlook 1

Similar threads

Back
Top