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
Cristhian says
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
Diane Poremsky says
I will take a look at it.
Copying meetings used to copy it as an appointment, not it copies it as a meeting.
LuÃs Carlos says
I loved this code!! I send a lot of invitations and it will be very usefull, thanks for sharing!!!
Steen B.N. CPH says
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.
Kaytilou says
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 big thank to you, you would save my life, no longer spend a few hours restarting people. It is not often easy when these meetings are recurring every month.
Wish you a great day
Diane Poremsky says
>>
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?
Kaytilou says
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 :-)
Chip Rose says
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.
Diane Poremsky says
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)
BladerzZz says
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.
Diane Poremsky says
it could be compiled, if you have visual studio. (I'm not aware of any existing addins that offer this feature.)
BladerzZz says
And you know maybe how to do this?
Diane Poremsky says
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
AJ Noto says
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.
Diane Poremsky says
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....
Diane Poremsky says
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.
Diane Poremsky says
This version uses a text file. The registry version might be faster.
Me says
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.
Diane Poremsky says
You can right click on it in the calendar and choose Forward.
SSethis says
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.
George says
I was able to create document also and i did this
But now I Have an issue that in word document it comes like:
No response: aaa
accepted: bbb
No response : xxx
declined: yyy
No response : ggg
I do not want it like this. Please help asap.
Diane Poremsky says
If you want all of the accepted together, and the declined together, you'll need to create arrays then write to the document. I don't have ant code samples that do that, unfortunately.
George says
it is a msgbox. I am planning to copy the values . The list of attendees and their responses to a word document. Unfortunately am not able to copy the values in vba to word. For example:
Not responded:
aaa
bbb
ccc
Respnded:
ddd
ffff
ggg
Tentative:
xxx
yyy
I need them like these in a word document. I was able to creat and open a document and write Not Responded, Responded, Tentative into it.
But the values am not able to. Please help.
Thanks In advance
George says
also i did in my program to show the status of email id's in a msgbox. Unfortunately when i send an invite to 600 people only 20 were shown in the msgbox. Looks like there is a limitation. is there any other way?
Diane Poremsky says
It sounds like there is a character limit. Some of the dialogs have a limit of 256 characters.
Are you using a msgbox or do you mean the To field? It might be possible to split the list into multiple messages.
George says
thanks i knew that way. I wanted to know if there is any VBA code whch does it..
Diane Poremsky says
I don't have any VBA samples that do this, sorry.
George says
hi,
please tell me if i need to send the email to a distribution list. how to do for the same.
Thanks in advance.
Diane Poremsky says
You'll need to expand the DL field - on the scheduling tab, click the + to expand the DL. It *should* keep the responses for those who replied and add everyone else to the list. Then you can send and email to those who haven't responded...
Rolly Estrada says
Thanks. Interestingly, the code is not breaking out when i stepped in to the code through breakpoints.
Diane Poremsky says
Use the step button and watch it - does it skip everything? Oh, and comment out on error resume next. With that gone, run it and see where it stops.
Rolly Estrada says
Thanks. I did that but no new effect. I did an isolation and have tried other macros on this blog: 'ReplyAllwithAttachments' and 'ReplywithAttachments' and both ran seamlessly.
Diane Poremsky says
I tried it with the code on the site (to confirm it wasn't messed up) and it worked perfectly - a new email was created with the time and date of the selected meeting, addressed to people who did not respond. It will generate an email if you select an appointment, but the to field is blank.
Did you try stepping through the code to see if/where it fails?
Rolly says
Hi Diane,
I pasted everything as instructed above, and added the macro in QAT which I named as SendEmailtoNoResponse. I have opened an appointment where I was an organizer and after hitting the macro's button in my QAT, it just does nothing. My macro security is set to notification to all macros. Where do you think I've gone wrong? Thank you.
regards,
Rolly
Diane Poremsky says
Set macro security to allow all macros and restart outlook - does it work now? That will tell us if the problem is with the macro or somewhere else.
Dan says
In your "Remove invitees who declined" Macro, is there a way to make it operate only on a selected meeting, instead of all meetings in the calendar?
Diane Poremsky says
Yes, replace For Each objItem In oAppointments.Items with Set objItem = Application.ActiveExplorer.Selection.Item(1) and delete Next (Before MsgBox "Done").
Charlotte McClintic says
Syntax Error
Ln93,col 1
Please , I am a basic user 101.....copied and pasted as instructed
Diane Poremsky says
Are any of the lines in red color? That will tell us where the error is.
Curt Miller says
This is great! What would have to be changed to remove the meeting organizer from the email addressees of non-responders?
Diane Poremsky says
You can change the line to this
If objAttendees(x).MeetingResponseStatus = 0 And objAttendees(x) <> objItem.Organizer Then
to leave off the organizer
Fred Durst says
I am getting the same error. Where specifically do you need to paste the "work with open item or selected item at the end of the module. Do you need to replace the current Set objitem?
Diane Poremsky says
Get the GetCurrentItem function from work with open or selected items and paste it at the end of this macro, after the End Sub. (I don't include the function on the page because it may cause Google to think the page has too much copied content.)
Paul Abke says
Sorry, I missed the step about addind the function.
Paul Abke says
I get an error at the statement: Set objItem = GetCurrentItem()
I am using Outlook 2010 with Office 365