1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Automatic Outlook mail due date reminder based on Excel file

Discussion in 'Business Applications' started by reddy12345, Jun 10, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    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...
     

    Attached Files:

  2. Sponsor

  3. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    Requirement is to send a mail 7 days before the due date mentioned in the excel sheet..


    please suggest
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    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
     

    Attached Files:

  5. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    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
     
  6. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    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. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    Thanks for the detailed explanation..
    I'm attaching the file for your reference..

    Please share the date format which I have to use..

    Thanks in advance..
     

    Attached Files:

  8. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    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.
     

    Attached Files:

  9. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    After changing the clock region from US to India dd-MM-yyyy, it is working fine as expected...

    Thanks a ton for your quick support..
     
    Last edited: Jun 13, 2016
  10. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Amit ji

    im not able to test task scheduler right now but once I am I will let you know how to implement.

    Do you have a preferred time you want this to be run?
     
  11. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    I have created the task successfully to open the workbook..

    Will test and update you by tomorrow..
     
  12. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    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?
     
    Last edited: Jun 14, 2016
  13. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    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.
     
  14. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    is there any other option ?.
     
  15. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Not that I am aware of, sorry
     
  16. reddy12345

    reddy12345 Thread Starter

    Joined:
    Jun 10, 2016
    Messages:
    12
    Hi,

    I have a Due date as below format:

    upload_2016-7-19_8-58-9.png

    1. Do I need to change anything in the code
    2. What region format I have to select

    Please suggest
     

    Attached Files:

  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1172695