Public Sub Envoi_Mail_Alerte_Habilitation()
Dim i As Integer, j As Integer, objOutlook As Outlook.Application
Dim Resultat As String
Dim oBjMail As Outlook.MailItem
Dim oBjOl As Outlook.Application
Set oBjOl = CreateObject("Outlook.Application")
For i = 3 To 12
For j = 5 To 35
If Not IsEmpty(ActiveCell) And IsDate(ActiveCell) Then
Resultat = DateDiff("d", ActiveCell.Value, Range("B14").Value)
End If
MsgBox Resultat
Select Case Resultat
Case 91 To 180
Set oBjMail = oBjOl.CreateItem(0)
With oBjMail
.To = "monemail@blablabla.fr"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention la date de validité d'une formation ou habilitation d'un salarié est comprise entre 3 et 6 Mois"
.Send
End With
Set oBjMail = Nothing
Case 1 To 90
Set oBjMail = oBjOl.CreateItem(0)
With oBjMail
.To = "monemail@blablabla.fr"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est inférieure à 3 Mois."
.Send
End With
Set oBjMail = Nothing
Case Is <= 0
Set oBjMail = oBjOl.CreateItem(0)
With oBjMail
.To = "monemail@blablabla.fr"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est dépassée."
.Send
End With
Set oBjMail = Nothing
End Select
Next j
Next i
objOutlook.Quit
Set objOutlook = Nothing
End Sub