Private Sub SendAll()
    Dim oSheet  As Worksheet
    Dim oCell   As Range
    Dim sEMail  As String
    Dim sName   As String
    Dim sPath   As String
    
    On Error Resume Next
    
    sPath = Environ$("Temp")
    If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
    
    For Each oCell In Лист1.Range("C2:C6").Cells
        sEMail = oCell
        sName = oCell(, 0)
        Set oSheet = ThisWorkbook.Worksheets(sName)
        If oSheet Is Nothing Then
            MsgBox "Лист """ & sName & """ в этой книге отсутствует!", vbExclamation, "Лист не найден"
        Else
            oSheet.Copy
            With ActiveWorkbook
                .SendMail sEMail, "для " & sName, True ' с уведомлением о доставке
                .Close False
            End With
        End If
    Next
End Sub