Outlook VBA in Excel

[blackorchids2002]

Hi Masters,

This is the first time I will post this particular forum "General Excel Discussion & Other Questions".

I have created a vba code in excel to send an appointment/meeting in Outlook. I have a leave form request that I want to automate. For monitoring purposes in my calendar for those employee who will take their time off, I created below code.

Is it possible that the "Required Attendees" calendar status on the invite would be displayed as Out of Office" and the one who send the invite (which is the team lead) on the calendar status would be displayed as "Free or available"?

.BusyStatus = 3 (this is the Out of Office status)
.RequiredAttendees = (this is the employee who would take the leave)



Here is my VBA code in excel :

Sub test ()
Dim oApp As Object, oApt As Object

'to create a clendar invite

Set oApp = CreateObject("Outlook.Application")
Set oApt = oApp.CreateItem(1)

On Error Resume Next

With oApt
.Subject = "PTO REQUEST APPROVED"
.Start = Now()
.End = Now()
.MeetingStatus = 1
.RequiredAttendees = ThisWorkbook.Sheets("PTO Request Form").Range("J8")
.BusyStatus = 3
.AllDayEvent = True
.ReminderSet = False
.Attachments.Add ThisWorkbook.FullName
.Display
.Send
End With

On Error GoTo 0

'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oApt = Nothing
Set oApp = Nothing
End Sub


Your reply is highly appreciated.

Thanks,
blackorchids2002