We have already seen how to integrate Excel with Outlook to send and receive emails in a previous post. Now we see how to manage the calendar from Excel and make appointments, send out meeting invites, or get meeting information from existing appointments, all automatically. This is just a summary; you can find more detailed information and further functionality and examples in Excel Macros for Outlook.
Appointment vs Meeting
An appointment is what we see in the calendar while a meeting invite is the request sent to other recipients to join the meeting. When accepted, that meeting request becomes an appointment in the recipient’s calendar. Outlook object model contemplates an AppointmentItem object that represents an appointment in the calendar and a MeetingItem object that represents a change to the recipient's Calendar folder initiated by another party or as a result of a group action.
The meeting item has many of the properties of a mail item, and both “live” in the mailbox. The appointment item, on the other hand, has a number of different properties (but some in common too) and lives in the Calendar. For example, the MeetingItem has properties such as ReceivedTime, SenderName (or SenderEmailAddress), while the AppointmentItem has properties such as Start, Duration, Location. Both of them have properties such as Subject or Body, and many methods in common.
Create an Appointment
We create an appointment in the calendar using the method CreateItem along the appointment item type (olAppointmentItem) and saving the appointment. Before saving, we set properties of the appointment. But mail and appointment items have different properties (just a few are the same). One of the most important properties of the appointment item is the property MeetingStatus, which determines whether is saved as an appointment in our calendar (for us alone) or sent as a meeting invite to other people. See below an example to create a one-time appointment in our calendar (only for us).
Set OutAppot = OutApp.CreateItem(olAppointmentItem)
With OutAppot
.MeetingStatus = olNonMeeting
.Subject = "apointment test"
.Body = "This is a test appointment"
.Start = "2/3/2025 10:00"
'.Display
End With
We can create multiple appointments putting the code above inside a loop. We can do that for example to add appointments from a list in the Excel worksheet.
Send Meeting Invites
We need to set the MeetingStatus property to olMeeting (default value) to create a meeting for others to attend. But a meeting is not a meeting without other participants; otherwise, it’s just an appointment in our calendar alone. There are basically two ways to add recipients to the meeting. The first one is using the property RequiredAttendees (and OptionalAttendees), as shown belo. Another option is using the Recipient object, which allows further actions.
Sub SendMeetInvite()
Dim OutInvite As AppointmentItem
Set OutInvite = OutApp.CreateItem(olAppointmentItem)
With OutInvite
.MeetingStatus = olMeeting
.RequiredAttendees = "excelmacromania@outlook.com; xlsgsdata@gmail.com"
.Subject = "test meet invite"
.Body = "This is a test meet invite"
.Start = "05/12/2023 10:00"
.Location = "Your Office"
.Duration = 30
.Display
'.Send
End With
Set OutInvite = Nothing
Set OutApp = Nothing
End Sub
As we saw earlier for emails (see Excel Email Management), we can set the subject and body of the calendar invite with the respective properties. Furthermore, the Start and Location properties set the timing of the meeting. We can put that in a loop to send multiple meeting invites to various recipients and at different time slots.
We can use other properties to set recurrence, reminders, importance, and also define various sending options. That´s all explained in detail in Excel Macros for Outlook.
Get Meeting Requests
We can loop through items in the mailbox to find meeting invites. The process is similar to what we saw earlier in this other post: Extract Emails From Outlook. In order to get or extract meeting requests only, we need to target that item type. We can get info such as meeting subject, timing, duration, location, and the list of recipients. Furthermore, we can accept or decline meet invites automatically with Excel VBA. That´s all a bit more advanced and has been left out of the scope of this article. However, if you are interested to know more about it, you can read more and see examples in Excel Macros for Outlook.
As explained earlier, once the meeting invite is accepted, it becomes an appointment in the Outlook calendar. We can also get and extract all those appointments with Excel VBA.
Get Calendar Appointments
To get appointments we target the Calendar folder (olFolderCalendar) and loop through items in that folder as done in a previous post with the Inbox to get emails. We can then get any relevant info of the appointment. In the example below, we extract the subject, start time, duration, and list of recipients to the Excel worksheet.
Set OutFolder = OutApp.Session.GetDefaultFolder(olFolderCalendar)
For Each OutAppot In OutFolder.Items 'loop through items in calendar
With OutAppot
r = r + 1
Cells(r + 1, 1).Value = .Subject
Cells(r + 1, 2).Value = .Start
Cells(r + 1, 3).Value = .Duration
Cells(r + 1, 4).Value = .RequiredAttendees
End With
Next OutAppot
That retrieves all the appointments in the calendar, which can be quite a lot. We can narrow the list by targeting specific appointments only, for example using the Start property inside a conditional statement to target appointments for a specific time period (tomorrow, this week, etc).
We can use that information for example to see the availability in our calendar to send other appointments. We can also get the meeting response status for all the recipients in an appointment automatically, or move, cancel, or delete appointments. That´s all explained in Excel Macros for Outlook.
No comments:
Post a Comment