Tuesday, September 16, 2008

Outlook Meeting Tracking Export

Recently I was planning an event using my Outlook calendar at work. I needed to share the list of users who had accepted the outlook invitation with my fellow event planners. Unfortunately I couldn't find a way to do this in outlook without taking screen shots of the list

I found a website where someone had written a macro to export the list in word.
http://www.outlookcode.com/codedetail.aspx?id=37

The code didn't work in Office 2007 so I tweaked it and decided to take it one step further. I set it to export my list to Excel, instead of word, where I could quickly sort by name or acceptance.

If you have never created a macro before this may be a little intimidating but here's some easy instructions I got from Google Groups

  1. Copy the code below

  2. Open Outlook

  3. Press your <ALT>+<F11> (This opens the Microsoft Visual Basic - VbaProject.OTM)

  4. On the menu across the top select "Insert"

  5. Select "Module"

  6. In the blank window on the right, PASTE the code you copied

  7. Select "File"

  8. Select "Close and Return to Microsoft Outlook"

  9. Right click on your Tool menu (in the gray part where the buttons are)

  10. Select "Customize..."

  11. Click on the "Commands" tab

  12. Select "Macros" from the Categories

  13. Click on "Project1.PrintAapptAttendee" and drag it to your toolbar

    and Drop it (You have to drop it ON the toolbar not in the gray

    blank area but next to something like the yellow question mark icon)

  14. Close the Customize window.

Now try to use this "Macro"

  1. Open an appointment that has attendees in it

  2. Click on the "Project1.PrintAapptAttendee" button that you put on your toolbar.

  3. Wait a moment for Word to open and show you your appointment with all of the attendees and how they responded to the invitation



Code:



Sub PrintAapptAttendee()

' Gather data from an opened appointment and print to
' Excel. This provides a way to print the attendee list with their
' response, which Outlook will not do on its own.

' Set up Outlook

Dim objApp As Outlook.Application
Dim objItem As Object
Dim objSelection As Selection
Dim objAttendees As Outlook.Recipients
Dim objAttendeeReq As String
Dim objAttendeeOpt As String
Dim objOrganizer As String
Dim dtStart As Date
Dim dtEnd As Date
Dim strSubject As String
Dim strLocation As String
Dim strNotes As String
Dim strMeetStatus As String
Dim strUnderline As String ' Horizontal divider line


' Set up Excel

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add


'Create the header and set the background color as yellow

objExcel.Cells(1, 1).Value = "Attendee"
objExcel.Cells(1, 1).Interior.ColorIndex = 6
objExcel.Cells(1, 2).Value = "Response"
objExcel.Cells(1, 2).Interior.ColorIndex = 6


objExcel.Cells(1, 3).Value = "Req/Opt"
objExcel.Cells(1, 3).Interior.ColorIndex = 6

On Error Resume Next


Set objApp = CreateObject("Outlook.Application")
Set objItem = objApp.ActiveInspector.CurrentItem
Set objSelection = objApp.ActiveExplorer.Selection
Set objAttendees = objItem.Recipients
Set objExcel = GetObject(, "Excel.Application")

If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
End If

strUnderline = String(60, "_") ' use 60 underline characters

On Error GoTo EndClean:


' check for user problems with none or too many items open
Select Case objSelection.Count

Case 0
MsgBox "No appointment was opened. Please opten the appointment to print."
GoTo EndClean:

Case Is > 1
MsgBox "Too many items were selected. Just select one!!!"
GoTo EndClean:
End Select


' Is it an appointment

If objItem.Class <> 26 Then
MsgBox "You First Need To open The Appointment to Print."
GoTo EndClean:
End If


' Get the data

dtStart = objItem.Start
dtEnd = objItem.End
strSubject = objItem.Subject
strLocation = objItem.Location
strNotes = objItem.Body
objOrganizer = objItem.Organizer
objAttendeeReq = ""
objAttendeeOpt = ""

' Get The Attendee List

For x = 1 To objAttendees.Count

strMeetStatus = ""

Select Case objAttendees(x).MeetingResponseStatus

Case 0
strMeetStatus = "No Response"

Case 1
strMeetStatus = "Organizer"

Case 2
strMeetStatus = "Tentative"

Case 3
strMeetStatus = "Accepted"

Case 4
strMeetStatus = "Declined"

End Select

If objAttendees(x).Type = olRequired Then

objExcel.Cells(x + 1, 1).Value = objAttendees(x).Name
objExcel.Cells(x + 1, 2).Value = strMeetStatus
objExcel.Cells(x + 1, 3).Value = "Required"

Else

objExcel.Cells(x + 1, 1).Value = objAttendees(x).Name
objExcel.Cells(x + 1, 2).Value = strMeetStatus
objExcel.Cells(x + 1, 3).Value = "Optional"

