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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
|
Sub Envoi()
Dim celmed As String
Dim nom As String, ol As New Outlook.Application, derlg As Integer, repcourant As String, p1 As Variant, p2 As Variant
Dim olmail As MailItem, admail As String, messmail As String, i As Integer, nomfamille As String, prenom As String, daten As String, tech As String, medecin As String
repcourant = Application.ThisWorkbook.Path
If Dir(repcourant & "\Fiches Installations Aeris", 16) = "" Then MkDir repcourant & "\Fiches Installations Aeris"
'paramètres :
'attribution des cellules via feuille parametres
celmed = Sheets("Parametres").Range("B1").Value
'Autres parametres
daten = "-" & Day(Now) & "." & Month(Now) & "." & Year(Now)
nomfamille = ActiveSheet.Range("C11").Value 'case a lire
prenom = ActiveSheet.Range("C13").Value 'case a lire
tech = ActiveSheet.Range("F6").Value 'case a lire
medecin = ActiveSheet.Range(celmed).Value 'case a lire
messmail = "Ci-joint le rapport d'installation de Mr" & " " & nomfamille & " " & prenom & "," & " " & "réalisé par" & " " & tech
admail = "xxxxxxx@xxxxx.fr"
p1 = Sheets("Patient").Range("G19").Value
p2 = Sheets("Patient").Range("G21").Value
With Sheets("patient")
.Select
nom = ThisWorkbook.Path & "\" & "Fiches Installations Aeris" & "\" & nomfamille & "_" & prenom & daten & "-" & "Dr_" & medecin & ".pdf"
'MsgBox nom
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
nom, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, _
OpenAfterPublish:=False
End With
Set ol = New Outlook.Application
Set olmail = ol.CreateItem(olMailItem)
With olmail
.To = admail
.Subject = "Rapport Installation de Mr " & nomfamille & " " & prenom 'Sujet
.Body = messmail 'Corps du mail
.Attachments.Add nom
If (IsEmpty(p1)) Then
Else
.Attachments.Add p1
End If
If (IsEmpty(p2)) Then
Else
.Attachments.Add p2
End If
.Display '.Send 'On peut switcher entre .send et .display selon que l'on veut envoyer le mail (send) ou seulement le préparer et le vérifier(display)
End With
End Sub |
Partager