Excel to add event on outlook calender

[Dan Wood]

Is there a way to get excel to add an event onto an outlook calender?

My spreadsheet is set up to have a few possible fields, but i want when
'Annual Leave' is selected, an outlook calender all day event to be added.

Excel and outlook will be open at the same time so nothing needs opening.

 

[Dan Wood]

I have the macro now to add the event onto my calender.

Sub Add_Appointment()
Dim myOlapp As Object
Dim myitem As Object

Set myOlapp = CreateObject("Outlook.Application")
Set myitem = myOlapp.createitem(1)

With myitem
.Body = "Annual Leave."
'.Duration = dur'
.AllDayEvent = True
.Subject = "A/L"
.Save

End With

Set myitem = Nothing
Set myOlapp = Nothing

End Sub

What i now would like to know is:-

1) A code to run this macro if a field is changed, for example if a cell
between range b1 to b14 changes to A/L i want this to run and add the
appointment into my calender. This could also be done by clicking a button
after any updates have been made.

2) What field do i need to change to select the calender i want this added
to, i can get this to add to my calender, but eventually want it to add to a
shared calender.

3) Is there a way to only add new appointments, and not repeating the old
ones.

Thanks in advance