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 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| Private Sub btn_envoyer_Click()
Dim varitem As Variant
Dim nbselection As Long
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from t_temp_etiquettes_pros"
DoCmd.SetWarnings True
Rem declaration des variables
Dim db As DAO.Database
Dim rcd As DAO.Recordset
Dim rcd_temp As DAO.Recordset
Rem Initialisation des variables
Set db = CurrentDb
Set rcd = db.OpenRecordset("Select * from T_Professionnel")
Set rcd_temp = db.OpenRecordset("Select * from t_temp_etiquettes_pros")
While Not rcd.EOF
rcd_temp.AddNew
'temp/T-Pro
rcd_temp(0) = rcd(0)
rcd_temp(1) = rcd(5)
rcd_temp(2) = rcd(4)
rcd_temp(3) = rcd(7)
rcd_temp(4) = rcd(6)
rcd_temp(5) = rcd(8)
rcd_temp(6) = rcd(9)
rcd_temp(7) = rcd(12)
rcd_temp.Update
rcd.MoveNext
Wend
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE t_temp_etiquettes_pros SET selectionne=0"
DoCmd.SetWarnings True
For Each varitem In Me.Lst_Pro.ItemsSelected
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE t_temp_etiquettes_pros SET selectionne=-1 WHERE NumeroProfessionnel=" & Me.Lst_Pro.ItemData(varitem)
DoCmd.SetWarnings False
nbselection = nbselection + 1
Next varitem
EnvoyerEmailEtFichiersListe
End Sub
Private Sub EnvoyerEmailEtFichiersListe()
Dim olApp As Outlook.Application
Dim miEmail As Outlook.MailItem
Dim rcCCI As Outlook.Recipient
Dim db As DAO.Database
Dim rct_cci As DAO.Recordset
Dim nb_cci As Long
Dim str_cci As String
Dim i As Long
Rem Initialiser un objet Outlook
Set olApp = CreateObject("Outlook.Application")
Rem Initialise un objet base de données
Set db = CurrentDb
Rem Créer le message
Set miEmail = olApp.CreateItem(olMailItem)
Rem Initialise la liste des contacts de la requête
Set rct_cci = db.OpenRecordset("Select EmailProfessionnel from t_temp_etiquettes_pros where EmailProfessionnel is not null and EmailProfessionnel<>'' and selectionne=-1")
Rem Initialise le nombre de CCI
While Not rct_cci.EOF
nb_cci = nb_cci + 1
rct_cci.MoveNext
Wend
Rem Si il y a au moins un CCI, alors repositionnement du recordset au début
If nb_cci > 0 Then
rct_cci.MoveFirst
End If
Rem Initialisation de la chaîne d'adresses Email CCI
For i = 0 To nb_cci - 1
If i > 0 And i < nb_cci Then
str_cci = str_cci & ";"
End If
str_cci = str_cci & rct_cci("EmailProfessionnel")
rct_cci.MoveNext
Next i
Rem Renseigner le message
With miEmail
Rem Définir le CCI, et le vérifier
Set rcCCI = .Recipients.Add(str_cci)
rcCCI.type = olTo
Rem Afficher le message
.Display
End With
Set miEmail = Nothing
Set olApp = Nothing
End Sub |
Partager