1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| Sub envoiermail()
Dim fichiers As String
Dim i As Integer
Dim path As String
Dim txtBody As String
service = InputBox("Quelle est le nom de votre service ?", "Service")
semaine = ActiveSheet.Range("C3").Value
'Application.DisplayAlerts = False
Sheets("Planning hebdo").Copy
ChDir "X:\EFFECTIFS\2014\Sauvegarde planning à 2 jours maxi"
ActiveWorkbook.SaveAs Filename:="X:\EFFECTIFS\2014\Sauvegarde planning à 2 jours maxi\liste de recensement " & service & " " & semaine & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
'Application.DisplayAlerts = True.
fichiers = "X:\EFFECTIFS\2014\Sauvegarde planning à 2 jours maxi\liste de recensement " & service & " " & semaine & ".xlsx"""
If fichiers = "" Then Exit Sub
txtBody = "Bonjour, " & _
vbCrLf & vbCrLf & _
"Ci-joint la liste de recensement du personnel de mon secteur " & service & _
vbCrLf & vbCrLf & _
"Bonne réception."
EnvoiMail_Outlook "Liste de recensement de la " & semaine & " du service " & service, txtBody, "mon adresse mail", Pj:=fichiers
End Sub
Sub EnvoiMail_Outlook(Sujet As String, Message As String, Destinataire As String, Optional DestinataireCopy As String, Optional DestinataireCopyCacher As String, Optional Pj As String = "")
Set ObjOutlook = CreateObject("Outlook.application")
Set MailObj = ObjOutlook.CreateItem(0)
With MailObj
.To = Destinataire
.cc = DestinataireCopy
.BCC = DestinataireCopyCacher
.Subject = Sujet
.BodyFormat = 2
.HTMLBody = Message
If Trim("" & Pj) <> "" Then
p = Split(Pj & ";", ";")
For i = 0 To UBound(p)
If Trim("" & p(i)) <> "" Then .Attachments.Add Trim("" & p(i))
Next
End If
'.Display 'Can be .Send but prompts for user intervention before sending without 3rd party software like ClickYes
.SEND
End With
End Sub |
Partager