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
Download Event Planner And RSVP Manager for Excel 2003
Download Event Planner And RSVP Manager for Excel 2007/2010
Why bother?
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.
Dashboard
I've included a simple dashboard (using the term loosely) that lets you easily tally the number of responses.
Troubleshooting
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!
Download Event Planner And RSVP Manager for Excel 2003
Download Event Planner And RSVP Manager for Excel 2007/2010
