Send an e-mail based on the number of days until due

[Even]

Hi there, is it a way to generate an e-mail that includes the text I show beneath? When it is less den 90 days until renewal, I want to send out an e-mail to the person in charge of the customer about the renewal terms.

Send an automatic e-mail based on due date.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	5/17/2021
2	General terms	%	Special terms			Customer	Due date	Time until due	Transport	D&O	Cyber	Crime	Art		Name in charge	E-mail
3	Transport	4,50%	5,00%			Example 1	9/1/21	107	4,50%	3,50%		5,00%
4	D&O	3,50%	5,00%			Example 2	11/30/21	197		5,00%			3,50%
5	Cyber	3,50%	5,00%			Example 3	7/1/21	45	4,50%		5,00%				John	John@test.com
6	Crime	3,50%	5,00%			Example 4	8/25/21	100		5,00%		5,00%
7	Art	3,50%	5,00%			Example 5	6/1/21	15	4,50%	3,50%		5,00%			Jane	Jane@test.com
8						Example 6	7/30/21	74		5,00%			3,50%		Jason	jason@test.com
9						Example 7	7/1/21	45	4,50%		5,00%				Lydia	Lydia@test.com
10						Example 8	8/1/21	76		5,00%		5,00%			Ellen	Ellen@test.com
11						Example 9	8/1/21	76		3,50%
12						Example 10	8/1/21	76		3,50%
13
14
15						Email Subject	Renewal
16						Email Body	Dear… it is soon time for renewal for X customer. The following standard terms of renewal is: Transport : 4,5% D&O: 3,5% Crime: 5% Please let me know if they will be renewing under these terms. Thank you! Sincerely X
17
All products

Cell Formulas
Range	Formula
A1	A1	=TODAY()
I3,I9,I7,I5	I3	=$B$3
L3,L10,L6:L7	L3	=$C$6
J3,J7	J3	=$B$4
J4,J10,J6,J8	J4	=$C$4
M4,M8	M4	=$B$7
K5,K9	K5	=$C$5
H3:H12	H3	=G3-$A$1
J11	J11	=B4
J12	J12	=B4

Cells with Data Validation
Cell	Allow	Criteria
E3:E12	List	=$A$9:$A$11

 

[shknbk2]

Here's a first attempt. Rather than use the text in the body cell G16, the code develops the same text in the large "body" portion in the middle. Also, the subject line is hard coded to be "Renewal", but if you want to make it based on G15, that is easily changed.

The body text is built as HTML-based code. The "<br>" text is a new line command. Other formatting can be used such as bolding certain words if desired.

Option Explicit

Sub Send_email_under_90()
    Dim OutApp          As Object
    Dim OutMail         As Object
    Dim rCell           As Range
    Dim body            As String
    Dim colCustomer     As Integer 'Customer column
    Dim colTimeTillDue  As Integer 'Time until due column
    Dim colTransport    As Integer 'Transport column
    Dim colDandO        As Integer 'D&O column
    Dim colCyber        As Integer 'Cyber column
    Dim colCrime        As Integer 'Crime column
    Dim colArt          As Integer 'Art column
    Dim colName         As Integer 'Name in charge column
    Dim colEmail        As Integer 'Email column
    
    colCustomer = 6
    colTimeTillDue = 8
    colTransport = 9
    colDandO = 10
    colCyber = 11
    colCrime = 12
    colArt = 13
    colName = 15
    colEmail = 16
    
    For Each rCell In Range(Cells(3, colTimeTillDue), Cells(Rows.Count, colTimeTillDue).End(xlUp))
        If rCell.Value < 91 Then
            'Build body message
            body = "Dear " & Cells(rCell.Row, colName).Text & ",<br><br>"
            body = body & "It is soon time for renewal for " & Cells(rCell.Row, colCustomer).Text
            body = body & ". The following standard terms of renewal are:<br>"
            If Cells(rCell.Row, colTransport).Text <> "" Then
                body = body & "Transport: " & Cells(rCell.Row, colTransport).Text & "<br>"
            End If
            If Cells(rCell.Row, colDandO).Text <> "" Then
                body = body & "D&O: " & Cells(rCell.Row, colDandO).Text & "<br>"
            End If
            If Cells(rCell.Row, colCyber).Text <> "" Then
                body = body & "Cyber: " & Cells(rCell.Row, colCyber).Text & "<br>"
            End If
            If Cells(rCell.Row, colCrime).Text <> "" Then
                body = body & "Crime: " & Cells(rCell.Row, colCrime).Text & "<br>"
            End If
            If Cells(rCell.Row, colArt).Text <> "" Then
                body = body & "Art: " & Cells(rCell.Row, colArt).Text & "<br>"
            End If
            body = body & "<br>Please let me know if they will be renewing under these terms.<br><br>Thank you!<br><br>Sincerely X"
            
            'open outlook if OutApp hasn't been set to the Outlook App yet
            If OutApp Is Nothing Then
                Set OutApp = CreateObject("Outlook.Application")
            End If
            
            'Create email and display
            'The email can be automatically sent instead if ".Display" is replaced with ".Send"
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .Subject = "Renewal"
                .To = Cells(rCell.Row, colEmail).Text
                .htmlbody = body
                .display
            End With
            Set OutMail = Nothing
        End If
    Next rCell
    Set OutApp = Nothing
End Sub

 

[Even]

Wow - that is amazing, shknbk2! Based on first look it seems to be working nicely. I need to be a little familiar with it and I get back to you

 

[Even]

That was a very fine code. It was easy to understand and to build on. But it opens the mail when the cell is blank in column H (time until due). Do you know how to open the mail only for non blank cells and of course when it is <90?

 

[Even]

Never mind. I don't need it.

 

[Even]

All the cells will be filled so I don't need to ignore blank cells

 

[shknbk2]

Glad to help. Let me know if any additional tweaks are needed.

 

[Even]

Thank you so very much!

 

[Even]

By the way: This is probably a new post?Is it possible to generate the same e-mail by clicking on the customer's name?

 

[shknbk2]

You can create hyperlinks to macros: see here and here for examples.

However, if the customer names are going to be changing often enough to make creating these hyperlinks a pain, you can add a double-click event trigger for the worksheet that verifies if the cell that was double-clicked is within the range of the clients and if so, run the macro. The double-clicking event would avoid the need to create hyperlinks. It would kind of work similarly to the FollowHyperlink examples of the links above but using the BeforeDoubleClick event instead. A good example is part of this discussion here, see post #16 especially.