This code began it's life in a macro that created a list of Meeting Attendees and Responses. With a few tweaks, the macro creates a new email message addressed to the invitees who have not yet responded.
This can be tweaked to send messages addressed to those who accepted, are tentative, or declined by changing the 0 in this line: If objAttendees(x).MeetingResponseStatus = 0 Then to the constant representing another response type.
In Outlook VBA, valid response statuses are:
Constant | Response |
---|---|
0 | No response |
1 | Organizer |
2 | Tentative |
3 | Accepted |
4 | Declined |
Create a message addressed to attendees
- Press Alt+F11 to open the VBA editor.
- Right click on Project1 and choose Insert > Module.
- Paste the code below into the Module.
- Get the GetCurrentItem function from Outlook VBA: work with open item or selected item and paste it at the end of the module.
If location and attendees fields are not picked up when the macro runs against a selected meeting, open the meeting and run it.
To use, select a meeting on the calendar or open a meeting and run the macro. A new message will open, addressed to all invitees (required, optional, or resource) that has not yet responded. The organizer is not included in the message.
This macro works with either the selected item or opened item, using the GetCurrentItem function, which is available at "Outlook VBA: Work with Open Item or Selected Item"
Sub SendEmailtoNoRepsonse() ' Get the GetCurrentItem function from ' http://slipstick.me/e8mio Dim objApp As Outlook.Application Dim objItem As Object Dim objAttendees As Outlook.Recipients Dim objAttendeeReq As String Dim objOrganizer As String Dim dtStart As Date Dim dtEnd As Date Dim strSubject As String Dim strLocation As String Dim strMeetStatus As String Dim strCopyData As String On Error Resume Next Set objApp = CreateObject("Outlook.Application") Set objItem = GetCurrentItem() Set objAttendees = objItem.Recipients ' Is it an appointment If objItem.Class <> 26 Then MsgBox "This only works with meetings." GoTo EndClean: End If ' Get the data dtStart = objItem.Start dtEnd = objItem.End strSubject = objItem.Subject strLocation = objItem.Location objOrganizer = objItem.Organizer objAttendeeReq = "" ' Get The Attendee List For x = 1 To objAttendees.Count ' 0 = no response, 2 = tentative, 3 = accepted, 4 = declined, If objAttendees(x).MeetingResponseStatus = 0 Then If objAttendees(x) <> objItem.Organizer Then objAttendeeReq = objAttendeeReq & "; " & objAttendees(x).Address End If End If Next strCopyData = vbCrLf & "-----Original Appointment-----" & vbCrLf & _ "Organizer: " & objOrganizer & vbCrLf & "Subject: " & strSubject & _ vbCrLf & "Where: " & strLocation & vbCrLf & "When: " & _ dtStart & vbCrLf & "Ends: " & dtEnd Dim objOutlookRecip As Outlook.Recipient Set listattendees = Application.CreateItem(olMailItem) listattendees.Body = strCopyData listattendees.Subject = "Please respond to: " & strSubject listattendees.To = objAttendeeReq For Each objOutlookRecip In listattendees.Recipients objOutlookRecip.Resolve Next listattendees.Display EndClean: Set objApp = Nothing Set objItem = Nothing Set objAttendees = Nothing End Sub
Remove invitees who declined
This variation of the macro from J. Frohberg above will remove all person's who declined the meeting. It checks all meetings in the Calendar.
Sub Delete_All_Declined() Dim oOL As New Outlook.Application Dim oNS As Outlook.NameSpace Dim objItem As Outlook.AppointmentItem Set oOL = CreateObject("Outlook.Application") Set oNS = oOL.GetNamespace("MAPI") Set oAppointments = oNS.GetDefaultFolder(olFolderCalendar) For Each objItem In oAppointments.Items On Error Resume Next x = 1 Do Until x > objItem.Recipients.Count ' 0 = no response, 2 = tentative, 3 = accepted, 4 = declined, If objItem.Recipients(x).MeetingResponseStatus = 4 Then If objItem.Recipients(x) objItem.Organizer Then objItem.Recipients(x).Delete x = x - 1 objItem.Save End If End If x = x + 1 Loop Next MsgBox "Done" Set oAppointmentItem = Nothing Set oAppointments = Nothing Set oNS = Nothing Set oOL = Nothing Set objItem = Nothing End Sub
How to use the macros on this page
First: You need to have macro security set to low during testing. The macros will not work otherwise.
To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
Hi
This macro is very useful for me, but I have a question.
I'd created recurrent meetings in a weekly / bi weekly basis for my customers, when I try to run this code for recurrent meetings. I received this error "You don't have appropriate permission to perform this operation on this line "objCopiedMeeting.Save" it creates a mess because duplicate the meeting and appears both the original and the copy, I need stop the code and delete the copy.
But when I delete the copy, Outlook sent a notification for the customer
I don't know how I can select one meeting, and on this selection, run the code only on this meeting
Regards
I will take a look at it.
Copying meetings used to copy it as an appointment, not it copies it as a meeting.
I loved this code!! I send a lot of invitations and it will be very usefull, thanks for sharing!!!
I have created an Outlook add-in from VB.Net to prepare a meeting forward message - with a dialog to set filter criterias for both the response type and also when the did respond.
Many thanks for this macro, I've tried it and it is so closed of what I havve to do. Let me explain. I am in charge of organizing events/meeting for my company, sometimes with more that 100 people. I send meeting request. Some people answer, some don't and some on provisional which is very difficult for the follow up. What I do is open the meeting request, click on follow, uncheck people's request to those who have accepted the meeting and send back the request meeting with a wording asking for answering. I have to do it as many time as needed until getting all the answer. This may sound easy, but what a waste of time and especially when there are more than 50 people. Your macro "Send an email to attendees who have not responded" is perfect, but means that you send an email instead of sending back the request meeting. Do you think there will be a way to automate this by macro? If there is no solution, I will use your macro (send email) but I would love to find a solution, resending the request meeting without loosing people who have already accepted it. A very… Read more »
>>
What I do is open the meeting request, click on follow, uncheck people's request to those who have accepted the meeting and send back the request meeting with a wording asking for answering.
>>>
Are you removing users and sending an update? That will cancel it for the people you removed.
How do you need the macro automated?
Thank you Diane for your answer. I use to do exactly what you are doing, ( open the meeting request, click on follow, uncheck people's request to those who have accepted the meeting and send back the request meeting with a wording asking for answering) but sometime it take time specially when you have more than 50 people in the request. That is the reason I wanted to automate this function by a macro in order to save time.
If you have the solution, it would be perfect. I wish you a great day :-)
Get Compile Error with "Set objItem = GetCurrentItem()" being highlighted in the error in visual basic screen.
Hopefully this is a problem with Outlook 2016 using 365?
It would be great to have a fix... very much appreciate you going out of your way to enlighten us... again thanks. Chip.
Sorry about that - you need the function at https://www.slipstick.com/developer/outlook-vba-work-with-open-item-or-select-item/#getcurrentitem - this allows you to use it with either open or selected items. (I'll make note of that on the page)
Is it possible to get this as an Add-in in Outlook? As a macro it works so nice but i need it as a add-in.
it could be compiled, if you have visual studio. (I'm not aware of any existing addins that offer this feature.)
And you know maybe how to do this?
It's beyond the scope of this article. I don't want to support users who run into problems trying to compile code, hence no articles that tell how to do it. There is a walkthrough at https://msdn.microsoft.com/en-us/library/cc668191(v=vs.120).aspx
This is sooo close to what I need but I cannot think of a way to tweak to for my needs... I am in charge of organizing meetings for my company (150+ people). However, depending on the conference room, there may only be space for a certain amount of people who accept meeting invitations (say 50ish people). But because the meetings are first come-first serve, once the room capacity is met I have to send individual emails to those who still accepted, but are now technically on a waiting list, alerting them to the fact there is no space left in the room.
Is someone willing to please help me automate this? I thought of using an automated response rule based on subject line "meeting name" and "accepted", but cannot figure out how to delay sending the response until i have 50 "accepts" in a certain folder.
It's not an unusual request, I'm surprised no one has figured out a good solution.
So you are moving the accepts to a specific folder? A macro could watch the folder and count the number of messages then trigger an autoreply. Even if you weren't moving the replies, a macro could still count, using the subject. Not sure how error-proof it will be... The big thing will be missing a malformed subject or if someone replies with the same subject. Undercounting wouldn't be so bad - people on the wait list would fill in.
Setting it up each time could be a problem though....
The good news is that i have a working macro. It's based on the macro at https://www.slipstick.com/developer/fun-arrays-one-macro-many-replies/ - the possibly bad news - it stores the response count in the registry (in a corp environment, you might not have the permissions needed to write to it). You'd also need to update the macro with the meeting subject and room count each time you send a meeting request that needs limited. A first version of this macro is at count-room-size.txt.
This is an itemadd macro that watches the inbox. Add the meeting subject and count to the arrSubject and arrRmSize, in the same spot for each. To test it you can accept a meeting (using a second account) then copy the acceptance and paste it in the inbox.
This version uses a text file. The registry version might be faster.
Is there anyway to use the forward meeting invite instead of just sending an email?
That way if the recipients have misplaced the original invite, they can accept the notification.
You can right click on it in the calendar and choose Forward.
Sorry is there a way to automatically do this and remove the people who have responded? I believe someone else asked a similar question but I don't think you answered. It's something so many people are desperate for. Essentially we need to prompt the people who haven't responded with an email they can click accept rather than a general email they can't directly action.