It is posisible but I think it needs to be done the opposite way.


The macro needs to be written in Outlook VBA not excel VBA. When the event
is finished that event would be recognized in Outlook VBA (not excel VBA).
Since it is possible to open an excel workbook from Outlook it would be
better that.

You will need to setup a module code in Outllok to create a calendar event.
This code would open the workbook. You will also need a class module
inoutlook so when the calendar event occurs again open the workbook up a
enter into the workbook that the event completed.

I sample of the code to create the event is shown below. It may be better
to ask additional questions about outlook at the outlook programming website.


Public Sub cmdExample()

Set excelobj = getobject("c:\Myappointment\book1.xls")
MyDate = excelobj.sheets("sheet1").Range("A1")


Set myOlApp = New Outlook.Application
Set myApptItem = myOlApp.CreateItem(olAppointmentItem)
myApptItem.Start = Mydate
myApptItem.End = #2/2/1998 4:00:00 PM#
myApptItem.Subject = "Meet with Boss"

'Get the recurrence pattern for this appointment
'and set it so that this is a daily appointment
'that begins on 2/2/98 and ends on 2/2/99
'and save it.
Set myRecurrPatt = myApptItem.GetRecurrencePattern
myRecurrPatt.RecurrenceType = olRecursDaily
myRecurrPatt.PatternStartDate = #2/2/1998#
myRecurrPatt.PatternEndDate = #2/2/1999#
myApptItem.Save

'Access the items in the Calendar folder to locate
'the master AppointmentItem for the new series.
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderCalendar)
Set myItems = myFolder.Items
Set myApptItem = myItems("Meet with Boss")

'Get the recurrence pattern for this appointment
'and obtain the occurrence for 3/12/98.
myDate = #3/12/1998 3:00:00 PM#
Set myRecurrPatt = myApptItem.GetRecurrencePattern
Set myOddApptItem = myRecurrPatt.GetOccurrence(myDate)

'Save the existing subject. Change the subject and
'starting time for this particular appointment
'and save it.
saveSubject = myOddApptItem.Subject
myOddApptItem.Subject = "Meet NEW Boss"
newDate = #3/12/1998 3:30:00 PM#
myOddApptItem.Start = newDate
myOddApptItem.Save

'Get the recurrence pattern for the master
'AppointmentItem. Access the collection of
'exceptions to the regular appointments.
Set myRecurrPatt = myApptItem.GetRecurrencePattern
Set myException = myRecurrPatt.Exceptions.Item(1)

'Display the original date, time, and subject
'for this exception.
MsgBox myException.OriginalDate & ": " & saveSubject

'Display the current date, time, and subject
'for this exception.
MsgBox myException.AppointmentItem.Start & ": " & _
myException.AppointmentItem.Subject
End Sub



"Jim" wrote:

I'm using Excel Office 2007. I would like a VBA code to create a task in
Outlook based on a date in an Excel workbook. Once the task is completed, I
would like that completion date entered into the Excel Workbook. Is this
possible?

My initial date is located in the workbook 'Loan Book', worksheet 'Loan
Data' cell CN9. I would like the task completion due date to be four days
prior to the sheet date. Once the task is completed, I would like that
completion date CP9.

Thanks in advance for any advice.