Bonjour a tous, j"ai un userform (etape1) qui sert a entre des donné de travailleur, lorsqu'une date est entré dans la textbox5 (date du dépôt du grief au superviseur), je veux envoyer un courriel a l'adresse courriel qui est inscrit tans la textbox8, et dans la colone AA de la feuille.
Sub EnvoiAutomatiqueMail()
Dim j&
If OutlookOuvert = False Then j = Shell("Outlook", vbNormalNoFocus)
For j = 3 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(j, 5) > Now And Cells(j, 7) <> "" And Cells(j, 28) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 5) & "" & " a votre superviseur immédit."
Cells(j, 28) = Now
ElseIf Cells(j, 11) <> "" And Cells(j, 29) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 11) & "" & " au surintendant."
Cells(j, 29) = Now
ElseIf Cells(j, 15) <> "" And Cells(j, 30) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 15) & "" & " au Resource Humaine."
Cells(j, 30) = Now
ElseIf Cells(j, 19) <> "" And Cells(j, 31) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & " une demande d'arbitrage a été demandé le" & " " & Cells(j, 18) & " " & "pour votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3)
Cells(j, 31) = Now
End If
Next j
End Sub
Function Envoi(Corps$)
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.Subject = "Dépôt de grief avant date d'échéance"
.To = Feuil2.[aa3]
.CC = Feuil2.[am3]
.Body = Corps
.Display 'pour voir
'.Send 'pour envoyer
End With
End Function
Function OutlookOuvert() As Boolean
Dim oOL As Object
On Error Resume Next
Set oOL = GetObject(, "Outlook.Application")
On Error GoTo 0
OutlookOuvert = Not (oOL Is Nothing)
Set oOL = Nothing
End Function
Merci de votre aide
Sub EnvoiAutomatiqueMail()
Dim j&
If OutlookOuvert = False Then j = Shell("Outlook", vbNormalNoFocus)
For j = 3 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(j, 5) > Now And Cells(j, 7) <> "" And Cells(j, 28) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 5) & "" & " a votre superviseur immédit."
Cells(j, 28) = Now
ElseIf Cells(j, 11) <> "" And Cells(j, 29) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 11) & "" & " au surintendant."
Cells(j, 29) = Now
ElseIf Cells(j, 15) <> "" And Cells(j, 30) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & "votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3) & "," & " a été déposé le " & " " & " " & Cells(j, 15) & "" & " au Resource Humaine."
Cells(j, 30) = Now
ElseIf Cells(j, 19) <> "" And Cells(j, 31) = "" Then
Envoi "Bonjour Mr, " & " " & Cells(j, 1) & "," & " " & " une demande d'arbitrage a été demandé le" & " " & Cells(j, 18) & " " & "pour votre grief # " & " " & Cells(j, 2) & " " & "-" & Cells(j, 3)
Cells(j, 31) = Now
End If
Next j
End Sub
Function Envoi(Corps$)
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.Subject = "Dépôt de grief avant date d'échéance"
.To = Feuil2.[aa3]
.CC = Feuil2.[am3]
.Body = Corps
.Display 'pour voir
'.Send 'pour envoyer
End With
End Function
Function OutlookOuvert() As Boolean
Dim oOL As Object
On Error Resume Next
Set oOL = GetObject(, "Outlook.Application")
On Error GoTo 0
OutlookOuvert = Not (oOL Is Nothing)
Set oOL = Nothing
End Function
Merci de votre aide