Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Set up Outlook calender reminders from Excel

    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.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    ProjectMgr,

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

    Maud

  4. #3
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Attached is a sample.
    Thank you.
    Attached Files Attached Files

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    A really nice bit of code that I tested and it works is here:
    http://www.jpsoftwaretech.com/using-...rs-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
    Last edited by Maudibe; 2014-03-21 at 19:55.

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

  7. #5
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    So in Excel I would set up a Macro and paste the code into it then?
    Thank you in advance for your efforts!

  8. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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
    Last edited by Maudibe; 2014-03-22 at 00:47.

  9. #7
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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

  10. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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

    Tasks1.png

    PM3.png

    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
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-24 at 21:53.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

  12. #9
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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!

  13. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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

    workflow.png
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

  15. #11
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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!!!

  16. #12
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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.

    PM2.png

    In the meantime checkout the attached spreadsheet for the updates.

    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-24 at 21:47.

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

  18. #13
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Looks and works great!

  19. #14
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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
    Attached Files Attached Files

  20. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

  21. #15
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    BTW, credit to the author of the code with some modifications made for your worksheet.

  22. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-03-25)

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •