• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate TAT & Send reminders with help of MACRO

Gaurang Mhatre

Gaurang Mhatre

Member
Hi Friends,

Hope you guys are doing good...:)

Need your help!....

There is a project plan which i am doing and for it i have made an excel sheet.

Basically for this piece a Macro is needed, when I enter a date in particular column the TAT should work according to the date inputted. For eg TAT is T+2 and I put date as 1-08-14 then TAT should reflect in TAT column as 3-08-14 pertaining to activity mentioned in the sheet and the same should be synchronised in OUTLOOK reminder and mail should be sent along with reminder to the person handling it. In short, a button in excel sheet that will refresh the dates enter and calculate TAT accordingly as per the date and also sends email with reminder in outlook.

Is there a macro for it. Is it possible??

Would like to know a macro which would refresh the data and will send outlook reminders with email to the person handling that project.

Will appreciate if you could help me in this...

Thanks for your help in advance.

Regards,
Gaurang.
 

Attachments

  • Project Plan - Collections.xlsx
    13.5 KB · Views: 26
Forgot to add

Paste the below code in the workbook
Sub SendRemainders()
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet

Set wbk = ThisWorkbook
Set wsht = wbk.ActiveSheet

Dim OutlookApp As Object
Dim OutLookMailItem As Object
Dim counter As Long

Set OutlookApp = CreateObject("Outlook.Application")
Set OutLookMailItem = OutlookApp.CreateItem(olTaskItem)
counter = 5
Do Until wsht.Cells(counter, 1) = ""
Set OutLookMailItem = OutlookApp.CreateItem(olTaskItem)
With OutLookMailItem
.Assign
.Recipients.Add (wsht.Range("H3").Value)
.Subject = wsht.Cells(counter, 2).Value
.Body = EmailContent
.attachments.Add ThisWorkbook.FullName
.importance = 2
.ReminderSet = True
.ReminderTime = wsht.Cells(counter, 3).Value
.DueDate = DateValue(wsht.Range("F3").Value)
.Display
.Save
.Send

End With
Set OutLookMailItem = Nothing
counter = counter + 1
Loop
Set wbk = Nothing
End Sub
 
Hi Dinesh,

Thanks for taking out your time and able to solve my query.
God Bless..
I hope this solution which dinesh shared will help others too...

Regards,
Gaurang
 
Hi Dinesh,

Debug error reflecting in macro!!
Can you help me with it, i mean why it is showing debug error. rest i will do.

Actually the end date which you have highlighted in macro is the “start date” cell.
Is it possible to set reminders in outlook as per the TARGET date assign??

Please find attached snapshot of the error.

Thanks

Gaurang
 

Attachments

  • Debug Error.jpg
    Debug Error.jpg
    180.7 KB · Views: 10
Last edited:
Hi Gaurang,

yes you can change the code to target date by referring to that cell. On the debug error I tested the code in my computer it works well. Which version of Outlook do you have? and also can you post the error description.you can get the error description by typing err.description in immediate wondow.

-Dineh
 
Hi Dinesh,

I am using Outlook 2010. i tried the code but it still shows the error.
is there any other code for outlook 2010 or something?
Regards,
Gaurang
 
Hi Dinesh,

As requested, I have attached 3 snapshot files of the errors reflecting while running macro.

As I said earlier I am using OUTLOOK 2010.

When I run the macro it shows run time error 438 (error 1 image) when I click on debug option it highlights “assign” (error 2 image).

Then when I delete that assign part, it gives another debug, once I click on that debug option it highlights (error no 3 image) Duedate piece.

This are the types of error which I am getting when I run the macro.


Really in need of this macro, Thanks a lot for helping me out buddy :)

Regards,
Gaurang
 

Attachments

  • Error1.jpg
    Error1.jpg
    196.4 KB · Views: 4
  • Error2.jpg
    Error2.jpg
    203.2 KB · Views: 4
  • Error3.jpg
    Error3.jpg
    176.3 KB · Views: 3
Hi Gaurag,

Can you type OutLookMailItem. and see whether it has assign property? type after
Set OutLookMailItem = OutlookApp.CreateItem(olTaskItem)
-Dinesh
 
Hi Dinesh,

I guess I have typed OutLookMailItem in wrong area. Thus its not working still.

Attached is the sheet for your reference.


Regards,
Gaurang
 

Attachments

  • Project Plan - Collections.xlsm
    19.2 KB · Views: 17
Hi Gaurang,

Can you test the below code . It is the same as I originally posted I have added a extra line (Const olTaskItem = 3). Let me know if it works and if you want to change the due and remainder dates.
Code: 
Sub SendRemainders()
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet

Set wbk = ThisWorkbook
Set wsht = wbk.ActiveSheet

Dim OutlookApp As Object
Dim OutLookMailItem As Object
Dim counter As Long

Const olTaskItem = 3

Set OutlookApp = CreateObject("Outlook.Application")

counter = 5
Do Until wsht.Cells(counter, 1) = ""
Set OutLookMailItem = OutlookApp.CreateItem(olTaskItem)
With OutLookMailItem
.Assign
.Recipients.Add (wsht.Range("H3").Value)
.Subject = wsht.Cells(counter, 2).Value
.Body = EmailContent
.attachments.Add ThisWorkbook.FullName
.importance = 2
.ReminderSet = True
.ReminderTime = wsht.Cells(counter, 3).Value
.DueDate = DateValue(wsht.Range("F3").Value)
.Display
.Save
.Send

End With
Set OutLookMailItem = Nothing
counter = counter + 1
Loop
Set wbk = Nothing
End Sub
 
Last edited:
Hi Dinesh,

It is working buddy, thanks!

So tomorrow, if I email this sheet to respective managers and ask them to run macro on their own after updating the details (dates) and ask them to click on “run macro”, so that reminders will be reflected automatically on the screen. But if you see the reminder message dialog box keeps on opening as there are 12 Sr.No i.e. 12 activities thus 12 reminders will open at one go..and all the time I have to send it.

Is there any other method where the macro will capture all 12 activities in one mail and will send reminders according to the TAT i.e. only 1 email message box should open for all the 12 reminders. I guess it’s not possible. But just curious to know.

I Appreciate the efforts put by you on this. Really hats off! :)

God bless u !!

Regards,
Gaurang
 
Hi Gaurang,
Finally its working after a week's time. Sending multiple remainders is not possible if we do manually so we cannot implement the same using vba.


Thanks,
Dinesh
 
Yup, appreciate your efforts for this...Thanks alot brother...
Hats off to you!!

God bless you in your career and in life :)

Regards,
Gaurang
 
Buddy,

forgot to mention one query,
how to add cc, and how to select range for TAT as the TAT showing the same date for all the activities. TAT Dates should reflect as per the activity.
what will be the code.

Thanks!

Regards,
Gaurang
 
Hi Gaurang,

We cannot add CC to a task item. I am not sure.To change the TAT change the due date as below
.ReminderTime = wsht.Cells(counter, 3).Value
.DueDate = wsht.Cells(counter, 3).Value

-Dinesh
 
You must log in or register to reply here.
Back
Top