Excel cell date to Outlook
 

Can Outlook be prompted to issue a reminder from a date in a spreadsheet? I use the spreadsheet every day.

I have all my rating expiry dates, passport expiry date, mother's birthday etc listed on a spreadsheet but would like a reminder (or similar) to be sent to my inbox.

Any recommendations appreciated.

DT

But most of them don't reccur annually (except Ma's birthday, of course!)

DubTrub,

It is possible, but non-trivial. It requires a bit of Excel VBA. You will need to write some code to select the data. For each appointment you will need:

1. subject - string - MySubject
2. Date - date- MyDate
3. Reminder delay (mins before) - long - MyDelay

The code to create the reminder/appointment is:


Sub CreateAppt()
Dim objOL As Outlook.Application
Dim objAppt As Outlook.AppointmentItem


Set objOL = CreateObject("Outlook.Application")
Set objAppt = objOL.CreateItem(olAppointmentItem)
With objAppt
.Subject = MySubject
.ReminderSet = True
.Start = MyDate
.AllDayEvent = True
.ReminderSet = True
.ReminderMinutesBeforeStart = MyDelay
.Save
End With


Set objOL = Nothing
Set objAppt = Nothing
End Sub


This should get you on your way.

EG

Thanks for that, ExG

It looks a bit too complicated for my little brain, so I think I'll dream up some other resolution.

VBA questions
 

Hi,

This posting is exactly what I'm trying to do, but I don't have a ton of experience with VBA. I've titled the pertinent column headings 'MySubject, MyDate, and MyDelay, and pasted your code into the VBA editor for this sheet.

No reminder has appeared in my outlook. Can you tell me what I missed?

Thanks!!

Hi ces,

The non trivial bit, which I have not included, is the selection of the data for the variables. The code has to select data from each column, then check that there is not already an appointment with the same details. Equally there is the issue of how to handle changes in the data. If you are not careful you will end up with dozens of duplicate appointments. To write all the code needed is quite a lot of work. All this can be is a pointer in the right direction.

Bearing in mind the above, the bit you are missing is to assign a value in a column to the appropriate variable. So for a simple example, if you have a worksheet called Data with an appointment in row A, with date in A1, subject in A2 and delay in A3 then the code would be:

Sub CreateAppt()
Dim objOL As Outlook.Application
Dim objAppt As Outlook.AppointmentItem

MyDate = Worksheets("Data").Range("A1").Value
MySubject = Worksheets("Data").Range("A2").Value
MyDelay = Worksheets("Data").Range("A3").Value

Set objOL = CreateObject("Outlook.Application")
Set objAppt = objOL.CreateItem(olAppointmentItem)
With objAppt
.Subject = MySubject
.ReminderSet = True
.Start = MyDate
.AllDayEvent = True
.ReminderSet = True
.ReminderMinutesBeforeStart = MyDelay
.Save
End With


Set objOL = Nothing
Set objAppt = Nothing
End Sub