Identify people who have accepted a meeting request

N

NickH

I would like to write a macro which will step through all respondents to a
meeting request in Outlook and save the e-mail address of those who have
accepted the request - ignoring those who declined.

The purpose is to be able to send further information to people who will
attend, just prior to the start time. My intention is to set up a single
e-mail which is addressed to the selected people.

Can anyone give me the basic mechanism to do this, please?

Many thanks
 
N

NickH

Hi All,

I persevered with my problem below and worked out the answer for myself. My
code is reproduced below for anyone else who would like it.

I have several questions arising from this, which some kind person may be
able to answer for me?

1. If I use "Set objApp = CreateObject("Outlook.Application") ", I get an
ActiveX failure. But what is the difference between
"Set objApp = CreateObject("Outlook.Application") " and
"Set objApp = Outlook.Application " (the base instance, I presume)
… and why should I use one or the other?
2. Is there an easy way to sort the addresses alphabetically? I can do this
by manually positioning the items in an array, but I wondered if there were
an easier way?

Anyway, here's my code:

Sub GetCalendarItems()
Const kMeetingSubject = "Subject" ' whatever the item subject is
Dim objApp As Outlook.Application
Dim OItem As Object
Dim objNS As NameSpace
Dim objAttendee As Outlook.Recipient
Dim strAcceptedList As String
Dim NewMessage As MailItem

Set objApp = Outlook.Application
Set objNS = objApp.GetNamespace("MAPI")

Rem Step through all items in the calendar
For Each OItem In objNS.GetDefaultFolder(olFolderCalendar).Items
If OItem.Class = olAppointment Then ' Only interested in
Appointments
If OItem.Subject = kMeetingSubject Then ' This one of interest?
strAcceptedList = vbNullString ' -yes- initialise
the address string
For Each objAttendee In OItem.Recipients
Rem Step through the recipients list, looking for 'Accepted' and
'Tentative' responses
If objAttendee.MeetingResponseStatus = (olResponseAccepted Or
olResponseTentative) Then
If strAcceptedList <> vbNullString Then strAcceptedList =
strAcceptedList & "; " ' insert separator after the first
strAcceptedList = strAcceptedList & objAttendee.Address '
add the recipient address to the list
End If
Next
Rem Check that we found someone who accepted
If strAcceptedList <> vbNullString Then
Rem Create a new Mail Item and fill in the "To:" list and subject
Set NewMessage = objApp.CreateItem(olMailItem)
NewMessage.Display
NewMessage.To = strAcceptedList
NewMessage.Subject = kMeetingSubject & " " & Format(OItem.Start, "dd
mmm hh:mm") & " : " & "<additional comment>"
NewMessage.Close olSave ' save as draft for later editing
End If
Rem If you are only looking for one instance of the meeting, you can
"Exit For" at this point
Rem Otherwise keep going until you have encountered all instances -
with one mail prepared for each instance
End If
End If
Next

strAcceptedList = vbNullString
Set objAttendee = Nothing
Set NewMessage = Nothing
Set OItem = Nothing
Set objNS = Nothing
Set objApp = Nothing
End Sub
 
Top