I've created an event planner and RSVP manager for Excel and Outlook. This event planner allows you to
- Record important details about your party, conference or event in one place
- Set up a list of attendees
- Send out invitations (Outlook meeting requests)
- Track Outlook's invitation responses (Accepted, Declined, Tentative) directly from Excel
I have found that using meeting requests as invitations is easier than using emails or even voting, because you allow Outlook to use its built-in features to manage the "meeting".
- Recipients can easily indicate their status by "accepting" or "declining" the invitation, or change their status to "tentative"
- You can simply open the meeting request to check responses, without having to rifle through your emails.
See Who has accepted my meeting request? for sample Outlook VBA code that easily lists the responses to any given meeting request.
Three steps to track event responses
Setting up this workbook to track event responses takes three simple steps:
Step 1 – Log Event Info
On the Event worksheet, you need to enter
- The name for your event (will be shown as the Subject of the Outlook invitation)
- The location of the event (meeting room, office, off-site location, etc)
- Start date and time (in the following format: mm/dd/yyyy hh:mm:ss AM/PM)
- End date and time (in the following format: mm/dd/yyyy hh:mm:ss AM/PM)
- Body text (if any) for the invitation – displayed when the meeting request is opened in Outlook
- Path and filename for an (optional) attachment to the invitation
- Number of minutes for an event reminder
- Whether this is an all-day event (if so, leave start and end dates/times blank)
Step 2 – Write down names of attendees
On the Invitation List worksheet, put the list of invitees in column A. This list must be in the format Last, First in order for Outlook to capture the responses and record them correctly. If your copy of Outlook displays names differently, then enter the names that way instead.
Step 3 – Add VBA code to Outlook
The following code needs to be placed in the ThisOutlookSession module in Outlook. Where do I put my Outlook VBA code? You'll need to modify the top section as follows:
- Change "your event name here" to the name of your event (the same as the Subject on the Event worksheet)
- Change "path and filename of event workbook here" to the location of the event workbook. It may be placed in a shared or network location.
- Change "Invitation List" to the name of the Invitation List worksheet (I would just leave it as Invitation List)
- If you prefer a different date format, change "mm/dd/yyyy hh:mm:ss AM/PM" to something else
Private WithEvents Items As Outlook.Items Const eventName As String = "your event name here" Const eventWorkbook As String = "path and filename of event workbook here" Const invitationSheetName As String = "Invitation List" ' name of invitee list sheet Const dateFormat As String = "mm/dd/yyyy hh:mm:ss AM/PM" Private Sub Application_Startup() Dim olApp As Outlook.Application Dim objNS As Outlook.NameSpace Set olApp = Outlook.Application Set objNS = olApp.GetNamespace("MAPI") ' (1) default Inbox Set Items = objNS.GetDefaultFolder(olFolderInbox).Items End Sub Private Sub Items_ItemAdd(ByVal item As Object) On Error GoTo ErrorHandler Dim meeting As Outlook.meetingitem Dim reply As String Dim sender As String Dim partyFolder As Outlook.MAPIFolder Dim xl As Object ' Excel.Application Dim xlwkbk As Object ' Excel.Workbook Dim xlwksht As Object ' Excel.Worksheet Dim senderRange As Object ' Excel.Range If TypeName(item) = "MeetingItem" Then Set meeting = item With meeting ' check if meeting response is for correct event If InStr(.subject, eventName) > 0 Then ' capture sender name sender = .senderName ' check reply status reply = Mid$(.subject, 1, InStr(.subject, ":") - 1) ' update reply for spreadsheet Select Case reply Case "Accepted" reply = "Y" Case "Declined" reply = "N" End Select ' open event workbook Set xl = GetExcelApp If xl Is Nothing Then MsgBox "Cannot start Excel" GoTo ProgramExit End If Set xlwkbk = xl.Workbooks.Open(eventWorkbook) Set xlwksht = xlwkbk.Sheets(invitationSheetName) ' search invitation list for sender name Set senderRange = xlwksht.Cells.Find(sender) If Not senderRange Is Nothing Then ' put response and date into appropriate cells senderRange.Offset(0, 1).Value = reply senderRange.Offset(0, 2).Value = Format(.ReceivedTime, dateFormat) End If ' save and close Excel xlwkbk.Close True xl.Quit ' move response to Inbox subfolder If Not CheckForFolder(eventName) Then Set partyFolder = CreateSubFolder(eventName) Else Set partyFolder = Session.GetDefaultFolder(olFolderInbox).Folders(eventName) End If .UnRead = False .Move partyFolder End If End With End If ProgramExit: Exit Sub ErrorHandler: MsgBox Err.number & " - " & Err.Description Resume ProgramExit End Sub Function GetExcelApp() As Object On Error Resume Next Set GetExcelApp = CreateObject("Excel.Application") End Function
Visit Create Folders for CheckForFolder and CreateSubFolder.
How It Works
After adding the event details and attendees list to the event workbook, click Create Event to send out meeting requests to the attendees. Click Open/Edit Event to check your event's responses or change details and send out updates.
As emails come into your Inbox, the Outlook code looks for meeting responses. If the event name is found in the subject line, Outlook captures the sender name, the received date and the actual response (Accepted, Declined, Tentative). Excel is started and the event workbook is opened and updated with the details from the response.
The response is moved into a subfolder with the same name as the event. If the folder does not exist, it is created.
Make sure you restart Outlook after placing this code in ThisOutlookSession.
Note that the Outlook code will only work when Outlook is open.
I've included a simple dashboard (using the term loosely) that lets you easily tally the number of responses.
Question: My invitation opens in Outlook, instead of being sent. I have to click Send to send it. How do I change this behavior?
Answer: Open up the event workbook and start the VB Editor by pressing Alt+F11. Find this line of code:
Const DEBUG_MODE As Boolean = True
Change True to False and the invitation will be sent without displaying. Be sure your invitation details are correct before doing this, as the invitation will be sent and there will be no way to stop it!