Essaie ceci
Désolé, je n'ai pas pu testé car je n'ai pas outlook à la maison !
Option Explicit
Sub envoi()
Dim messagerie As Object
Dim email As Object
Dim cel As Range
Dim texte As String
Set messagerie = CreateObject("Outlook.Application")
For Each cel In Range("A2:A" & Range("A1").End(xlDown).Row)
If cel.Offset(0, 7) = "retenu" Then
Set email = messagerie.CreateItem(0)
With email
.to = cel
.Subject = "mettre ici le titre du mail"
.body = Cells(1, 2) & " = " & Cells(cel.Row, 2) & Chr(10) & _
Cells(1, 3) & " = " & Cells(cel.Row, 3) & Chr(10) & _
Cells(1, 4) & " = " & Cells(cel.Row, 4) & Chr(10) & _
Cells(1, 5) & " = " & Cells(cel.Row, 5) & Chr(10) & _
Cells(1, 6) & " = " & Cells(cel.Row, 6) & Chr(10) & _
Cells(1, 7) & " = " & Cells(cel.Row, 7) & Chr(10)
.ReadReceiptRequested = True
.display ' à remplacer par .send si ok
End With
Set email = Nothing
End If
Next cel
Set messagerie = Nothing
End Sub