Tech Support Guy banner
Status
Not open for further replies.

Automatic Outlook mail due date reminder based on Excel file

3K views 19 replies 3 participants last post by  Stella123 
#1 ·
Hello all,

I'm receiving mail alert based on date only, it is not considering the month into the account.. Kindly suggest..

Attached the excel file with macro...
 

Attachments

#3 ·
please check the attached, what I have done is rewritten the VBA code as the previous one was a little confusing.

On the attached as I have change two dates column 2 and column 16

The code will generate the below emails based on the dates

16.06.2016
Code:
Dear Amit ,
Reminder Project ABC is due in 6 days
08.01.2015
Code:
Dear Jill,
Reminder Project FGH was due -519 days ago
Please note that currently the code will DISPLAY the emails, if you want them to be automatically sent then change .display to .send

When you open the workbook the code should start
 

Attachments

#4 ·
Thanks for your support..

i'm receiving the mails for all the projects like below,But my requirement is to receive the mails only the projects due date is 7 days before..

Reminder Project ABC is due in 3 days
Reminder Project DEF is due in 64 days
Reminder Project XYZ is due in 66 days

Please suggest
 
#5 ·
There must be something wrong with the way you are entering the dates. OR the date formats must be wrong, the code looks at 3 conditions

if the date difference is above or equal to 0 (Zero) AND the date difference is below or equal to 7

OR the date difference is below 0 (Zero)

It will not do anything if the difference is outside of these conditions

please update the file you are working on so I can check the dates as without seeing it I cannot explain what is happening, I have tested it on my machine and it works correctly.
 
#7 ·
So from the attached workbook, the date difference between today and the dates are below. From the code I have written, only one email should be generated and that would be for the -10 row.

For me this is exactly what is happening, only one email is being generated.

Can you go into control panel
Click on 'Clock, Language and Region settings'
Click 'region and Language'

Please check what Format is selected. I have "English United Kingdom" perhaps you have another switch switches your month and date around?

UK is dd/mm/yyyy
US is mm/dd/yyyy

Iv seen this to cause issues for some people.

Code:
Date        Date Difference
03.06.2016    -10
27.07.2025    3331
03.06.2017    355
10.07.2025    3314
07.08.2016    55
24.07.2025    3328
03.06.2017    355
10.03.2025    3192
14.07.2016    31
03.06.2017    355
10.03.2025    3192
14.07.2016    31
10.03.2025    3192
04.08.2016    52
08.06.2017    360
04.10.2016    113
03.10.2025    3399
04.08.2016    52
08.06.2017    360
04.10.2016    113
18.07.2025    3322
29.09.2016    108
06.10.2016    115
26.03.2026    3573
06.04.2025    3219
04.04.2025    3217
04.04.2025    3217
04.04.2025    3217
Having said that I have made a small change to the code see if this helps first. Please check the attached.
 

Attachments

#11 · (Edited)
I have created a task scheduler to run everyday at 4 PM,.. and observed that the task is just opened the workbook.. & then i need to enable the macro to run..

How to avoid this manual work..

Requirement:
I have a macro in excel which needs to be run once a day and I don't even want to open the excel sheet. How can I make this process automatic?
 
#12 ·
open Excel
go to file > options
Select Trust Centre > Trust Center Settings
Select Macro Settings
Select "Enable all macros (not recommended.....")

This will then ensure all macros are enabled as soon as a workbook is open.

However this can be potentially harmful if you receive a workbook with malicious macros.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top