In this post, I will discuss about sending an email to Opportunity owner for Contract expiration.Contracts are maintained outside Salesforce(for instance, in Box).

Requirement:

System should send an email to Opportunity Owner, one month before the contract gets expired and Contract Expiry Date will be calculated based on Contract Term and Close Date selected at Opportunity. For example, if the contract term is 1 year and Close date is March 8th 2015, Contract Expiry Date should be calculated as Feb 8th 2016, system should send an email to the Opportunity Owner on Feb 8th 2016.

Solution:

This can be achieved through Time Dependent Workflow and creating a formula field which calculates Contract Expiry Date. Steps are as follows:

a. Create a field called Contract Expiry Date in Opportunity which should be a formula field and it calculates the date based on Contract Term and Closed date. This should give the day which is Close Date + 11 Months, if the Contract term is 1 year and Close date + 23 months, if the contract term is 2 years and so on. Find the screenshot below for the same:

b. Update Close Date to the date when the Opportunity is set to Closed Won. This is done through the workflow and it will fire when a record is created, and every time it’s edited. Create the workflow rule and action as given below. This is done so that Close Date is set automatically even when user forgets to update the Close Date after setting the Stage to Closed Won:

c. Create a workflow and a Time dependent workflow action to send an email 1 day before Contract Expiry Date as per the screenshot below. This should fire only when Contract Expiry Date is not null and evaluation criteria as Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria.

You can goto Monitoring to see if Time dependent actions are queued for the Opportunity record:

The solution I proposed above will calculate the Contract Expiry Date as 5th Feb 2016 instead of 8th Feb 2016, if the Close Date is 8th March 2015 and Contract term is 1 year. This is because of the formula which I used to calculate the Contract Expiry Date as not all months have equal no of days and the formula gives the approximate day of 1 month before (Close Date + Contract Term). I will discuss in next post to calculate the exact Contract Expiry Date.

Please share your thoughts/comments.