Public Vmail As String
Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 16/12/2011 par fl170417
'
'
Dim vnom, vdir As String
For Each Sheet In Sheets
Sheet.Select
vnom = ActiveSheet.Name
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "N:\"
'choix du repertoire en fonction du nom agence
Select Case vnom
Case "Agence1": vdir = "Est\"
Vmail = "xxxxxxxxxx@hotmail.com"
Case "Agence2": vdir = "Nord\"
Vmail = "xxxxxxxxxx@yahoo.fr"
Case "Feuil3": vdir = "Nord\"
Vmail = "xxxxxxxxxx@yahoo.fr"
End Select
ActiveWorkbook.SaveAs Filename:="N:\" & vdir & vnom & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Name = vnom
'envoyer ton mail avec le fichier en attaché et vmail (déclarée en "public vmail as string" )comme adresse mail
Call EnvoiMail 'ta macro d'envoi de mail
ActiveWindow.Close savechanges = True
Next
End Sub
''''''''''''''''''''''''''''''Sub Mail_workbook_Outlook_1()
Sub EnvoiMail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Vmail 'ta variable Email déclarée Public!
'''''''''''''''''''''''''''''''''''''''''''''''''. to = "mail@mail.com"
.CC = " xxxxxxxxxx@hotmail.com "
.BCC = "xxxxxxxxxx@yahoo.fr "
.Subject = "mouvements clients"
.body = "Bonjour," & Chr(13) & "" & Chr(13) & "Veuillez trouver, ci-joint, le fichier des jours….." & Chr(13) & "" & Chr(13) & "Cordialement." & Chr(13) & "" & Chr(13) & "blabla"
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub