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 'Software Development' started by vetrivelumani, Jun 3, 2016.

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

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    Hi Everyone!

    This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a payment is 10 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

    In the attached excel file, An email should go of to -email address (Col. Q), with subject "Vendor Payment (Col. C) is due on Due date(Col. M)", and body "Dear Name(Col. P), please update the payment status".

    Also, the script should put a check mark on Reminder sent column (Col. Q) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

    I have scored the forum for similar problems, and although I found a number of threads, I am not proficient enough in VBA to modify them to my needs.

    I'd really appreciate any help,
     

    Attached Files:

  2. Sponsor

  3. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Seems like a lot of people wanted automated emails from Excel recently :confused: iv written quite a few here. You requirements are really simple to execute. However I have one issues

    I don't quite understand how this will work, if the workbook is not running then, you will need something else running in order to query the data in the workbook and generate emails accordingly otherwise how would the software know how and what to do?

    everything else is doable, the only option I can suggest is having a timer in the excel which will continuously run, say every half hour or hour, or whatever interval you want, and recheck the data.

    Have a think and if you have suggestions please let us know. Then we can try coding this up.
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    I have coded the requirement on the attached, however this will only generate emails when you open the file, I am waiting for your response for how this should be triggered. Note that the emails this generates are displayed for you to send, if you want them to send automatically then just change the .display to .send

    Also note that there was no reminder sent in column Q so I added it to column R. This does not have a check mark, I have opted for a date stamp so that you can tell when the reminder was sent.

    I have also made an assumption that the same email should not be sent more than once per day, therefore the code will check the date stamp per row and if the date stamp is todays date it will not send another email to that vendor, however the following day it will send another email. let me know if this is not what you expect .........
     

    Attached Files:

  5. vetrivelumani

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    Really Appreciate your quick help. But it is not working when I opened the work book. What ever you have mentioned is right ( the email would be sent only if the work book is opened).

    I have attached updated file with my e-mail id for your reference. Pls help.
     

    Attached Files:

  6. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    When you open the workbook you need to enable macros, depending on the version of office you are using you may get a display box asking to enable macros, or a yellow bar across the top of excel with an 'enable content' button., if you click this and enable content it will open and generate two emails.
     
  7. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    So from your attached workbook I get the following email being generated, this is because the due date is LESS than 10 days from today...
     

    Attached Files:

  8. vetrivelumani

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    I did the same, but it is not working. pls hep
     
  9. vetrivelumani

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    yes, i have change the date to check whether it is working or not. could you let me know why this is not working in my system.
     
  10. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    In the past when iv had this issue it is usually because of the date format of the system, how does the date in your system tray appear? are you in the UK?
    my system clock shows 03/06/2016

    Another thing to try is to clear the dates in column R and then save and reopen
     
    Last edited: Jun 3, 2016
  11. vetrivelumani

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    Hi,

    I have tried all the way to see the output which is not working. I have attached my actual format. Please help me. Basically I want the mechanism to send an automated outlook mail to SPOC id, 10 days

    prior to payment due date. Pls help me
     
  12. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    That is exactly what I am trying to do, I asked you what your system date looks like but you haven't told me....

    Do the following:

    Go to control panel
    Go to 'Clock, Language, and region'
    Click 'region settings'

    and have a look at the format what does it say???

    Mine is English (United kingdom)
     
  13. vetrivelumani

    vetrivelumani Thread Starter

    Joined:
    Jun 3, 2016
    Messages:
    6
    Sorry, i am in Bangalore - India. attached screen shot for your refernce pls.

    The format which is show's in my system is English (India)
    Short Date: 06-06-2016
    Long date: 06 June 2016
    Short time: 15:10
    Long time: 15:10:29
     

    Attached Files:

  14. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    That should in theory work then, not sure what this isn't working for you. it works perfectly for me. have you tried to delete the entries in column 'R', save and reopen?
     
  15. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Did this work for you in the end?
     
  16. 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/1172280