Send email reminder when there are 31 days before a date.

[U.Ali]

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

[Kenneth Hobson]

Welcome to the forum! That is a pretty involved project for a first time poster.

I will check back tomorrow to see if you or someone else has finished helping or not.

To make your project, I would recommend that you add a helper column or two. One might be, if x, then 31 day reminder is in queue.

This needs some work but shows the concept for the 31 day reminder. This does not check. It just sends the title row and the 2nd data row that could meet the 31 day limit and Column with x is blank. Here I defer delivery for 10 minutes. In practice it would do the 31 day deal. In a Module:

Sub Test()
  Dim ws As Worksheet, r As Range, b As Range
  'Tools > References > Microsoft Outlook xx.0 Object Library
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  'Tools > References > Microsoft Word xx.0 Object Library
  Dim Word As Document, wr As Word.Range
  
  Set ws = Worksheets("Renewal Dates")
  Set r = ws.Range("A1:G1")
  
  Set olApp = New Outlook.Application
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail
    .Importance = olImportanceNormal
    .To = "ken@gmail.com"
    .Subject = "31 Day Reminder"
       
    .GetInspector.Display
    Set Word = .GetInspector.WordEditor
    
    r.Copy
    Word.Range(0, 0).Paste
    r.Offset(2).Copy
    Set wr = Word.Content
    wr.Collapse Direction:=wdCollapseEnd
    wr.Paste  'Paste at end
    Application.CutCopyMode = False
    
    'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._mailitem.deferreddeliverytime.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
    .DeferredDeliveryTime = Now + TimeValue("00:10:00")
    .Display
    '.Send
  End With
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

[U.Ali]

Hello Kenneth

Thank you for your response.

I am a first time poster but do have some experience with Excel, mostly Vlookup and Index Match, next to no experience with VBA.

On the renewal dates worksheet from the references menu i added the outlook and word object libraries.

On the sheet i inserted a mondule and your code and it does run, it sends an email to my designated email however it does not send the information i require. In your code you have:


r.Copy
Word.Range(0, 0).Paste
r.Offset(3).Copy
Set wr = Word.Content
wr.Collapse Direction:=wdCollapseEnd
wr.Paste 'Paste at end
Application.CutCopyMode = False

In the above code segment, with the value set to three, an email is sent which lists the data below

Company Name Contract Start Date Contract End Date Break Clause Notice Period Notice Can be Given Reminder Date
Shadow Star Ltd 01/04/17 01/01/18 N/A 2 Months Anytime 1-Dec-17

In the data above, the clients contract ends in Jan 18 so the reminder should not sent untill it is 31 days before Jan 18. The code should notify me that based on my table, the contract of ABC Corp is expiring on 01/11/17 and that i should take action now.

Thank you for your assistance so far

Kind Regards

U.Ali

[Kenneth Hobson]

Please click the # icon on toolbar and paste code between the code tags.

I don't see any other helpers. Obviously, my post #2 was to show you it could be done. I will work up the code to iterate the range and the helper column if you like. There are basically two issues to address: (1) the early notice (3) daily notices after deadline.

You may want to consider changing your conditional formula for column G. In G2, I changed it to this: =TODAY() >=G2

You might want to also reconsider the 31 day notice. I noticed that you have a column E for month notices. If you change that to just numbers for months, you can get the early notice by using =EDate() rather than -31.