Using excel to create task reminders in outlook

Topic

New Reply

Hi all
I’ve been trying to adapt macros in the forum for this but have failed by myself.

I’ve got a spreadsheet in Excel 2010 which is updated most days. When a new entry is put in I was trying to use vba to create a reminder in outlook.
The information needed is in column B (subject), column A (body) and for it to be the working day before the expected date in column E
The existing information is stored in this sheet as a record.

There are multiple worksheets, some (or occasionally all) are updated daily.
If anyone can help that would be great.

EDIT: this spreadsheet is on a shared drive and there are 3 people who will edit it. It’s fine if all reminders go to one person but I wouldn’t want to duplicate it to all 3!

Reply | Quote

Replies

Perhaps the below can link can provide assistanc

http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/
Also from the site
About JP
I’m just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there.

TD

Reply | Quote

I tried that but didn’t have much luck. have spent some time going through tutorials the last couple of days though so I’ll give it another go. Thanks

Reply | Quote

George,

Using a class that I wrote for task reminders, here is your modified workbook that will create reminders set for the day prior to the date in column E and due on the date in column E. The subject and body are extracted from the columns you specified. The code will prompt you to open Outlook if closed. The code will cycle through the rows and once a reminder has been created, an “X” is placed in column G to inform you and the code not to create a duplicate task reminder. Note: Column E must be formatted as a date

HTH,
Maud

40993-FreightGeorge1

40994-FreightGeorge2

Code:

Public Sub SetReminders()
[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
LastRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For I = 3 To LastRow
    If Cells(I, 7)  “X” Then
        Task.TaskStartDate = Cells(I, 5)
        Task.TaskSubject = Cells(I, 2)
        Task.TaskBody = Cells(I, 1)
        Task.TaskReminderset = True
        Task.TaskReminderDate = Cells(I, 5) – 1
        Task.TaskImportance = 2
        Task.TaskCreate
    End If
    Cells(I, 7) = “X”
Next I
End Sub

Reply | Quote

Thank you, that is really helpful of you.

I’ve just got a couple of questions on that attachment.
I’m actually in the UK so I’ve changed the date to our format (dd/mm/yy) and as that doesn’t display time of arrival I’ve put that in a separate column beside it. Just trying to avoid the error I think would happen with input.

The X was actually coming up in column F, in the file so I’ve put Cells (I, 8) = “X” to shift it over and account for my additional column

When I’ve tested it with multiple lines though it is only picks up on the last entry. Is it possible to get it to add multiple reminders if had say 4 shipments or should I just run the macro after each entry?

Reply | Quote

George,

I revised the workbook so that it will create multiple reminders when the code runs. If you want the reminder to be created a second time, delete the “X”. Also, column E is formatted as dd/mm/yyyy hh:mm so the column with the time alone has been removed.

Let me know if there needs to be anymore adjustments.

Maud

Reply | Quote

Awesome, many thanks

Reply | Quote

Awesome, many thanks

hi

i was trying to use your code to add a start date but i was unsuccessful
would you be able to modify this code that it should have a start date to

Reply | Quote

i’m sorry i meant if you can revise it to include due date it seems to be taking the start date and making it the due date to

Reply | Quote

BB,

Can you post a sample of your workbook with the dates you are mentioning?

Maud

Reply | Quote

BB,

Can you post a sample of your workbook with the dates you are mentioning?

Maud

HI
IT Would be hard for me to attach a workbook i’m referring to the workbook that you uploaded freight George etc..

Reply | Quote