Outlook Appointments

Hi Ian,

I don't have time to give you sample code for this right now, but I just want to assure you that this is possible using Automation from Excel, and this should be very efficient--much better than the csv approach you mentioned.

It would be a good idea to explore the subject of Automation in the Outlook Visual Basic Editor. Incidentally, Outlook uses VB script, not VBA (VB script is a subset of VBA). When you are ready to write your code in Excel, to make things easier (for example, to make Outlook object model helps available from Excel) you should go to Tools > References and load the Outlook object library (example, "Microsoft Outlook 9.0 Object Library").

I hope this at least gets you started.

Damon

Following on from Damon's post. This code will hopefully give you an idea of how to automate Outlook. I think the trickiest part is getting the start and finish dates correct. There is no End date within the object model for an AppointmentItem, just a start date and a duration property (duration is in minutes). This should work with the data you posted although I haven't bothered to calculate the correct duration - it just puts each one in as 100 minutes. Let us know how you get on

Damon,

Excellent, I was looking around the object browser in Excel,
hadn't even thought of looking at Outlook, and found to my suprise an excellent insite to VBA/VBScript (a must point to for beginners (like me)) I will be reading this, very good information on Automating.

dk,

Top stuff! I have changed it slightly to include the start time
strange it's not there as default as it's a filed on the form, as is end time
Also selecting the manager.

Sub AddToOutlook()

'!! Reference to Outlook object library required !!

Dim olAppointment As Outlook.AppointmentItem
Dim olApp As Outlook.Application
Dim lngRow As Long, shtSource As Worksheet


'Get reference to MS Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
End If

On Error GoTo 0

Set shtSource = ActiveSheet

For lngRow = 2 To shtSource.UsedRange.Rows.Count

Set olAppointment = olApp.CreateItem(olAppointmentItem)

'i'll be using the username
If shtSource.Cells(lngRow, 8).Value <> "Man1" Then
Else
With olAppointment

.Subject = shtSource.Cells(lngRow, 2) & " Holiday"
.Start = CDate(DateValue(shtSource.Cells(lngRow, 5)) + shtSource.Cells(lngRow, 6))
.Duration = (shtSource.Cells(lngRow, 7) - shtSource.Cells(lngRow, 6)) * 1440
.Save
End With
End If

Next lngRow

End Sub


I've had a look around a bit to see how I can delete an item if I'm placing one in.
It would only need to look at the subject "Ian Mac Holiday", as it isn't possible to have the same person off twice.

Also, I'm still a little concerned about how long this might take to execute when there are 10,000+ rows of data!

Thanks for the pointer both have helped lots

kindest regards,

In addition.

In my outlook poor old Jimmy Jones currently has 14 items for April Fools day.
And climbing with each test

Regards