Tech Support Guy banner
Status
Not open for further replies.

Automatic Outlook mail due date reminder based on Excel file

2K views 13 replies 2 participants last post by  DataBase 
#1 ·
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,
 

Attachments

#2 ·
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

The script should preferably run automatically every time the PC is running. without the excel file necessarily open.
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.
 
#3 ·
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 .........
 

Attachments

#5 ·
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.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top