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

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

41534-trueborn1

41535-trueborn2

41536-Trueborn3

41537-trueborn4

Public OLook As Boolean
Public Sub GetReminders()
[COLOR="#008000"]'——————————–
'DECLARE AND SET VARIABLES[/COLOR]
Dim LastRow As Long, I As Integer
[COLOR="#008000"]'——————————–
'CYCLE THROUGH EACH SHEET[/COLOR]
OLook = True
For I = 1 To Worksheets.Count
    Worksheets(I).Activate
[COLOR="#008000"]'——————————–
'GET COLUMN WITH DATE AND CALL SetReminders ROUTINE[/COLOR]
    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
[COLOR="#008000"]'——————————–
'DECLARE AND SET VARIABLES[/COLOR]
        Dim Task As ClsTaskEmail
        Set Task = New ClsTaskEmail
[COLOR="#008000"]'——————————–
'SETUP TASK[/COLOR]
        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

Yes, move the code to a standard module the assign a button or just run from macros in developer tab.

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?

KKbermet,

I’ll take a look at it this weekend.

Maud

Will do so today

Howard,

Can you provide a sample of your workbook?

Sorry Howard, didn’t see the attachment to the right in post #11