Option Explicit
Sub Copie_Feuille()
Dim wks As Workbook, chemin$, fichier$, nom$
With Sheets("Stock")
.Activate
.Range("a4:k79").Copy
End With
chemin = ThisWorkbook.Path & "\Fichiers\"
nom = ActiveSheet.Name
ActiveSheet.SaveAs Filename:=chemin & nom & ".xls", FileFormat:=xlExcel8
End Sub
Sub Envoi_Mail()
Dim olApp As Object
Dim olMail
Dim nom As Range, derlig As Long, i As Long, chemin$, fichier$, _
envois$, sujet$, corps$, Liste$, Rep_Doc$
With Sheets("Envois Mail")
.Activate
envois = .Range("a2")
sujet = .Range("e2")
corps = .Range("f2")
Set nom = .Range("g2")
derlig = .Range("c" & Rows.Count).End(xlUp).Row
For i = 2 To derlig
Liste = Liste & Cells(i, 3).Value & ";"
Next i
End With
chemin = ThisWorkbook.Path & "\Fichiers\"
fichier = nom.Value & ".xls"
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
With olMail
.To = envois
.Subject = sujet
.BCC = Liste
.Body = corps
.Attachments.Add chemin & fichier
.Display
'.Send
End With
Rep_Doc = Dir(chemin & "*.*")
Do While Rep_Doc <> ""
Kill chemin & Rep_Doc
Rep_Doc = Dir
Loop
Set olMail = Nothing
Set olApp = Nothing
End Sub