Sub envoyer_mail()
Dim MaMessagerie As Object
Dim MonMessage As Object
Set MaMessagerie = CreateObject("Outlook.Application")
Set MonMessage = MaMessagerie.CreateItem(0)
Dim DateVL As String
DateVL = Range("tb_mail_gestion[Date VL]").Rows(3)
Dim Corps As Variant
lastc = Sheets("Mail").Range("A2").End(xlToRight).Column
lastr = Sheets("Mail").Range("A2").End(xlDown).Row
Corps = converthtml(Range(Sheets("Mail").Cells(2, 1), Sheets("Mail").Cells(lastr + 1, lastc)))
Dim strHTML1, strHTML2, strHTML3, strHTML4 As String
strHTML1 = "<html><body><h1>Bonjour,</h1><p>Merci de bien vouloir trouver ci-dessous le suivi des dépassements OPC sur la VL du 28/04/2023. Merci de nous indiquer les actions de régularisation déjà réalisées et/ou à venir.</p></body></html>"
strHTML2 = "<html><body><i><u><strong><p>1) Nouveaux dépassements :</i></u></strong></p></body></html>"
strHTML3 = "<html><body><i><u><strong><p>2) Dépassement en cours :</i></u></p><strong></body></html>"
strHTML4 = "<html><body><i><u><strong><p>3) Régularisation :</i></u></p><strong></body></html>"
strHTML5 = "<html><body><p>Cordialement,</p></body></html>"
Dim Email As String
Email = "AM_GestionOPC"
Dim Email_CC As String
Email_CC = "AM_controledesrisques;" & _
"AM_rcci;"
MonMessage.To = Email
MonMessage.CC = Email_CC
MonMessage.Subject = "Alerte dépassements ratios sur les fonds - " & DateVL & ""
MonMessage.HTMLBody = strHTML1
MonMessage.HTMLBody = MonMessage.HTMLBody & Corps
MonMessage.HTMLBody = MonMessage.HTMLBody & strHTML2
MonMessage.HTMLBody = MonMessage.HTMLBody & strHTML3
MonMessage.HTMLBody = MonMessage.HTMLBody & strHTML4
MonMessage.HTMLBody = MonMessage.HTMLBody & strHTML5
MonMessage.Display
Set MaMessagerie = Nothing
End Sub
Function converthtml(plage As Object)
Dim lmf, fso, ts, r
lmf = "abctext.html"
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, lmf, plage.Parent.Name, plage.Address, xlHtmlStatic, "Book1_26691", "")
.Publish (True)
.AutoRepublish = False
End With
Set fso = CreateObject("scripting.filesystemobject")
Set ts = fso.OpenTextFile(lmf)
r = ts.ReadAll
ts.Close
converthtml = r
End Function