Set up Outlook calender reminders from Excel

Topic

New Reply

I would like to do the following in MS Office 2010 and by no means am I well versed in Excel or Outlook; I have an Excel spreadsheet with a lot of information, specifically dates, and I see from previous posted that this has been done before but I am quite confused and find that copy and paste of the code simply does not work for me.

I would like to set up Office 2010 calendar reminders for dates in various columns of my spreadsheet (about 6 columns) and each of them set up a calendar reminder of “what” that column header indicates. Furthermore, I would have to have it tell me the “what” along with grabbing the first two columns’ information (client tracking number and client name). I am sure that it’s possible yet FML I don’t have a clue how to and if it is set up as a Macro because I do not have the Developer tab in Excel … Thanks!

Here’s the summary (as mentioned, I would like it to have the client tracking number from Column 1 and the client name from Column 2 in the calendar notification):

1. Look for date #1 (Column I: “Project End Date”) and set up a calendar reminder 7 days prior.
2. Set up a calendar reminder 2 days after receiving Order (Column H) to indicate “1st Files to Send”.
3. Set up an “if” function which will look for date #3 (if Column U has number >0) and set up a calendar reminder 7 days after this date re: “3rd Party letters to Send”.
4. Set up an “if” function that if there is an entry (date) in Column AB then set up a calendar reminder of 7 days prior to Column I re: “Arrange Monitors”.
5. Set up an “if” function that if there is an entry (date) in Column AC then set up a calendar reminder of 2 days after date in Column H re: “Confirm Monitors”.
6. Set up an “if” function that if there is an entry (date) in Column AC then set up a calendar reminder of 3 days prior to Column I re: “DDS”.

As you can see there are a lot of thing to track with my position and would appreciate the assistance if anyone can. My spreadsheet is just under 40 columns and just from Jan 1 of this year, there have been almost 100 projects to track. The information above is only the highest priority to keep on target.

Thank you all in advance.

Reply | Quote

Replies

ProjectMgr,

Welcome to the forum. Could you please provide a sample so we can see what you are describing?

Maud

Reply | Quote

Attached is a sample.
Thank you.

Reply | Quote

A really nice bit of code that I tested and it works is here:
http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/

I’ll am sure this can be worked into your request. Give us some time.

One initial line I see that needs to be modified is:

dteDate = NextBusinessDay(CDate(strDate), intDaysBack)

change to

dteDate = CDate(strDate) + intDaysBack

Maud

Reply | Quote

So in Excel I would set up a Macro and paste the code into it then?
Thank you in advance for your efforts!

Reply | Quote

The code is actually the macro or set of instructions to achieve some purpose. You paste the macro into a standard module, a worksheet module, a class module, or a form module and then have some mechanism to activate the code such as a button or an event.

I have been playing with the routine from the site that I linked and it will enable your request to be vary doable. It will need some modification but I’ll have something for you this weekend.

3. Set up an “if” function which will look for date #3 (if Column U has number >0) and set up a calendar reminder 7 days after this date re: “3rd Party letters to Send”.

Could you explain this more clearly and indicate what column date #3 is in? Is column U a number or a date?

Maud

Reply | Quote

That column is a number that I enter in manually and the date reflects something that I have to do if the number is >0

Reply | Quote

That part is clear but if Column U is a number in what column do I find the date #3 that depends on the value in U?

Here is what I have done so far. It will create reminders for all except Reminder number 3 which you need to clarify

36571-Tasks1

36608-PM3

To run the macro you must:
1. Outlook must be running but does not have to be the active window
2. select any cell on the line of the project you want to create reminders for
3. Click the “Create Reminders” button
4. Check Outlook for the reminders

Let me know if we are on the right track

Reply | Quote

