Bonjour,
L’envoi des mails peut s’exécuter sur l’activation de la feuille «VM REIMS » ou sur l’événement "ouverture du fichier "
Voici un exemple sur l’événement « activation de la feuille » le code est sur la page de "VM REIMS"
à tester,
Private Sub Worksheet_Activate()
Dim i As Long
For i = 5 To Cells(Rows.Count, 7).End(xlUp).Row
If Cells(i, 7) = 1 And Cells(i, 8) = 0 Then
EnvoiMail i
Cells(i, 8) = 1
End If
Next
End Sub
Sub EnvoiMail(rw As Long)
'Nécessite d'activer la référence "Microsoft Outlook Library"
Dim OutObj As Object, OutMail As Object
Dim sAdrMail As String, strSujet As String, strBody As String
Set OutObj = CreateObject("Outlook.Application")
Set OutMail = OutObj.CreateItem(0)
sAdrMail = Cells(rw, 9)
strSujet = "SUIVI DES VISITES MEDICALES"
htmlBody = "<BR>" & _
Cells(5, 1) & ": " & Cells(rw, 1) & "<BR>" & _
Cells(5, 2) & ": " & Cells(rw, 2) & "<BR>" & _
Cells(5, 3) & ": " & Cells(rw, 3) & "<BR>" & _
Cells(5, 4) & ": " & Cells(rw, 4) & "<BR>" & _
Cells(5, 5) & ": " & Cells(rw, 5) & "<BR>" & _
Cells(5, 6) & ": " & Cells(rw, 6)
With OutMail
.to = sAdrMail
.Subject = strSujet
.htmlBody = htmlBody
.Send
End With
Set OutMail = Nothing
Set OutObj = Nothing
End Sub