Auotmatic reminder/email from excel to outlook

[kspbc]

Hello,

I'm new to the forum and hopefully posted in the right place etc.

I run a Building and maintenance company and I've set up a spreadsheet to log all of my estimates, customers, and payments etc.

I have attached a copy of the file to this post.

I am hoping someone can help to set up some VBA or code to get outlook to send me an email or flag up a calendar reminder in outlook to tell me to chase a payment based on the dates in the cells.

Column AC contains the date that the invoice is due to be paid
Column AD Contains a date that I want to set for say 3 days from the due date
Column AE Contains a 2nd date I want to set for say 5 days from the due date
Column AF Contains the email address I want the reminder to be sent to
Column AG Contains the Status of the Payment i.e. paid (change cell to green background), outstanding (change cell to red background)

What I'd like to achieve is to get the excel sheet to send me an email or send outlook a reminder to remind me to contact the customer regarding payment due.

Thanks in advance

Stewart

Cost Bible - Copy.xlsx

[owenaldridge]

Hi Stewart,

Try the attached workbook, hopefully this does what you need. You may want to change some elements of the code to suit your needs, i.e. subject, body etc.

I decided to use "emails" as the method for a reminder. It is relatively straight forward to change this to "tasks" if required.

It sends emails to you if today has past either 1st payment reminder date or 2nd payment reminder date.

It will not send you the same email twice, as it marks each one as sent in two new columns I created.

To run, just run the macro "Create_Emails".

Note: there must always be data in Column B for each entry, but I suspect there will be anyway.

Let me know how this works for you, message me if you need any more help

Owen