Sorry, I have just realized my mistake. Column T (not date #3: copy/paste error) is a number for which if it is >0 then the Column U should have a date that reflects Column S +1 which is where I would need the notification in the calendar. Again, sorry about that and I will try this out so far tomorrow. Thank you for your efforts and time!

Reply | Quote

PM,

Thanks for that vital piece of info. I have completed the last task (total 6) and all should work now. The code has been modified from the original author’s code from a function to a subroutine and then altered to meet your needs. The specific tasks are originated from a calling routine and are then processed and outputted one at a time to Outlook. If you have to create new reminders, I have an idea for a “Reminder Builder Form”. Let me know how this revised code works out.

Maud

36582-workflow

Reply | Quote

Maud, this is awesome! Thank you so much for your assistance. As I am currently changing mobile phones (with my current one, I do not know if calendar tasks will remind me of these events), is there a way to have it populate directly into the Outlook calendar? or would it only be to the Task’s area? Also, is there a way for it to grab the Column A information and the Client name too? Again, I think what you did is fantastic!!!

Reply | Quote

PM,

I have added a couple lines of code that will add the Client and Well Location / UWI to each task which will display in the body of the reminder. I am sure that the task can be directly added to the calendar but I will have to research that a bit.

36607-PM2

In the meantime checkout the attached spreadsheet for the updates.

Maud

Reply | Quote

Looks and works great!

Reply | Quote

PM,

I amended the workbook as follows:

The name for sheet1 is changed to “Reminders” and remains unchanged to place task reminders in Outlook. Remember to select a cell on the row of the project you are working on.

A second sheet, “Appointments”, is a duplicate of “Reminders”, however, the code is changed to insert Appointments into the calendar. I have also added additional code to add the Client Name and Well Location/UWI to the body of the appointment. Like the reminders, you will have to select a cell on the row of the project you are working on.

I imagine that you will probably want to combine the reminders and the appointments into one single code and worksheet. If so, that will be Revision 5.

Maud

Reply | Quote

BTW, credit to the author of the code with some modifications made for your worksheet.

Reply | Quote

I really apprecate all of your hard work and effort put into this (and special kudos to JP too). This will really help me out when we ramp up our workflow again in a few weeks!

Reply | Quote

Thanks for the thanks PM

Reply | Quote

ProjectMgr,

I notice a comment in your first post that has not been addressed. You mentioned you do not have the Developer tab in Excel. That is very easy to fix! Simply go to File/Options/Customize Ribbon. In the column on the right-hand side (Customize the Ribbon), place a check mark next to Developer; then select the OK button and there you have it!

Reply | Quote

I have been testing and tweaking the code slightly and everything works fantastic! One question though, as I was testing I found that if these dates are in the calendar and I click the button again it duplicates the event; is there any line of code to put in so that this does not happen? i.e. so it does not put 2x the events in the calendar and maybe a pop-up generates indicating duplicate project?
Thanks!

Reply | Quote

PM, Will have an answer for you this weekend.

Maud

Reply | Quote

Thanks Maud.

Greg (PM)

Reply | Quote

Greg,

Due to technical difficulties, I was unable to remote into my work computer where Outlook is installed. However, I have compiled some additional untested code that should work to test if the appointments and/or tasks (reminders) have been created.

There are two ways to approach this:

1. The hard way: I have located some code that will actually test to see if there are the tasks/appointments already present in Outlook using the parameters in your workbook. It would need much modification to suit your workbook and since I do not have access to Outlook at present, I have focused on approach 2.

2. The easy way: A history of tasks/appointments can be created by placing the number 1 in a remote column on the spreadsheet for each project IF the code for that project has successfully added a task or appointment. Each time the code is run, it will for check that column (Column 52) for the history status for that project. If a blank is found (no history) then the code will run and produce the tasks/appointments and then update the history by placing a 1 in the column. If a 1 is found (column 52) then there is a history of the tasks/appointments being made. You will be presented with a message box indicating such whereby you can make a choice to:
a. Continue to create the task or appointment in which the code will run (Yes)
b. Decline to continue and the code will be halted (No)
To remove the history for that project, just delete the value in column 52.

I hope this addresses your question,
Maud

36663-project-overview2

Inserted are the following lines (blue):

Tasks (reminders):

Code:

Public Sub CreateTasks()
num = Selection.row
[COLOR=”#008000″]’TEST HISTORY STATUS OF TASKS[/COLOR]
[COLOR=”#0000FF”]If Cells(num, 52) = 1 Then
    Msg = “Task reminders for this project have aready been created.” & Chr(13) & _
    “Do you wish to create them any way?”
    Style = vbYesNo
    Response = MsgBox(Msg, Style)
    If Response = vbNo Then Exit Sub
End If[/COLOR]
AddToTasks Cells(num, 9), “Project End Date”, 7
AddToTasks Cells(num, 8), “1st Files to Send”, -2
If IsDate(Cells(num, 28)) Then AddToTasks Cells(num, 9), “Arrange Monitors”, 7
If IsDate(Cells(num, 29)) Then AddToTasks Cells(num, 8), “Confirm Monitors”, -2
If IsDate(Cells(num, 29)) Then AddToTasks Cells(num, 9), “DDS”, 3
If Cells(num, 20) > 0 Then AddToTasks Cells(num, 21), “3rd Party letters to Send”, -7
[COLOR=”#0000FF”]Cells(num, 52) = 1[/COLOR]
End Sub

Appointments:

Code:

Public Sub CreateTasks2()
row = Selection.row
[COLOR=”#008000″]’TEST HISTORY STATUS OF APPOINTMENTS[/COLOR]
[COLOR=”#0000FF”]If Cells(row, 52) = 1 Then
    Msg = “Appointments for this project have aready been created.” & Chr(13) & _
    “Do you wish to create them any way?”
    Style = vbYesNo
    Response = MsgBox(Msg, Style)
    If Response = vbNo Then Exit Sub
End If[/COLOR]
appointment = AddToCalendar(Cells(row, 9), “Project End Date”, “Office1”, #8:00:00 AM#, #9:00:00 AM#)
appointment = AddToCalendar(Cells(row, 8), “1st Files to Send”, “Office2”, #8:00:00 AM#, #9:00:00 AM#)
If IsDate(Cells(row, 28)) Then appointment = AddToCalendar(Cells(row, 9), “Arrange Monitors”, “Office3”, #9:00:00 AM#, #10:00:00 AM#)
If IsDate(Cells(row, 29)) Then appointment = AddToCalendar(Cells(row, 8), “Confirm Monitors”, “Office4”, #9:00:00 AM#, #10:00:00 AM#)
If IsDate(Cells(row, 29)) Then appointment = AddToCalendar(Cells(row, 9), “DDS”, “Office5”, #10:00:00 AM#, #11:00:00 AM#)
If Cells(row, 20) > 0 Then appointment = AddToCalendar(Cells(row, 21), “3rd Party letters to Send”, “Office6″, #10:00:00 AM#, #11:00:00 AM#)
[COLOR=”#0000FF”]Cells(row, 52) = 1[/COLOR]
End Sub

Reply | Quote

Thanks Maud,
I will try out Option #2 which should suit its purpose.
Much appreciate again!

Reply | Quote