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 62 63 64 65 66 67 68 69 70 71 72
| Sub Envoi_Mail3()
'Déclaration des variables
Dim Debut As String
Dim Destinataire As Variant
Dim Objet As String
Dim Source As String
Dim Contenu As String
Dim Contenu1 As String
Dim Contenu2 As String
Dim Porteur As String
Dim Email As String
Dim Action As String
Dim Quand As String
Dim Final As String
Dim Demandeur As String
Dim Previ As String
Dim Raison As String
Dim Previs As Range
Dim ObjOutlook As New Outlook.Application
Dim ObjOutlookmail As MailItem
'Initialisation des variables
Destinataire2 = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, -2).Value
Source = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, -6).Value
Objet = "Rappel de demande d'action via" & " " & Source
Porteur = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, 4).Value
Action = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, -5).Value
Quand = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, -10).Value
Final = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, 10).Value
Demandeur = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, -8).Value
Raison = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, 11).Value
Set Previs = Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12))
For Each Cell In Previs
If Cell.Value < Now And Worksheets("Feuil8").Range(Cells(6, 12), Cells(Worksheets("Feuil8").Range("L" & Rows.Count).End(xlUp).Row, 12)).Offset(0, 1).Value <> "OK" Then
Email = Destinataire
Debut = "Bonjour," & " " & Destinataire2 & vbNewLine & " " & vbNewLine
Contenu = Demandeur & " " & "a fait une demande via" & " " & Source & ", " & " en date du " & " " & Quand & "." & vbNewLine & "(" & Action & ")" & vbNewLine & vbNewLine
Contenu1 = "Merci de ne pas oublier la prise en charge de cette demande et me revenir lorsqu'elle sera traitée" & "." & vbNewLine
Contenu2 = "Bien à vous" & vbNewLine & vbNewLine &
End If
Next
Set ObjOutlook = New Outlook.Application
Set ObjOutlookmail = ObjOutlook.CreateItem(olMailItem)
'Envoi du mail
With ObjOutlookmail
.To = Email
.CC =
.Subject = Objet
.Body = Debut & Contenu & Contenu1 & Contenu2
.Send
End With
End Sub |
Partager