Outlook Appointments

[Ian Mac]

All,

I'm in a bit deep here.

I have the following data:

Book1
	A	B	C	D	E	F	G	H
1	Pid	FullName	DateRequested	TimeRequested	DateOfHoliday	From	To	Manager
2	6074114	IanMac	26/01/2004	23:32	02/02/2004	17:00	22:00	Man1
3	6074114	IanMac	26/01/2004	23:32	03/02/2004	17:00	22:00	Man1
4	6074114	IanMac	26/01/2004	23:32	04/02/2004	17:00	22:00	Man1
5	6074114	IanMac	26/01/2004	23:32	05/02/2004	17:00	22:00	Man1
6	6074114	IanMac	26/01/2004	23:32	23/05/2004	19:00	22:00	Man1
7	6198766	JaneDoe	26/01/2004	23:32	14/05/2004	17:00	22:00	Man2
8	6198766	JaneDoe	26/01/2004	23:32	15/05/2004	17:00	22:00	Man2
9	6198766	JaneDoe	26/01/2004	23:32	16/05/2004	17:00	22:00	Man2
10	6543256	JimmyJones	26/01/2004	23:32	01/04/2004	17:00	18:00	Man1
11	6074114	IanMac	26/01/2004	23:32	29/09/2004	17:00	22:00	Man1
12	6074114	IanMac	26/01/2004	23:32	30/09/2004	17:00	22:00	Man1
Sheet1

I would like to have the information transferred to a manager's Outlook Appointments.

It will be controlled by a button on a user form.
Firstly it needs to identify the manager (this I can do from their username)
Next I would like each Item to be added to Outlook?!!

The information would be:

Fullname & " Holiday" = Subject
DateOfHoliday = Date in calender
From = Start Time in Calender
To = End Time in Calender
I also need to label it i.e. Vacation and colour the item (not super doopy important! but a wish)

How?
Also, I will eventually have 1,000's of items, is this going to be a slow process?
Might it be better to sort the data and export a CSV then import from Outlook (via Excel code)

Here is another problem.
My data also has another column (Cancelled)
How could I then delete any items from Outlook which have been cancelled? 1 or 0 (1 being Cancelled)

Any ideas, anything for even a step, nay leap towards the right direction.

Kindest regards

 

[Damon Ostrander]

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

 

[dk]

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

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)

        With olAppointment
            .Subject = shtSource.Cells(lngRow, 2) & " Holiday"
            .Start = DateValue(shtSource.Cells(lngRow, 5))
            .Duration = 100
            .Save
        End With

    Next lngRow

End Sub

 

[Ian Mac]

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,

 

[Ian Mac]

In addition.

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

Regards