excel vba for sending calendar invite in outlook

[blackorchids2002]

Hi Masters,

Good day! I need your expertise on the how to loop by using range in my excel sheet.
I have check boxes where I assigned the ff:

Check Box1 : Full day ( Date From : input field is found in column D20) and Date To: input in column E20)
Check Box2: Half Day ( Date From : input field is found in column D21) and Date To: input in column E21)
Check Box3: Maternity ( Date From : input field is found in column D22) and Date To: input in column E22)
Check Box4: Bereavement ( Date From : input field is found in column D22) and Date To: input in column E22)


I want the loop code to be applied on ".Start" & ".End" codes below.

If they selected one of the checkboxes, let say Checkbox3, then the loop for ".Start" code should capture the "Date From" column and .End code to "Date To" column.


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

'to create a clendar invite
Set oApp = CreateObject("Outlook.Application")
Set oApt = oApp.CreateItem(1)

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

Appreciate all your help on this.

Thanks,
blackorchids2002

 

[JP2112]

You should be able to check the value of each check box like this:

Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value

If .Value = 1, Then take cell D20's value and apply it to the .Start property, and so on.