Option Explicit
Sub Envoi()
Dim Dest, Sujet as String, i As Integer, f As Worksheet, onglet As String
Set f = Feuil4
For i = 1 To Worksheets.Count - 1
onglet = Sheets(i).Name
Set Dest = f.Range("F:F").Find(onglet) 'Saisir l'adresse mail
Sujet = Dest.Offset(0, 2)
Dest = Dest(1, 2)
Sheets(i).Select 'Saisir le nom exact la feuille
ActiveSheet.Copy 'crée une copie de la feuille active
ActiveWorkbook.SendMail Dest, Sujet, True
Application.DisplayAlerts = False
ActiveWorkbook.Close 'ferme la copie de la feuille active
Application.DisplayAlerts = True
Next
f.Activate
End Sub