Sending Outlook invite via Excel

[housemd]

Hi All,

I would like to send an outlook meeting invite via excel

I have the following code so for, which works for an appointment

Option Explicit

Private Sub meetingbooking()
    Dim objOL   'As Outlook.Application
    Dim objAppt 'As Outlook.AppointmentItem
    Const olAppointmentItem = 1
    Const olMeeting = 1


    Set objOL = CreateObject("Outlook.Application")
    Set objAppt = objOL.CreateItem(olAppointmentItem)
    With objAppt
        .Subject = "Meeting"
        .Start = "10/18/2014 09:30"
        .End = "10/18/2014 10:30"
        .Location = "xyz@company.com"
        .MeetingStatus = olMeeting ' make it a meeting request
        '.Attachments.Add attPath
        .Body = "This is a test-text in the message body..."
        .Display
    End With


    Set objAppt = Nothing
    Set objOL = Nothing


End Sub

I want the following changes to this code:

1. Make it a meeting instead of an appointment
2. Be able to book a room
3. Send the meeting request to another colleague of mine

Could anyone help me with how to proceed for these three changes?

Thanks in advance

 

[John_w]

1. Make it a meeting instead of an appointment
2. Be able to book a room
3. Send the meeting request to another colleague of mine

1. The .MeetingStatus = olMeeting line should already do that.
2. Set the .Location property.
3. Specify email address(es) in the RequiredAttendees or OptionalAttendees property.

Private Sub meetingbooking()
    Dim objOL   'As Outlook.Application
    Dim objAppt 'As Outlook.AppointmentItem
    Const olAppointmentItem = 1
    Const olMeeting = 1

    Set objOL = CreateObject("Outlook.Application")
    Set objAppt = objOL.CreateItem(olAppointmentItem)
    With objAppt
        .Subject = "Meeting"
        .Start = "10/18/2014 09:30"
        .End = "10/18/2014 10:30"
        .Location = "Meeting room"
        .MeetingStatus = olMeeting ' make it a meeting request
        .RequiredAttendees = "email1@email.com,email2@email.com"
        .OptionalAttendees = "email3@email.com"
        '.Attachments.Add attPath
        .Body = "This is a test-text in the message body..."
        .Display
    End With

    Set objAppt = Nothing
    Set objOL = Nothing

End Sub

 

[housemd]

Thanks John, that worked.

Now I am just trying to tweak even the code you gave and see what all enhancements I can make. Do you have any suggestions for the following?

1. How to check if the meeting room which I am booking is free or already booked (I should be able to check its availability but not sure how)

2. This might be a little farfetched, but is there anyway I can override an existing booking and book the room in my name instead? (This would be awesome if I can get it to work)

 

[housemd]

Any suggestions please?

 

[John_w]

Is this a shared calendar? If so then I don't know.

For your own Outlook Calendar, you could read Outlook appointments for your date and time and see if the meeting room 'belongs' to one of the appointments. You may have to handle overlapping appointments to see if the meeting room is available. There is code at http://www.mrexcel.com/forum/excel-questions/470308-excel-outlook-appointment.html which checks if an appointment exists. You would have to modify this for your exact needs. To override an existing booking you may have to delete or change the appointment related to the booking.

 

[housemd]

Is this a shared calendar? If so then I don't know.

For your own Outlook Calendar, you could read Outlook appointments for your date and time and see if the meeting room 'belongs' to one of the appointments. You may have to handle overlapping appointments to see if the meeting room is available. There is code at http://www.mrexcel.com/forum/excel-questions/470308-excel-outlook-appointment.html which checks if an appointment exists. You would have to modify this for your exact needs. To override an existing booking you may have to delete or change the appointment related to the booking.

Oh. So if someone else has booked the room and I do not have their credentials, then I cannot override the booking of the meeting room?

 

[John_w]

If by credentials you mean access to the other person's Outlook (Calendar), then I don't think you can override the booking without access to their Calendar. You may want to look at sharing calendars, e.g. Share your calendar information - Outlook, however I've never done this so can't really help any further.