Outlook/Excel

[robzrob]

Is it possible to link Outlook and Excel? For example, if a user puts
AL (Annual Leave) - or anything else - in their calendar on a day or
days, can I pick that up and manipulate it in a spreadsheet?

 

[JP]

You could set up an event in Outlook, so that whenever someone adds an
Appointment Item, scan it and look for certain keywords, and if found,
start Excel and copy whatever information you like to a spreadsheet.
Do you have any more information about your problem? Office version?

--JP

 

[robzrob]

You could set up an event in Outlook, so that whenever someone adds an
Appointment Item, scan it and look for certain keywords, and if found,
start Excel and copy whatever information you like to a spreadsheet.
Do you have any more information about your problem? Office version?

--JP



- Show quoted text -

Hello JP

My Outlook is 2000 (9.0.0.2711)

My Excel is 2000 (9.0.2720)

I'm hoping any solution (yours, I hope!) will allow me to work on it
at home but use it at work, where the version is later - 2003, I
think. Will that be a problem?

 

[JP]

I'd be glad to help, but there's a lot of information missing from
your request, such as

1) Do you want the code to run on demand, or on a real-time basis?
2) Do you want the code to run from Outlook, or from Excel?
3) Where would the person write "Annual Leave", in the body of the
calendar entry, or the subject, or the location?
4) What do you mean by "manipulate"?

You need to be as specific and detailed as possible so that any code
someone provides you will be relevant.

--JP

 

[robzrob]

I'd be glad to help, but there's a lot of information missing from
your request, such as

1) Do you want the code to run on demand, or on a real-time basis?
2) Do you want the code to run from Outlook, or from Excel?
3) Where would the person write "Annual Leave", in the body of the
calendar entry, or the subject, or the location?
4) What do you mean by "manipulate"?

You need to be as specific and detailed as possible so that any code
someone provides you will be relevant.

--JP






- Show quoted text -

1) I'd like it to run in real time
2) I'm not sure what the difference between running in Excel or
Outlook would make, I suppose I would like it to be in the Excel
Workbook I'll be using
3) The text would be in the Subject field
4) By manipulate, I mean that I've got a workbook which is a
calendar. I want this: if Joe puts AL in the subject field of the
26.06.08 window of his Outlook, then 'Joe, AL' will appear in the cell
of the Workbook corresponding to 26.06.08.

 

[JP]

It would need to run from Outlook, it just makes more sense that way
that when someone adds a calendar entry, the event code would pick it
up and start Excel.

Keep in mind this means you would need to install this code on every
users' machine that you want to receive this information from.

This should get you started. Place this code in the ThisOutlookSession
module of your Outlook. Then close and restart Outlook.


Private WithEvents CalItems As Outlook.Items

Private Sub Application_Startup()

Dim objNS As Outlook.NameSpace
Set objNS = GetNamespace("MAPI")
Set CalItems = objNS.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub CalItems_ItemAdd(ByVal Item As Object)

Dim Cal As Outlook.AppointmentItem
Dim XLApp As Object

Set Cal = Item

If InStr(Cal.Subject, "Annual Leave") > 0 Then

On Error Resume Next
Set XLApp = CreateObject("Excel.Application")
On Error GoTo 0

If XLApp Is Nothing Then GoTo ExitProc

XLApp.Workbooks("My Workbook.xls").Open

MsgBox "Your workbook is open"

XLApp.Quit

End If

ExitProc:
Set XLApp = Nothing
Set Cal = Nothing
End Sub


What this code does is check your Calendar whenever you add a new item
to the default "Calendar" folder. When a new appointment is placed, it
checks for the string "Annual Leave" in the subject. If it is found,
Excel is started and a workbook is opened. The sample code merely
displays a message box and then exits. You would need to adjust the
code for your particular needs.

For example, if you had a workbook on a shared network drive, change
"My Workbook.xls" to the full path and filename of the file you want
to add this information to.

HTH,
JP

 

[JP]

Correction:

It should be

XLApp.Workbooks.Open ("My Workbook.xls")

not

XLApp.Workbooks("My Workbook.xls").Open


Thx,
JP