Sub EnvoiMail()
Dim i As Integer, adr1 As String, nom As String
Dim dtAnn As Date, dtEnvois As Date
Dim ol As Object, olmail As Object
Set ol = CreateObject("Outlook.Application")
Set olmail = ol.Application.CreateItem(olMailItem)
With Sheets(1)
i = .Range("a65536").End(xlUp).Row + 1
dtAnn = .Cells(i, 3)
dtEnvois = .Cells(i, 4).Value ' colonne D
adr1 = .Cells(i, 5).Value
nom = .Cells(i, 2).Value
End With
If DateDiff("m", Date, dtEnvois) = 1 Then
With olmail
.To = adr1
.Subject = "Flag assessment"
.HTMLBody = "Bonjour " & nom & ",<br/><br/> Suite à l'approche de la date d'anniversaire de signature du NLP " & dtAnn & ", veuillez faire le nécessaire pour préparer l'assessment " & dtEnvois & vbCrLf & " .<br/><br/>Cordialement."
.Display
End With
Else
Exit Sub
End If
Set ol = Nothing
Set olmail = Nothing
End Sub