Sub envoi_mail()
For i = 2 To Sheets(1).Range("d1").End(xlDown).Row
dest = Cells(i, 5)
service = Cells(i, 2) & "-" & Cells(i, 1)
chemin = "XXX"
nom_fichier = Cells(i, 4)
fich = chemin & nom_fichier
txt = Dir(fich)
Set imsg = CreateObject("CDO.Message")
Set iconf = CreateObject("CDO.Configuration")
nberreur = 0
'MsgBox dest & "/" & service & "/" & chemin & nom_fichier
If txt = "" Then
nberreur = nberreur + 1
GoTo Suite
Else
With imsg
Set .Configuration = iconf
If .Attachments.Count <> 0 Then .Attachments.Delete (1)
.To = dest
.CC = ""
.BCC = ""
.From = "aa@bbb.fr"
.Subject = "sujet" & service
.TextBody = "Bonjour," & Chr(10) & service
.addattachment fich
.Send
End With
End If
Suite:
Next
MsgBox "Votre mail a bien été envoyé"
End Sub