# Outlook VBA in Excel



## blackorchids2002 (Jul 24, 2012)

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


----------

