Hello
Just joined and hope to learn a lot. I would like to kindly request help with the following.
I am using Outlook 2016 and Excel 2016.
I have a worksheet (attached) which tracks the contract status of tenants in an office building.
The columns are:
Column A - Company Name
Column B - Contract Start Date
Column C - Contract End Date
Column D - Break Clause
Column E - Notice Period
Column F - Notice Can Be Given
Column G - Reminder Date Column
The purpose of said sheet is to help me identify when a tenants contract is due to expire. I currently visit this sheet daily and look at Column G, if this column is equal to today's date, the cells is shaded red and this indicates that the client's agreement will expire within a month. Column G contains a simple formula which subtracts 31 days from the date in Column C and displays the result in Column G. I then manually email the tenant and enquire as to their intentions.
It would be ideal if the following can occur, the sheet is automatically checked each day and if today's date is equal to 31 days before the contract end date of each company then an email should be sent to my own mailbox. This email should specify which company the email reminder refers to and their corresponding information such as when their agreement started, when it expires and other info from columns a,d,e and f.
In addition, if the date in column C has already expired then a recurring reminder email should be sent to my mailbox informing me to update the value in column C for a particular company. Once it has been updated to a future date the reminder emails should stop until triggered by the 31 days email.
Just to clarify this worksheet is currently standalone but will be part of my main workbook which is opened daily and is constantly in use. In addition, the reminder emails do not need to be sent to tenants, just to my own mailbox.
I look forward to any assistance.
Thanks in advance.
Regards
U.Ali
Bookmarks