E-mail warning

When a customer is late with payment it would not be bad to send him an email alert. It is also a nice gesture if you thank him if he fulfills his obligations on time. All this can be done automatically from Excel, and the example you will be shown is the logical continuation of the previous post about the HYPERLINK function. Also, if you run your CRM in Excel, this is a good idea how to enrich it with some new functionalities.

We will start with the table which contains the name of the partner, the name and e-mail address of the contact person, as well as the amount of debt. Each company determines how much delay can be tolerated to the customer. In this simple example, we will assume that a good customer is one who has a balance of 0 or less, the average is one who owes 50,000 or less, and everyone else is a bad customer. To calculate the customer rating, it is necessary to write a formula:

=IF(D2<=0,1,IF(D2<=50000,2,3))

Based on the score, we should send various e-mail messages. For this purpose we will make a table that contains the customer’s score, subject and body of a message that would be sent, depending on the rating.

An email is formed as a string that is a combination of the recipient, the title, and the text. Simplified syntax:

„mailto:“<e-mail address>“?subject=“<subject>“&body=“<body message>

How to generate email alerts? We will write the formula:

=HYPERLINK(“mailto:”&C2&”?subject=”&VLOOKUP(E2,Sheet2!$A$1:$C$4,2)&”&body=
“&VLOOKUP(E2,Sheet2!$A$1:$C$4,3),”SEND”)

It creates a link called “SEND” and by clicking on it, we send the appropriate e-mail, depending on score, to the contact person.

If you click on the SEND link message will be sent by your default e-mail client.