Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Jul 2015
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Excel dates to Outlook reminder

    Good Morning.


    I'm attempting to set up a starting log for research purposes and need some help. Few questions here for any masters!

    1. If this is my spreadsheet and I want to link the YELLOW highlighted dates to my outlook, so that it automatically sends me a reminder, but I also want it to pull the data from the whole row and include it in the reminder, is this possible?

    2. Will the reminders generate automatically, even if the file is not open, or do I need the excel file open for this to work?

    3. What happens to my outlook reminder if I modify a date? Will it cancel or do I have to manually cancel the old reminder?

    Thanks so much to anyone who is able to help!!!!
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    I found this link and thought it might help you.

    I ran the code on Office 2010 and created a reminder OK. I commented out a line that references a nextbusinessday function. Read his page.

    I cant guarantee anything.
    I don't know the author but he deserves credit for this.

    Cheers
    G

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Hi Trueborn,

    To answer your questions
    1. If this is my spreadsheet and I want to link the YELLOW highlighted dates to my outlook, so that it automatically sends me a reminder, but I also want it to pull the data from the whole row and include it in the reminder, is this possible? Very much so

    2. Will the reminders generate automatically, even if the file is not open, or do I need the excel file open for this to work? You need to open the file because that is where the code is located. The code to update the reminders occurs when the workbook opens therefore you can use task scheduler to open the workbook every morning if you like

    3. What happens to my outlook reminder if I modify a date? Will it cancel or do I have to manually cancel the old reminder? There is a way to remove the reminder automatically. I will work on updating that but with this version, it must be removed manually.

    Your request is specific to your sheet which would require many modifications if just pasting from another source. The attached spreadsheet will cycle through all your worksheets and create reminders for the columns you highlighted if the due date is <3 days out from the current date. The importance level will be set to high. It will create the reminder with all the data you requested then place a comment in the due date cell that is time stamped with the date that the reminder was created. The presence of a comment will inhibit a duplicate reminder being created if the code runs again. With this edition, you must manually delete the reminder as well as the comment if you modify the date.

    Things to remember:
    1. The 7th row must be the first study on each worksheet as you have it set up now.
    2. If you add additional worksheets, add them to the end. I will need to adjust the code to automatically detect which columns are the due date in the next update.
    3. Do not change the headers B6:F6 which are the same on all sheets.
    4. You can have unlimited number of rows of data.
    5. I changed the dues dates for testing. You may have to replace modified dates with your originals if needed
    6. Make sure your due dates are in date format
    7. You did not specify how long before the due date the reminder was to be created so I took a guess at 3 days. If you want something different, let me know.

    HTH,
    Maud

    trueborn1.png

    trueborn2.png

    Trueborn3.png

    trueborn4.png

    Code:
    Public OLook As Boolean
    Public Sub GetReminders()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, I As Integer
    '--------------------------------
    'CYCLE THROUGH EACH SHEET
    OLook = True
    For I = 1 To Worksheets.Count
        Worksheets(I).Activate
    '--------------------------------
    'GET COLUMN WITH DATE AND CALL SetReminders ROUTINE
        LastRow = Worksheets(I).Cells(Rows.Count, 1).End(xlUp).Row
        Select Case I
            Case 1
                SetReminders "G", LastRow
                SetReminders "H", LastRow
            Case 2
                SetReminders "I", LastRow
            Case 3, 4
                SetReminders "H", LastRow
        End Select
    Next I
    End Sub
    
    
    Public Sub SetReminders(col As String, lrow As Long)
    Dim I As Long
    For I = 7 To lrow
        If OLook = False Then Exit Sub
        If Cells(I, col) - Date <= 3 Then
    '--------------------------------
    'DECLARE AND SET VARIABLES
            Dim Task As ClsTaskEmail
            Set Task = New ClsTaskEmail
    '--------------------------------
    'SETUP TASK
            If Task.OutlookCheck = False Then Exit Sub
            Task.TaskStartDate = Cells(I, col)
            Task.TaskSubject = Cells(6, col) & ": " & Cells(I, col)
            Task.TaskBody = "Patient Name: " & Cells(I, 2) & Chr(13) & _
                            "Date of Birth: " & Cells(I, 3) & Chr(13) & _
                            "MR #: " & Cells(I, 4) & Chr(13) & _
                            "Study ID # : " & Cells(I, 5) & Chr(13) & _
                            "IRB#: " & Cells(I, 6) & Chr(13) & _
                            Cells(6, 7) & ": " & Cells(I, 7) & Chr(13) & _
                            Cells(6, 8) & ": " & Cells(I, 8) & Chr(13) & _
                            Cells(6, 9) & ": " & Cells(I, 9) & Chr(13)
            Task.TaskReminderset = True
            Task.TaskReminderDate = Date
            Task.TaskImportance = 2
            Task.TaskCreate
            Cells(I, col).ClearComments
            Cells(I, col).AddComment
            Cells(I, col).Comment.Text Text:="Reminder set " & Date & " " & Time
        End If
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-08-02 at 04:29.

  4. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    christine85 (2015-09-29),trueborn25 (2015-08-03)

  5. #4
    New Lounger
    Join Date
    Jul 2015
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you so much! That is amazing!

    One quick question. If I remove the code, then reassign it to a Command Button (ActiveX Control), this would allow me to run the code on will rather than automatically when file is opened?

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Yes, move the code to a standard module the assign a button or just run from macros in developer tab.

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

    trueborn25 (2015-08-03)

  8. #6
    New Lounger
    Join Date
    Jul 2015
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Great! Thanks so much. You are awesome!

  9. #7
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Linking Outlook and Lotus to Excel dates.

    Hi Maudibe,

    I have previously messaged you before privately. Thanks for the reply!

    Description of the scenario: We have few clients whom at various dates my team should send new contracts. The dates are not consistent. Usually we check the dates manually as per sheet 5 (Daily view), so if as per today's date there is client name (and client code, we send the contracts. So the other day we missed out a day, as apparently someone keyed in Client Name and code, for Sunday which is a non-working day.

    Solution: As most of my colleagues are Lotus users, and me and few are Outlook; could you please make it such that the reminder of the date appears in Lotus and Outlook. So the reminder should consist of " Client Name (Client Code)" . The first 2 sheets are 2 Products we have . I have deleted other 2 due to size limits for attachment. So the rows in red stand for Client Name and Code, and the columns are the dates. accordingly.

    Also a colleague suggested to have a function (within excel) where once you open an excel, it shows pending for today, for tomorrow and next 2 days (so we don't miss out weekends anymore).

    What do you think?
    Attached Files Attached Files
    Last edited by kkbermet; 2015-09-29 at 23:45.

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    KKbermet,

    I'll take a look at it this weekend.

    Maud

  11. #9
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    KKbermet,

    I'll take a look at it this weekend.

    Maud

    Thank you.

  12. #10
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    KKbermet,

    I'll take a look at it this weekend.

    Maud

    Hey Maud,

    I was just informed by IT that my team will be wholly shifting to Outlook by Mid October. Hence there is no need to explore the Lotus coding.

    Apologies for that.

  13. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    I have a spreadsheet, where I have entered the details in Col B in Excel and the due date of the task in Col C on sheet diary

    I have tried to amend you code , but the reminder dates on Excel as well as on outlook are incorrect

    It would be appreciated if you would kindly amend the code to show the reminder date as per Col C at 8am on the due date
    Attached Images Attached Images
    Attached Files Attached Files

  14. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Just checking whether you have had time to look at my problem

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Will do so today

  16. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Howard,

    Can you provide a sample of your workbook?

Page 1 of 2 12 LastLast

Posting Permissions

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