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]

I'm glad I could help. I learned mostly through self teaching for over 30 years. A lot of books, internet learning, and a couple classes in school.

 

[Even]

I found out a way to make the text bold and make the list into a bullet point. However, the text in the end of the e-mail (after the bullet points) is on the same space as the bullet point list. I want it to be alligned to the left. Do you know how I can do that?
Here is the code (made it shorter so it is easier to view):

If Cells(rCell.Row, colTransport).Text <> "" Then
    body = body & "<ul><li> <b> Transport: </b>" & Cells(rCell.Row, colTransport).Text & "<br>"
End If
If Cells(rCell.Row, colDandO).Text <> "" Then
    body = body & "<ul><li> <b>D&O: </b>" & Cells(rCell.Row, colDandO).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"

 

[shknbk2]

You have to both open and close the list <ul> tag and the bullet <il> tags. See how this works. You might want to play around with a few extra <br>'s if you need more paragraphs.

            body = body & ". The following standard terms of renewal are:<br><ul>"
            If Cells(rCell.Row, colTransport).Text <> "" Then
                body = body & "<li><b>Transport:</b> " & Cells(rCell.Row, colTransport).Text & "</li>"
            End If
            If Cells(rCell.Row, colDandO).Text <> "" Then
                body = body & "<li><b>D&O:</b> " & Cells(rCell.Row, colDandO).Text & "</li>"
            End If
            If Cells(rCell.Row, colCyber).Text <> "" Then
                body = body & "<li><b>Cyber:</b> " & Cells(rCell.Row, colCyber).Text & "</li>"
            End If
            If Cells(rCell.Row, colCrime).Text <> "" Then
                body = body & "<li><b>Crime: " & Cells(rCell.Row, colCrime).Text & "</li>"
            End If
            If Cells(rCell.Row, colArt).Text <> "" Then
                body = body & "<li><b>Art:</b> " & Cells(rCell.Row, colArt).Text & "</li>"
            End If
            body = body & "</ul><br>Please let me know if they will be renewing under these terms.<br><br>Thank you!<br><br>Sincerely X"

 

[Even]

Perfect!