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.
Seems like a lot of people wanted automated emails from Excel recently 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.
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 .........
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.
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.
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
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
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?
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!