End If
RowCount = RowCount + 1
Next


'Sort Worksheet

objExcel.Worksheets("Sheet1").Range("B2").Sort _
Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
Header:=xlGuess

objExcel.Worksheets("Sheet1").Range("A2").Sort _
Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
Header:=xlGuess

objExcel.Worksheets("Sheet1").Range("C2").Sort _
Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
Header:=xlGuess


EndClean:

Set objApp = Nothing
Set objItem = Nothing
Set objSelection = Nothing
Set objAttendees = Nothing
Set objExcel = Nothing
Set objxls = Nothing
Set excelRng = Nothing
Set wordPara = Nothing


End Sub



---->UPDATE (9/19/08): Please note you are using this macro at your own risk.

Many people were complaining that they could only run the item once and then it stopped working. I did some research and it turn outs that Outlook disables all "untrusted" Macros.

To get this to run more than once you will need to go to:

Tools > Trust Center and Macro Security > and change the radio button to "Warning for all macros"

You will need to close outlook completely (make sure it's not running as a background task) then reopen it.

You will receive a warning each time you run this macro but it should still work.

118 comments:

  1. This worked Great, thanks!!!!!!!!!

    ReplyDelete
    Replies
    1. It does and I love this!!! Thank you so much!!! Just used it and it STILL Performs well.

      Delete
  2. I used the script, but it only worked one time. I had to delete the macro and reload it for it to work again. Any suggestions?

    ReplyDelete
  3. This worked very well for me, too, but for one user who has a longer list of attendees, it worked the first time, then stalled out on the second try. We closed Excel, closed Outlook, even tried to delete the macro and reinstall it, but we get no more results on this user's machine. It's as if it was good for one application only. Any ideas?

    ReplyDelete
  4. We're using Outlook 2007 and ran this macro. It worked perfectly, then ran it again. Nothing happened. We closed Outlook, Excel and reinstalled the macro. Then the Tracking option disappeared from the original calendar appointment! Help! We can't seem to get it back.

    ReplyDelete
  5. Works beautifully for me time after time. I love you!

    ReplyDelete
  6. It does work consistently; however, in Outlook 2007, there are a few steps missing. When you open the appointment, you have to click on the tracking tab, then press ALT+F8 and run the macro. WORKS EVERYTIME! Promise!

    ReplyDelete
  7. It doesn't seem to work for me. the Excel opens but it doesn't populate anything. My macro security is set to medium. what else can i do?

    ReplyDelete
  8. The code does not work for me. It opens Excel and populates three headers [Attendee, Response, Req/Opt] with highlighted background, but that is it. There is no further data. I am working in Outlook 2003. Any suggestions please?

    ReplyDelete
  9. You are such a life saver!!! Thank you so much!!!

    ReplyDelete
  10. Thanks guy...
    This is really a great piece!

    ReplyDelete
  11. This is fabulous, thank you!!

    ReplyDelete
  12. Thank you - this is very useful!

    ReplyDelete
  13. Thank you so much. I needed to get the email addresses as well. I modified the script to do so:

    ' . . . rest of file above

    objExcel.Cells(x + 1, 1).Value = objAttendees(x).Name
    objExcel.Cells(x + 1, 2).Value = strMeetStatus
    objExcel.Cells(x + 1, 3).Value = "Optional"

    End If

    'New line to add fourth column with email addresses.
    objExcel.Cells(x + 1, 4).Value = objAttendees(x).Address


    RowCount = RowCount + 1
    Next

    'File continues below . . .

    It works pretty well, except that internal attendees have Microsoft's internal email address format (X.400?) rather than SMTP styles addresses.

    ReplyDelete
  14. Brilliant! Wish there was a way to create a Macro button to run it from an appointment without having to use Atl F8

    ReplyDelete
  15. This worked very well! Thanks, you saved me several hours work.

    ReplyDelete
  16. Thanks, it worked for me, even if it put the headers on one page and the results in another one. I guess that's because I had Excel open before running the macro. I tried closing Excel, running the macro again and it would then put everything as expected (Headers + Data in same book). THANK YOU!

    ReplyDelete
  17. in outlook 2003 it works fine for me if i dont have excel open already. would be great if you could leave current work open. If anybody has a fix to that please post

    ReplyDelete
  18. Im on Off2007, works well provided that you dont have Excel previously open.

    ReplyDelete
  19. Hello,
    first of all - i'm not a programmer - i can only Google :-)
    for me the original script didn't work - excel workbook was created but never filled in any data.
    I did some research and found, that maybe the focus on the excel file was lost while the script is running and therefore no data could be added.
    I did some changes:
    - Add some lines to have dedicated "sheet" values
    - Add some lines to see if the person is an Active Directory account or has an external SMTP address
    Note: For me it was usefull to have also the SMPT address within the Excel sheet so i can match another list
    - Add a function to get the SMTP address out of the Active Directory via LDAP by the legacyExchangeDN

    I hope this script will help others - the original one was a big step into solving my problem.
    I tested the script with OL2003 - no guarantee that it will work and try it on your own risk!!!!
    ############################################################

    Sub PrintAapptAttendee()

    ' Gather data from an opened appointment and print to
    ' Excel. This provides a way to print the attendee list with their
    ' response, which Outlook will not do on its own.

    ' Set up Outlook

    Dim objApp As Outlook.Application
    Dim objItem As Object
    Dim objSelection As Selection
    Dim objAttendees As Outlook.Recipients
    Dim objAttendeeReq As String
    Dim objAttendeeOpt As String
    Dim objOrganizer As String
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim strSubject As String
    Dim strLocation As String
    Dim strNotes As String
    Dim strMeetStatus As String
    Dim strUnderline As String ' Horizontal divider line
    Dim objExcelApp
    Dim objExcelBook
    Dim objExcelSheets
    Dim objExcelSheet
    Dim objExcelRange
    Dim oExcel As Excel.Application
    Dim owb As Excel.Workbook
    Dim oWS As Excel.Worksheet
    Dim var As String
    Dim SMTPAdr

    ' Set up Excel

    Set objExcel = CreateObject("Excel.Application")
    Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set owb = oExcel.Workbooks.Add
    Set oWS = owb.Worksheets("Sheet1")


    'Create the header and set the background color as yellow

    oWS.Cells(1, 1).Value = "Attendee"
    oWS.Cells(1, 1).Interior.ColorIndex = 6
    oWS.Cells(1, 2).Value = "Response"
    oWS.Cells(1, 2).Interior.ColorIndex = 6

    oWS.Cells(1, 3).Value = "Req/Opt"
    oWS.Cells(1, 3).Interior.ColorIndex = 6

    oWS.Cells(1, 4).Value = "SMTP"
    oWS.Cells(1, 4).Interior.ColorIndex = 6

    On Error Resume Next

    Set objApp = CreateObject("Outlook.Application")
    Set objItem = objApp.ActiveInspector.CurrentItem
    Set objSelection = objApp.ActiveExplorer.Selection
    Set objAttendees = objItem.Recipients
    Set objExcel = GetObject(, "Excel.Application")

    If objExcel Is Nothing Then
    Set objExcel = CreateObject("Excel.Application")
    End If

    strUnderline = String(60, "_") ' use 60 underline characters

    On Error GoTo EndClean:

    ' check for user problems with none or too many items open
    Select Case objSelection.Count

    Case 0
    MsgBox "No appointment was opened. Please opten the appointment to print."
    GoTo EndClean:

    Case Is > 1
    MsgBox "Too many items were selected. Just select one!!!"
    GoTo EndClean:
    End Select

    ' Is it an appointment

    If objItem.Class <> 26 Then
    MsgBox "You First Need To open The Appointment to Print."
    GoTo EndClean:
    End If

    ' Get the data

    dtStart = objItem.Start
    dtEnd = objItem.End
    strSubject = objItem.Subject
    strLocation = objItem.Location
    strNotes = objItem.Body
    objOrganizer = objItem.Organizer
    objAttendeeReq = ""
    objAttendeeOpt = ""

    ' Get The Attendee List

    For x = 1 To objAttendees.Count

    strMeetStatus = ""

    Select Case objAttendees(x).MeetingResponseStatus

    Case 0
    strMeetStatus = "No Response"

    Case 1
    strMeetStatus = "Organizer"

    Case 2
    strMeetStatus = "Tentative"

    Case 3
    strMeetStatus = "Accepted"

    Case 4
    strMeetStatus = "Declined"

    End Select

    If objAttendees(x).Type = olRequired Then

    var = objAttendees(x).Address
    oWS.Cells(x + 1, 1).Value = objAttendees(x).Name
    oWS.Cells(x + 1, 2).Value = strMeetStatus
    oWS.Cells(x + 1, 3).Value = "Required"

    SMTPAdr = InStr(1, objAttendees(x).Name, "@", 1)
    If SMTPAdr = False Then
    var = getMail(var)
    End If

    oWS.Cells(x + 1, 4).Value = var

    Else

    var = objAttendees(x).Address
    oWS.Cells(x + 1, 1).Value = objAttendees(x).Name
    oWS.Cells(x + 1, 2).Value = strMeetStatus
    oWS.Cells(x + 1, 3).Value = "Optional"

    SMTPAdr = InStr(1, objAttendees(x).Name, "@", 1)
    If SMTPAdr = False Then
    var = getMail(var)
    End If
    oWS.Cells(x + 1, 4).Value = var

    End If
    RowCount = RowCount + 1
    Next

    'Sort Worksheet

    objExcel.Worksheets("Sheet1").Range("B2").Sort _
    Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
    Header:=xlGuess

    objExcel.Worksheets("Sheet1").Range("A2").Sort _
    Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
    Header:=xlGuess

    objExcel.Worksheets("Sheet1").Range("C2").Sort _
    Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
    Header:=xlGuess

    EndClean:

    Set objApp = Nothing
    Set objItem = Nothing
    Set objSelection = Nothing
    Set objAttendees = Nothing
    Set objExcel = Nothing
    Set objxls = Nothing
    Set excelRng = Nothing
    Set wordPara = Nothing

    End Sub



    'This function matches the legacyExchangeDN to the corresponding SMTP E-Mail address from AD is existing
    Function getMail(legacyEDN As String)

    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory domain.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")

    'Note: Please remove the ! with < and ? with > within the next line!!!!
    strBase = "!GC://DC=Your-Domain, DC=net?"

    ' Filter on user objects.
    strFilter = "(&(objectCategory=person)(objectClass=user)(legacyExchangeDN=" & legacyEDN & "))"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName,cn,DistinguishedName,displayName,mail"

    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute
    valueResult = adoRecordset.RecordCount

    Set adoRecordset = adoCommand.Execute


    If valueResult = 0 Then
    strMail = "no SMTP-address found"
    End If
    If valueResult = 1 Then
    strMail = adoRecordset.Fields("mail").Value
    End If
    If valueResult > 1 Then
    ' If valueResult > 1 Then
    ' ' Enumerate the resulting recordset.
    ' Do Until adoRecordset.EOF
    ' ' Retrieve values and display.
    ' If (InStr(1, Header, adoRecordset.Fields("mail").Value, vbBinaryCompare)) Then
    ' strMail = adoRecordset.Fields("mail").Value
    ' Else
    ' strMail = "no unique SMTP-address found"
    ' End If
    ' 'Move to the next record in the recordset.
    ' adoRecordset.MoveNext
    ' Loop
    'End If
    strMail = "Display name not unique"
    End If

    getMail = strMail

    ' Clean up.
    adoRecordset.Close
    adoConnection.Close
    End Function

    ReplyDelete
  20. Thank you so much... this is a fantastic tool and has saved me so many hours! You're a doll, thanks.

    ReplyDelete
  21. brilliant, thank you! -shawn

    ReplyDelete
  22. Ummm... I can't get the Excel version to get past this line:

    Dim oExcel As Excel.Application

    What do I need to do to make this run?

    Thanks!
    Des

    ReplyDelete
  23. I as well can not get past the Excel "Dim oExcel As Excel.Application" section of the code. The original code at the top of this thread worked until the acceptance list was too large, so it timed out. So I tried the last code to add all the other features as well, and now I am stuck.

    ReplyDelete
  24. Worked great! Thank you so much for making a daunting task easy.

    ReplyDelete
  25. In one site I found nice tool-viewer pst file,
    it has many features and as far as I know has free status,program helped me many times,it will help you to restore your data from files with *.pst and *.ost extension,tool will work under all supported versions of Microsoft Windows operating system, as well as with Microsoft Outlook,can retrieve all contents as a number of files in *.vcf, *.txt and *.eml formats,will extract thousands of different files, that will be placed into any folder, upon your choice,converting of recovered data into a *.pst file, that can be opened by any mail client or viewer .pst file, compatible with Microsoft Outlook, file size will not exceed 1Gb.

    ReplyDelete
  26. Yesterday I received some mails and I was afraid because it were damaged.But to my surprise friend recommended next application-outlook recovery software.It helped me very fast and it is free as far as I know,moreover utility save the recovered information as files with the *.pst extension.

    ReplyDelete
  27. It looks good but it only pastes the headings into excel not the meeting attendees and their responses - Any ideas what I need to change to get that info?

    ReplyDelete
  28. My collegues and I we thought that it would be useful to have the tracking information right into the body of the appointment, so when you print the appointment you can print also the tracking info.
    The easiest way to go would be to put the macro into a module, then add a button on the stardard toolbar. This macro adds the tracking info to the appointment's body. Then you can save the info inside the body or not. Also you can delete this info at a later time.

    Have fun with it!

    The code is in another post because of the lenght limitation. Sorry!

    ReplyDelete
  29. Part 1:
    Sub SendAppointmentResponsesToBody()

    ' usage: open an appointment from your calender and run the script - it will collect tracking information
    ' and append it to the appointment's body allowing the user to print / save it.

    Dim myStatus As String, myName As String, myVersion As String
    myName = "SendAppointmentResponsesToBody"
    myVersion = "v.1.0.0.1" '<<------increment here
    myCopyright = "©2009 Freeware"
    ' initialize
    Dim objApp As Outlook.Application
    Set objApp = CreateObject("Outlook.Application")
    Dim objItem As Object
    Set objItem = objApp.ActiveInspector.CurrentItem
    Dim objSelection As Selection
    Set objSelection = objApp.ActiveExplorer.Selection
    Dim objAttendees As Outlook.Recipients
    Set objAttendees = objItem.Recipients
    Dim strUnderline As String ' Horizontal divider line
    strDivider = "============================================================================"
    On Error GoTo EndClean:

    ' check for user problems with no or too many appointment(s) open
    Select Case objSelection.Count
    Case 0
    msgPrompt = myName & " - " & myVersion & vbCr & myCopyright & vbCr & vbCr & _
    "Please open an appointment first and re-run the script."
    msgButtons = vbOKOnly + vbCritical
    MsgBox msgPrompt, msgButtons
    GoTo EndClean:
    Case Is > 1
    msgPrompt = myName & " - " & myVersion & vbCr & myCopyright & vbCr & vbCr & _
    "Too many items were selected. Select exactly 1 appointment!"
    msgButtons = vbOKOnly + vbCritical
    MsgBox msgPrompt, msgButtons
    GoTo EndClean:
    End Select

    ' check if selected item it is an appointment
    If objItem.Class <> 26 Then
    msgPrompt = myName & " - " & myVersion & vbCr & myCopyright & vbCr & vbCr & _
    "This script works only on appointments and meeting requests."
    msgButtons = vbOKOnly + vbCritical
    MsgBox msgPrompt, msgButtons
    GoTo EndClean:
    End If

    ReplyDelete
  30. Part 2:

    ' prepare the body for appended info
    objItem.Body = objItem.Body + vbCr + vbCr + strDivider + vbCr + myName + " - " + myVersion + " " + myCopyright
    objItem.Body = objItem.Body & vbCr & "collected following informations at " & Now() & vbCr & strDivider
    objItem.Body = objItem.Body + vbCr + "Type " + vbTab + "Response" + vbTab + "Attendee" + vbCr + strDivider
    Dim strMeetStatus As String


    ' get the attendee list and their responses
    For x = 1 To objAttendees.Count
    Select Case objAttendees(x).Type
    Case olOptional '=2
    strAttendeeType = "optional "
    Case olOrganizer '=0
    strAttendeeType = "organizer"
    Case olRequired '=1
    strAttendeeType = "required "
    Case olResource '=3
    strAttendeeType = "ressource"
    Case Else
    strAttendeeType = "unknown "
    End Select

    Select Case objAttendees(x).MeetingResponseStatus
    Case olResponseAccepted '=3
    strMeetStatus = "accepted"
    Case olResponseDeclined '=4
    strMeetStatus = "declined"
    Case olResponseNone '=0
    strMeetStatus = "none "
    Case olResponseNotResponded '=5
    strMeetStatus = "not responded"
    Case olResponseOrganized '=1
    strMeetStatus = "organized"
    Case olResponseTentative '=2
    strMeetStatus = "tentative"
    End Select

    If objItem.Organizer = objAttendees(x) Then
    strAttendeeType = "organizer"
    strMeetStatus = "organized"
    End If

    objItem.Body = objItem.Body + vbCr + strAttendeeType + vbTab + strMeetStatus + vbTab + objAttendees(x).Name
    Next
    objItem.Body = objItem.Body + vbCr + strDivider + vbCr
    EndClean:
    Set objApp = Nothing
    Set objItem = Nothing
    Set objSelection = Nothing
    Set objAttendees = Nothing

    End Sub

    ReplyDelete
  31. Some times ago my children helped me very well)))But serious some very important mails were corrupted and they found next tool-recover ost data,which recover all my data for free as far as I remember.Moreover tool could restore data from files with *.ost extension.

    ReplyDelete
  32. Oh my gosh so COOL!! Thanks!

    ReplyDelete
  33. i have often wondered why microsoft do not have this functionality built into Outlook, seems fairly obvious. Thanks for designing it and making it available.

    ReplyDelete
  34. Cool stuff! Exactly what I need :)
    Thanks!

    ReplyDelete
  35. This was awesome, thanks so much!!!

    ReplyDelete
  36. Very cool, very well explained, took me 7 minutes from Google to a solution. Kudos.

    ReplyDelete
  37. Very much appreciated, all. Well done! Your efforts have saved me time and made my group ever so slightly more efficient. Every little bit helps us all.

    ReplyDelete
  38. A note to users who experience the macro only importing column headings. Try the macro again with Excel completely closed.

    ReplyDelete
  39. Thanks a lot!!! It is very useful! What Moteltan said:

    A note to users who experience the macro only importing column headings. Try the macro again with Excel completely closed.

    Is true!!! Works for me.

    Thanks to everyone.

    ReplyDelete
  40. This is amazing!! Not sure why I never tried googling for a solution before. Now I have an event that has almost 100+ attendees and I'm glad I can use a click of the button to view and sort the attendees list. Shame on MS for not providing a solution that's built in to their app.

    ReplyDelete
  41. I am using outlook 2003 and excel 2003. I get an error on this line

    objExcel.Worksheets("Sheet1").Range("B2").Sort _
    Key1:=objExcel.Worksheets("Sheet1").Columns("B"), _
    Header:=xlGuess

    Do you know why?

    ReplyDelete
  42. Works a treat, thanks!

    ReplyDelete
  43. I'm running Outlook 2007. I follwed this to a T, closing Excel before running, etc. I cannot get the data to export over. I get only the column headers and nothing else. I even tried the addition code listed from Anonymous on March 9, 2009, but it returns an error, Dim oExcel As Excel.Application is not valid.

    Please help. This would be extremely useful. -Heidi

    ReplyDelete
  44. Hi there,

    Thanks for the macro. I couldn't initially get it working after copying the code and modifying the trust centre security....i was following the instrutions above, opening a meeting invite and running the macro. It just copying the headings and giving me an outlook message saying no invite selected (even though i had the meeting window opened.

    What i noticed after opening a meeting was that when i returned to the calendar window, the meeting i had just opened was not selected SO i re-selected the meeting with the meeting window opened, ran the macro again and this time it worked!!

    Thanks again.
    Deb, Sydney

    ReplyDelete
  45. I only get the 3 headers. This would be very helpful. Could someone please tell me how to get this to work?

    Thanks,
    Chris

    ReplyDelete
  46. Ok, I had the same problem. I have closed my Excel and Outlook and open it again. Then it populated the opened Excel Book1.
    What I wonder if you have check. There is a pop-up after you initialize the macro, that ask you to allow access to Outlook for a choice of minutes, Did you tick the check box to allow and is the minutes you are selecting long enough?

    Again, Thanks for this, I am search for this feature for 1 and a half year now.

    Abri Theart
    IP PM/Change Manager
    NSN MEA

    ReplyDelete
  47. Is it possible to add "Out of Office" and even "Deleted" reply?

    Abri Theart
    IT PM/Change Manager
    NSN MEA

    ReplyDelete
  48. I wonder why Microsoft hasnot built this feature. Thanks for posting the macro, it works just great.

    just a note guys, this macro launches a new excel, but if you have excel already open the data goes to first opened sheet and headers go to new sheet.... small issuebut I am loving this macro already.

    Cheers,
    Ashish

    ReplyDelete
  49. If you have an error where you can't get past the Excel.application line, you want to make a Reference to MS excel 12. From the VB window: Tools>References> then find Microsoft Excel 12 object Library. Once you have that reference, that code should work.

    I'm having trouble with the code - it only lists my first person, then fails on the active directory bit. I'll get it going with a little work. EXCELLENT code. Thanks for all the hard work. Well done.

    ReplyDelete
  50. Also, you can put a little button on the top of every Outlook window that will call this macro. Open a calendar entry, right click on the top toolbar. "Customize quick access toolbar." From the drop down, choose macros. Move over your new macro. The icon will be on top of every outlook mail message or meeting invite by your micsosoft office icon.

    ReplyDelete
  51. This code doesn't work in outlook 2007, it only generates a header row in Excel with no attendee name.

    JK

    ReplyDelete
  52. Thanks so much for this little gem....saved me a bunch of time, thanks.

    Jim

    ReplyDelete
  53. Once I was in the Inet and was looking for the solution of my old email issue. I was born under a lucky star and noticed - ost to pst converter. It amazed me greatly. The utility settled my troubles easily and maybe has enough possibilities for solution similar problems.

    ReplyDelete
  54. Amazing, helpful and soooooo efficient! Saved me a lot of time!

    ReplyDelete
  55. way cool, nicely done, also appreciate the email adder

    ReplyDelete
  56. YAY! Thank you for saving my sanity!!!

    ReplyDelete
  57. Yes, you are SO the savior. =D

    ReplyDelete
  58. ...eeeeee YABBA DABBA DOOOOO!!!!!! :)

    ReplyDelete
  59. Yahoooooooo! works great! thanks

    ReplyDelete
  60. Only "complaint" would be the meeting organizer is not correctly identified in the exported spreadsheet. I looked at the code and it does seem to check for Organizer but does not populate the role correctly - organizer shows up as NO RESPONSE under response column.

    ReplyDelete
  61. Worked great the first time. Tried again to no avail. I would get a highlighted section at the top of the excel page and nothing below it. I tried the revised code above and no dice. So, I tried removing everything and starting again. I even disabled my macros warning and nothing - just the top row highlighted. So upsetting - I loved this!

    ReplyDelete
  62. Any chance someone figured out how to get past the email lookup on the first attendee where it seems to fail?

    ReplyDelete
  63. At last! Peachy!!

    ReplyDelete
  64. Excellent Macro, Thank you very much.

    ReplyDelete
  65. Oh man, this made my day!

    I see a some people have had troubles with the script. Here is my fix:

    At first it did not work on my MS Outlook Standard 2007 SP2 MSO (12.0.6557.5001).. The script ran, excel got opened and the headings got written, but no names.

    When I debugged the script it crashed on the code objExcel.Cells(x + 1, 1).Value = objCurrentAttendee.Name because the Cells in objExcel had not been initialized.

    To fix the problem i had to remove the line 50:
    Set objExcel = GetObject(, "Excel.Application")

    After that everything worked fine and the macro made my day!
    Thanks alot!

    ReplyDelete
    Replies
    1. Thanks for the tip - that fixed my issue! also amazed that Microsoft does not include this functionality in their product.

      Delete
  66. I used it on Outlook 2003 and it worked GREAT. Saved me so much time counting hundreds of attendees to a meeting. Thanks!!

    ReplyDelete
  67. Has anyone been able to get this to run for meetings they were invited TO? I create meetings on public calendars for other organizers and would like to have them be able to print them out without having them come to me.

    ReplyDelete
  68. this is fabulous!!

    ReplyDelete
  69. This is so excellent I wish I could send you some money or something. It's just awesome!

    ReplyDelete
  70. This is the best thing ever!!! Have 2007, did the alt+F8 and figured out to close excel completely before running and enabled the macros...all fantastic...studpid this option isn't available "out of the box" thank you soooo much!

    ReplyDelete
  71. This changed my life! Thanks

    ReplyDelete
  72. This worked well!

    ReplyDelete
  73. Can we have the full code, instead of broken down. Please.

    ReplyDelete
  74. Thank you.

    I took wasnt getting any names in excel. Thanks for someone posting that following line needs to be removed from code:
    "To fix the problem i had to remove the line 50:
    Set objExcel = GetObject(, "Excel.Application")"

    ReplyDelete
  75. Thanks that worked really well for me :)

    ReplyDelete
  76. Amazing! Why couldn't MS think of this?

    For those of you who were only getting headers, here's what I did.

    When you are asked whether to Accept or Deny the macro, accept for 10 mins which should give the program enough time to complete.

    ReplyDelete
  77. this is AWESOME, and I have spread it about the office. thankyou!

    ReplyDelete
  78. Good work...thanks a lot

    ReplyDelete
  79. I've been trying to find something like this for YEARS! So many times I have to print screen and piece together...THIS IS AWESOME!!! THANKS SO MUCH! Works perfectly!

    ReplyDelete
  80. Hi i need to just export the responses of just yes and no is there a simpler code?

    ReplyDelete
  81. Hi,

    Thanks for everyone's efforts and it works nicely (once you activate macros via developer view and navigate to the tracking view for a meeting as suggested above), I'm using Outlook 2010. However, I can get the same outcome for a SINGLE MEETING by going to the tracking view for a meeting, highlighting the first row, pressing shift and the down cursor to highlight all the rows, Ctrl-C to copy and Ctrl-V to paste into Excel?

    Is there a way to modify the macro to select a range of meetings by date range and/or part title and populate a series of sheets (one per meeting?). That would be a great help, and would beat cutting and pasting hands down.

    I'm a project manager and set up loads of meetings, something to pull off the attendee information for a range of meetings would be a fantastic time saver for me.

    ReplyDelete
  82. I ran the macro once and then ran it again and it stopped working the second time. I followed the update about disabling macros and it still did not work. However, I closed completely out of Excel and it worked again. So, I think that if you run the macro you have to have Excel completely closed. If you run the macro twice in a row, then you have to close Excel each new time. Maybe this advice will work for others.

    ReplyDelete
  83. Wow, this is brilliant!!!
    I now use the macro that sends the list to an excel file and the macro that puts the information into the body of the appointment (Oct 1 2009). I edited the macro to insert an extra tab after a "None" response so that the columns lined up. Also added an extra tab after "Type" in the header, once again so the columns lined up.
    Love them both !!!

    ReplyDelete
  84. It works Great thanks for sharing such a useful tip...

    ReplyDelete
  85. Fantastic macro, thanks for sharing. Just need to make sure Excel is closed down fully each time you run it.

    ReplyDelete
  86. I don’t understand this post. I must not be getting something basic here.

    ReplyDelete
  87. Why is it I'm only gettign 3 headers and the file is not downloading.

    ReplyDelete
  88. Worked like a charm for me, too (Outlook 2007)! I had to close out Excel completely, then reopen and run the macro :) Programmers are awesome!! :)

    ReplyDelete
  89. worked like a charm!!! Thank you!!!

    ReplyDelete
  90. Wow - amazing instructions - worked perfectly. Thank you!

    ReplyDelete
  91. Amazing. THANK YOU!

    I'm using 2003, works like a charm. Only problem is that you cannot have any Excel spreadsheets open otherwise it overwrites your existing content.

    Opening the invitation, clicking on Tracking and then Alt+F8 > Run -- works like a charm.

    Thanks a million!

    ReplyDelete
  92. Worked perfectly thank you so much

    ReplyDelete
  93. I'm sure lots have people have answered this but in Outlook 2003, open the appointment and go to Tracking. Then alt + f8 and run the macro from there

    doing this gets round the issue of just havine headers in Excel

    ReplyDelete
  94. There is tool from IMIBO - Very useful for those who prepare meeting - http://www.imibo.com/imidev/exchange/imiap.htm

    ReplyDelete
  95. Thanks!!!. Appreciate the very detailed messaged. This worked on Outlook 2007 on Windows. Wonder if there is similar macro for Outlook on Mac.

    ReplyDelete
  96. Great macro! If you want to autofit the A-column to see the full names, you can add this in the 'Sort Worksheet just before EndClean at the bottom (line160?):
    objExcel.Worksheets("Sheet1").Columns("A:A").EntireColumn.AutoFit

    ReplyDelete
  97. OMG! I did it! I followed the instructions read all the information, trialed and error, re read followed more carefully, stopped worrying, had a banana, opened the right workbook and there they all were in glorious alphabetical order and I still don't know what a macro is! Brilliant - well done everyone even those who said - they'd done it or it hadn't worked it's all useful information that keeps you going to the very satisfying end result :-))Thanks

    ReplyDelete
  98. Per some other bloggers, noticed that there were issues when Excel was already open. The column headers were split from the actual data and the data was pasting over my existing open file.

    A slight update to the code I've made below resolves this to work both when you already have Excel open or a new instance required.

    You just need to replace the text under the comment 'Set up Excel with the following:

    ' Set up Excel
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Add

    If objExcel Is Nothing Then
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Add

    End If

    On Error GoTo EndClean:

    'Create the header and set the background color as yellow... ...


    ReplyDelete
  99. Anyway I can package this so that I can install on multiple users' machines?

    Something I can add to a startup script perhaps?

    Thanks!

    ReplyDelete
  100. Thank you so much this is so useful. The original code in the post works for me in outlook 2007, but I did struggle at first because only the titles populated in excel. I discovered this was because I alreday had excel open, if you close excel irst then the problem is solved!

    This will save me so much time :)

    ReplyDelete
  101. amazing. great work

    ReplyDelete
  102. This is a real time saver and true productivity enhancer. And yes, if you already have Excel open, your data will be pushed into whatever spreadsheet is open, with no - as we found out -option to "undo" this paste action automatically.

    ReplyDelete
  103. What a fantastic bit of code for Outlook 2007. Saved me a whole lot of time especially around events of almost 100 people.

    ReplyDelete
  104. Great code, even better instructions considering I am no genius when it comes to these things...

    Thanks!

    ReplyDelete
  105. Excellent ! LOooooove it.
    thanks for sharing and being so clear about the instruction.
    I am no genius when it comes to codes but it's clean and clear.
    thanks to all for your improvement too...

    ReplyDelete
  106. Thank you so much! Still works :)

    ReplyDelete
  107. Great stuff works like a charm.

    MicroSoft get your head clear and add this as an option ...

    ReplyDelete
  108. Works great, thanks for taking the time to create and share

    ReplyDelete
  109. You can also select the first line in your tracking of the appointment, hold down the shift key, then arrow down (or page down), until all names are selected. Copy (Crtl+C), open Excel, Paste (Ctrl+V). Voiala.

    ReplyDelete
  110. Works great. I would love to add a column for email address but I just can't seem to get to work. I keep getting the x400 info ... can't get SMTP

    ReplyDelete
  111. How can I get this to work for Microsoft 2013?

    ReplyDelete
  112. thanks author for your script, was very useful. the newer versions of outlook now have a feature that should help. -> under Tracking (click the drop down) and there is a copy to clipboard option....paste into excel.

    ReplyDelete