Excel cell date to Outlook
Guest
Posts: n/a
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
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
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Join Date: Mar 2007
Location: boston
Posts: 1
Likes: 0
Received 0 Likes
on
0 Posts
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!!
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!!
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
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
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
