VBA code for automatic email if prompt date and due date reached

  • Hello, i have the attached spreadsheet where by i am trying to create a code that will send one of two emails when either the due prompt date is reached or the due date is reached. The subject for the prompt date should be 'Task Reminder' and the subject of the due date if reached should be 'Task Due'. the body of the text should simply be the contents of what is in Column a and the 'to' address should be derived from column e with a standard email address added as 'cc' i.e my own email address ("[email protected]"). I am also looking to have the code run each day without me needing to open it each time, but so far think that may only be possible with the use of windows scheduler - unless there are other ways of ensuring its run without me opening?


    Any help would be much appreciated


    Thanks


    Matt

  • Re: VBA code for automatic email if prompt date and due date reached


    Try something like this (untested, but should be fairly close to what you want).


    In a module (e.g. Module1):

    In the ThisWorkbook module:

    Code
    1. Private Sub Workbook_Open()
    2. Check_Tasks
    3. End Sub

    and create a Scheduled Task which opens the workbook.


    If Check_Tasks() is run more than once on a day then it will send the email again. Therefore you might want to add a column in the "to do" sheet which the code checks to see if it should send the email and populates with the date when the email is sent.

  • Re: VBA code for automatic email if prompt date and due date reached


    john W - thanks you are a star - works like a dream - having a bit of an issue running the windows scheduler when im not logged on - but think this is either due to the mapping of the drives and simply down to permissions as on a work network - but will use a seperate forum to possible be advised


    thanks again for your time and effort

  • Re: VBA code for automatic email if prompt date and due date reached


    Just one more thing actually - the macro works great and i have linked it to another spreadhseet which gets opened regadless of if im in so have a macro in that to run this macro. But my problem now is i only want the macro above to work when the optionj button is slected in the other spreadsheet, but everytimt i iopen this spreadsheet it runs. I can't just disable macro's as various other people will be going into this sheet to add tasks to be completed and i tried assigning it an option button but it still runs when the sheet is first opened? any ideas how i can limit it to only run once the macro in the other sheet is ran??


    Thanks


    Matt

  • Re: VBA code for automatic email if prompt date and due date reached


    I can't quite follow your rather garbled reply, but if I understand you correctly try this.


    1. Delete all the Workbook_Open code.


    2. Create a Notepad text file and paste in the following code and save it as C:\folder\Check_Tasks.vbs. Change the folder path and workbook file name in the code as required:



    3. Create a Scheduled Task to run C:\folder\Check_Tasks.vbs (or wherever you've saved or named it).


    You can now call the Check_Tasks VBA subroutine independently of opening the workbook in which it resides, whilst still having the scheduled task run it as required.

  • Re: VBA code for automatic email if prompt date and due date reached


    Hi John


    Thans for your time again and yes apologies for the languiage in the resonse (i was rushing to get to a meeting and didn't spell check). i did try something similar with a BAT file but for some reason the scheduled taks kept running and did not complete, i pressume this is to do with permissions etc. so managed to get around this by adding in a macro to a different spreadsheet that gets opened daily and then run this macro off that spreadsheet which works fine.


    The problem i an incurring now is that whenever i open the spreadhset containing the code above to say add mroe tasks on the to do list, the macro runs automatically and the emails are sent again. i tried adding in a button so that the macro would onlpy run when it is pressed but this hasn;t solved the issue (works fine when button pressed but still runs on excell startup)


    You mention 'Therefore you might want to add a column in the "to do" sheet which the code checks to see if it should send the email and populates with the date when the email is sent'. If i add another column lets call it column F, what code would i need to add in to have the macro update that coloumn withy the date once email sent and then perssumable would need another code to check to see if that date is populated before sending the email?


    Once again thanks for your time on this


    Matt

  • Re: VBA code for automatic email if prompt date and due date reached


    Quote from mdimiller;655974

    i did try something similar with a BAT file but for some reason the scheduled taks kept running and did not complete

    I don't why the tasks wouldn't complete. The problem with a .bat file is that you can't tell it run a specific macro in an Excel workbook. Hence the reason for using a VBScript (.vbs) file which opens a workbook (your "to do list.xls") and runs the Check_Tasks routine in that workbook.


    Quote


    The problem i an incurring now is that whenever i open the spreadhset containing the code above to say add mroe tasks on the to do list, the macro runs automatically and the emails are sent again.

    Did you delete the Workbook_Open code as instructed? Without the Workbook_Open code the Check_Tasks routine won't run automatically, unless you are calling it from the Auto_Open subroutine which also runs a macro automatically when the workbook is opened.


    Quote


    You mention 'Therefore you might want to add a column in the "to do" sheet which the code checks to see if it should send the email and populates with the date when the email is sent'. If i add another column lets call it column F, what code would i need to add in to have the macro update that coloumn withy the date once email sent and then perssumable would need another code to check to see if that date is populated before sending the email?


    Try this, which replaces all the VBA code previously posted. It checks column F to see if the Task Reminder email has been sent, and column G for the Task Due email.

  • Re: VBA code for automatic email if prompt date and due date reached


    hi John, Thanks for the code. it really works well. i have modified the Task due date to today and the email id to my email id. unfortunately, it is sending me recurring emails. how can i avoid this?

  • Re: VBA code for automatic email if prompt date and due date reached


    Quote from Latha D;677601

    i have modified the Task due date to today and the email id to my email id. unfortunately, it is sending me recurring emails.

    The code works for me with the due date today and doesn't send the 'Task due' email again because column G is populated with the date and time the email was sent. Are you using the proper code? - http://www.ozgrid.com/forum/showthread.php?t=176330&p=656063#post656063


    http://"http://www.ozgrid.com/forum/showthread.php?t=176330&p=656063#post656063?"

  • Re: VBA code for automatic email if prompt date and due date reached


    Dear John,


    If I want to Include One more column in my subject means (That is A& B) what can I Do, Can u pls Rever on this as much earlier...


    Thanks in Advance....

  • Re: VBA code for automatic email if prompt date and due date reached


    Please do not post questions in threads started by other members.


    Start your own thread, give it a title that accurately and concisely describes your issue and explain your problem fully. If you think this, or any other thread, can help clarify your issue you can include a link to it by copying the URL from the address bar of your browser when viewing the thread, and pasting into your message.


    Thank you.

  • Re: VBA code for automatic email if prompt date and due date reached


    Dear John,


    If Im including Aditional column in between A aad B ( The New column is named as B),then I want to Include the A column and newly created B column in my sujetc filed, Then what can I do in the .VBS, ANd the remaining columns are same.


    If possible can you make seprate program and share it.


    Thanks in advance..



    Can I get the above VB Program Excel Sheet..