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 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
| Option Compare Database
Option Explicit
Dim i As Byte
Dim TabVariable As Variant
Dim Rs As DAO.Recordset
Dim MonCheminLien As String
Dim CheminContrat As String
Dim CléPersonneNature As Integer
Private Sub EnvoiMail_Click()
'On Error GoTo Suite:
DoCmd.RunCommand acCmdSaveRecord
If DCount("CléP_Salarié_Pièce", "T_Salariés_Pièces", "Sp_Sélection = " & -1) < 1 Then
MsgBox "Il n'y a aucun document sélectionné pour l'envoi !", vbExclamation, CurrentDb.Properties("AppTitle")
Else
If MsgBox("Vous allez envoyer les " & DCount("CléP_Salarié_Pièce", "T_Salariés_Pièces", "Sp_Sélection = " & -1) & " documents sélectionnés" & (vbCrLf) & (vbCrLf) & _
"Confirmez vous cet envoi ?", vbYesNo, CurrentDb.Properties("AppTitle")) = 6 Then
Dim Sender As String
Dim Recipient As String
Dim Subject As String
Dim Bcc As String
Dim BodyText As String
Subject = InputBox("Objet du message", CurrentDb.Properties("AppTitle"))
BodyText = InputBox("Corps du message", CurrentDb.Properties("AppTitle"))
Dim Rs As Object
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT T_Salariés_Pièces.Sp_Lien INTO TL_Salariés_Pièces_Contrat_Sélection FROM T_Salariés_Pièces " & _
"WHERE (((T_Salariés_Pièces.Sp_Sélection) = True) And ((T_Salariés_Pièces.Sp_Clé_Personne_Nature) = " & CléPersonneNature & ")) " & _
"ORDER BY T_Salariés_Pièces.CléP_Salarié_Pièce"
DoCmd.SetWarnings True
'Nouveau ---------------------------------------------------------------------
Set Rs = CurrentDb.OpenRecordset("TL_Salariés_Pièces_Contrat_Sélection")
Rs.MoveLast
Rs.MoveFirst
ReDim TabVariable(1 To 2, 1 To 1) As String
TabVariable(1, 1) = "Lien"
TabVariable(2, 1) = "numero"
For i = 1 To Rs.RecordCount
If Not IsNull(Rs.Sp_Lien) Then 'test champs Sp_Lien
ReDim Preserve TabVariable(1 To 2, 1 To UBound(TabVariable, 2) + 1)
TabVariable(1, UBound(TabVariable, 2)) = CheminContrat & Rs.Sp_Lien
TabVariable(2, UBound(TabVariable, 2)) = i
End If
Rs.MoveNext
Next i
Set Rs = Nothing
'Nouveau Fin ------------------------------------------------------------------
Suite:
DoCmd.SetWarnings True
Sender = DFirst("Cst_Email_Expéditeur", "T_Constantes")
Recipient = DFirst("Cst_Compta_Messagerie", "T_Constantes")
'Envoie l'Email avec les pièces jointes
Dim Cdo_Message As New cdo.message
Set Cdo_Message.Configuration = GetSMTPServerConfig()
Dim Attache As String
With Cdo_Message
.From = Sender
.To = Recipient
.Subject = Subject
.Bcc = Bcc
.TextBody = BodyText
For i = 2 To UBound(TabVariable, 2)
.AddAttachment = .AddAttachment & TabVariable(1, i) & ";"
Next i
.Send
End With
Set Cdo_Message = Nothing
MsgBox "C'est parti !"
End If
End If
End Sub |
Partager