Réaliser un système d'envoi de documents individuels avec Access

Denis Hulo

Objectif : mettre en place un système permettant de générer et d'envoyer des documents individuels par e-mails depuis Access.

Niveau requis : confirmé.

Commentez cet article : Commentez

- Titre : Réaliser un système d'envoi de documents individuels avec Access

- Auteur : Denis Hulo

- Parution : 8 mai 2020

- Mise à jour : 17 mai 2020

- Public visé :

- Licence : Copyright ® 2020 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Processus de réabonnement

- III. Structure de l'application

- III-A. Tables principales

- III-A-1. T_Abonne

- III-A-2. T_Abonnement

- III-A-3. T_Message_Reabonnement

- III-B. Requête R_Echeances_Abonnements

- III-C. Formulaire d'envoi des documents

- III-C-1. Sous-formulaire des échéances

- III-C-2. Procédure sur clic du bouton d'envoi

- III-D. État E_Reabonnement

- III-E. Macro Autoexec

- III-F. Module standard

- III-F-1. Fonction d'envoi d'un e-mail

- III-F-2. Fonction d'envoi des documents individuels

- IV. Base de données à télécharger

- V. Remerciements

I - Introduction

Dans une maison de la presse en ligne, on doit demander aux clients de renouveler leurs abonnements annuels à des journaux ou à des magazines. Pour cela, il nous faut envoyer par e-mail à chaque abonné son document personnel pour son réabonnement.

Formulaire d'envoi

On souhaite dans ce contexte, générer pour chaque client, son document individuel avant de l'envoyer par automation avec Outlook.

II - Processus de réabonnement

Le processus de réabonnement des clients à leurs journaux ou magazines :

- on enregistre au préalable, les données concernant les abonnements et les abonnées dans deux tables distinctes ;

- on parcourt les abonnements devant être renouvelés prochainement ;

- on teste pour chaque abonnement si un envoi a déjà été effectué ;

- dans le cas contraire, on transmet par e-mail au client le document pour son renouvellement ;

- après réception de l'accord du client, on met à jour la base avec sa date de réabonnement.

La procédure de contrôle et d'envoi des documents doit s'exécuter au démarrage de l'application et aussi depuis un bouton de commande situé dans un formulaire.

On décrit par la suite, dans l'ordre, les différents objets permettant de mettre en place ce système de réabonnement.

III - Structure de l'application

La base exemple comporte :

- les tables permettant d'enregistrer les données concernant les abonnements et les clients ;

- la requête pour sélectionner les abonnements à partir d'un mois avant leur échéance ;

- l'interface Access pour rédiger et envoyer le message destiné aux abonnés, avec leur document individuel ;

- l'état servant de modèle pour générer les documents pdf destinés aux clients ;

- la macro Autoexec permet de contrôler au démarrage s'il y a des fichiers à transmettre, puis de les envoyer aux abonnés après confirmation ;

- les fonctions de transmission des documents pour les réabonnements.

III-A - Tables principales

III-A-1 - T_Abonne

Elle contient les informations relatives aux abonnés.

Nom du champ

Type de données

Description du champ

IdAbonne

NumeroAuto

Identifiant de l'abonné

NomAbonne

Texte

Nom de l'abonné

PrenomAbonne

Texte

Prénom de l'abonné

Email

Texte

E-mail de l'abonné

…

…

…

Le champ [Email] permet l'envoi du document avec Outlook.

III-A-2 - T_Abonnement

Elle contient les informations relatives aux abonnements.

Nom du champ

Type de données

Description du champ

IdAbonnement

NumeroAuto

Identifiant de l'abonnement

IdAbonne

Entier long

Identifiant de l'abonné concerné

DateAbo

Date/Heure

Date du premier abonnement

DateReabo

Date/Heure

Date du dernier réabonnement

DateEnvoiReabo

Date/Heure

Date du dernier envoi pour le réabonnement

DesignationAbo

Texte

Désignation de l'abonnement

…

…

…

Le champ [DateReabo] permet de savoir si l'abonnement est à jour.

III-A-3 - T_Message_Reabonnement

Elle permet d'enregistrer l'objet et le corps du message à envoyer aux clients pour leur réabonnement.

Nom du champ

Type de données

Description du champ

ObjetMessage

Texte

Objet du message à envoyer

CorpsMessage

Texte

Corps du message à envoyer

…

…

…

III-B - Requête R_Echeances_Abonnements

Elle affiche les abonnements des clients proches de leur échéance.

Requête R_Echeances_Abonnements

Expression pour évaluer la date du dernier abonnement :

DateDernierAbo: Nz([DateReabo];[DateAbo])

Si le champ [DateReabo] est vide, la fonction Nz renvoie la date du premier abonnement avec le champ [DateAbo], sinon elle retourne la date du dernier abonnement avec [DateReabo].

Expression pour évaluer la date d'échéance :

DateEcheance: AjDate("m";12;Nz([DateReabo];[DateAbo]))

On ajoute 12 mois à la date du dernier abonnement. Le critère permet de filtrer les données 1 mois avant échéance.

Expression pour évaluer si l'envoi du document a été réalisé :

Envoi: (Nz([DateEnvoiReabo];#01/01/1000#))>Nz([DateReabo];[DateAbo]))

On teste si la date d'envoi est postérieure à la date du dernier abonnement.

Si vous souhaitez effectuer des relances, par exemple tous les 15 jours, il faut en plus comparer la date d'envoi plus 15 jours à la date d'aujourd'hui :

Envoi: (Nz([DateEnvoiReabo];#01/01/1000#))>Nz([DateReabo];[DateAbo])) and (Nz([DateEnvoiReabo];#01/01/1000#)+15<Date())

Code SQL complet :

SELECT T_Abonnement.*, "A-" & Format([IdAbonnement],"000000") AS RefAbonnement, T_Abonne.NomAbonne, T_Abonne.PrenomAbonne, T_Abonne.Email, Nz([DateReabo],[DateAbo]) AS DateDernierAbo, DateAdd("m",12,Nz([DateReabo],[DateAbo])) AS DateEcheance, (Nz([DateEnvoiReabo],#1/1/1000#))>=Nz([DateReabo],[DateAbo]) AS Envoi
FROM T_Abonne INNER JOIN T_Abonnement ON T_Abonne.IDAbonne = T_Abonnement.IdAbonne
WHERE (((DateAdd("m",12,Nz([DateReabo],[DateAbo])))<=DateAdd("m",1,Date())));

Cette requête est utilisée dans la procédure d'envoi et permet de parcourir la liste des abonnements à partir d'un mois avant leur échéance.

III-C - Formulaire d'envoi des documents

Il est lié à la table contenant le message, et comporte en particulier deux zones de texte pour enregistrer l'objet et le corps du message à envoyer aux destinataires.

Formulaire d'envoi

La zone de texte pour enregistrer le corps du message permet de gérer du texte enrichi (caractères en gras, colorisation des caractères, etc.). Il suffit pour cela de mettre sa propriété Format du texte à Texte enrichi.

III-C-1 - Sous-formulaire des échéances

Il contient également un sous-formulaire pour afficher les abonnements proches de leur échéance.

Sous-formulaire des échéances

Les lignes en rouge correspondent aux abonnements n'ayant pas encore fait l'objet d'un envoi pour le renouvellement, celles en vert indiquent ceux dont l'envoi a été effectué.

III-C-2 - Procédure sur clic du bouton d'envoi

Elle permet d'exécuter la procédure d'envoi des documents individuels aux abonnés en vue de leur réabonnement :

Private Sub CmdEnvoyerDocuments_Click()

Me.Refresh ' enregistre les changements effectués sur le formulaire
If EnvoiDocuments() Then ' si l'envoi s'est bien passé
Me.Refresh ' on rafraîchit le formulaire
End If
End Sub

III-D - État E_Reabonnement

Le document est généré à partir de cet état basé sur la table des abonnements. Il comporte en particulier les informations concernant le client, avec en plus deux champs pour noter la date et signer le document avant de le retourner.

État E_Reabonnement

La ligne de code permettant de transformer l'état en document pdf est :

DoCmd.OutputTo acOutputReport, "E_Reabonnement", "PDF", cheminfichier

Celle ligne de commande est présente dans la fonction EnvoiDocuments.

III-E - Macro Autoexec

La macro permet de tester au démarrage de l'application si parmi les abonnements arrivant bientôt à échéance, certains n'ont pas encore fait l'objet d'un renouvellement, et, si c'est le cas, d'envoyer les documents pour les réabonnements.

Elle exécute à l'ouverture de la base la fonction EnvoiDocuments :

Macro Autoexec

L'argument Vrai indique que la fonction s'exécute automatiquement sans avoir besoin d'intervenir.

Le code de la fonction est présenté dans la section suivante.

III-F - Module standard

Il contient les fonctions permettant de générer et de transmettre aux clients les documents individuels.

III-F-1 - Fonction d'envoi d'un e-mail

Elle permet l'envoi d'un e-mail avec pièce jointe en pilotant Outlook par automation.

Cette fonction a comme arguments :

- cheminfichier : chemin complet sur le disque du document à envoyer ;

- email : adresse e-mail de l'abonné auquel on envoie le document ;

- objetMessage : objet du message à envoyer ;

- corpsMessage : corps du message à envoyer ;

- objOutLook : référence à l'application Outlook.

Function EnvoiEmail(cheminfichier As String, EMail As String, ObjetMessage As String, CorpsMessage As String, objOutLook As Object) As Boolean

'On Error GoTo Erreur_EnvoiEMail
Dim objMessage As Object ' Outlook.MailItem ' variable objet pour faire référence au mail

Set objMessage = objOutLook.createitem(0) ' création de l'objet Outlook.MailItem

With objMessage
.To = EMail ' on copie l'adresse mail du destinataire
.Subject = ObjetMessage ' on copie l'objet du message
'Corps du message
.BodyFormat = 3 'olFormatRichText : on définit le format Texte enrichi
.HTMLBody = CorpsMessage ' on copie le message

If (cheminfichier <> "") Then ' si un chemin de fichier est passé en argument
objMessage.Attachments.Add cheminfichier ' on attache le fichier au mail
End If

.Send ' envoi de l'e-mail
EnvoiEmail = True ' on indique que tout s'est bien passé

End With

'On libère la mémoire
Set objMessage = Nothing

' on sort de la fonction
Exit Function

Erreur_EnvoiEmail:
' Gestion de l'erreur
Select Case Err.Number

Case -2147467259  ' adresse invalide
MsgBox "Adresse e-mail invalide"

Case 2501
MsgBox Err.Number & " " & Err.Description
End Select
' si le message n'a pas été envoyé, on le signale en renvoyant une chaîne vide
EnvoiEmail = False
End Function

III-F-2 - Fonction d'envoi des documents individuels

Elle permet la génération et la transmission des documents individuels aux abonnés.

Exemple d'appel :

call EnvoiDocuments(true)

Elle possède un argument de type booléen :

- true : indique qu'elle s'exécute automatiquement, par exemple au démarrage de l'application ;

- false : valeur par défaut, indique qu'elle ne s'exécute pas automatiquement.

Déroulé de la fonction

- on parcourt la liste des abonnements proches de leur échéance ;

- pour chaque abonnement, on génère le document associé dans un dossier ;

- on envoie ensuite le document destiné à l'abonné.

Etapes importantes dans le code

Ouverture du Recordset basé sur la requête affichant les abonnements proches de leur échéance :

Set rsReabo = db.OpenRecordset("select * from R_Echeances_Abonnements where (Envoi=False) and nz(Email,"""")<>"""";", dbOpenDynaset)

Ligne de code permettant de transformer l'état en document pdf :

DoCmd.OutputTo acOutputReport, "E_Reabonnement", "PDF", cheminfichier

Ligne de commande permettant d'envoyer le document pdf :

If EnvoiEmail(cheminfichier, rs!email, ObjetMessage, CorpsMessage, objOutLook) Then

Code complet de la fonction :

Public Function EnvoiDocuments(Optional Automatique As Boolean = False) As Boolean
'Automatique : argument optionnel indiquant si la fonction s'exécute automatiquement ou pas.
On Error GoTo err_EnvoiDocuments
Dim fso As Object ' variable objet FSO
Dim nomDossier As String ' variable pour le nom du dossier de sauvegarde des documents pdf
Dim cheminfichier As String ' variable pour le chemin complet du document pdf
Dim NomAbonne As String ' nom de l'abonné
Dim db As DAO.Database ' variable objet pour faire référence à la base de données
Dim rsMsg As DAO.Recordset ' variable objet pour faire référence au recordset lié au message à envoyer
Dim rsReabo As DAO.Recordset ' variable objet pour faire référence au recordset lié aux réabonnements
Dim objOutLook As Object ' variable objet pour faire référence à l'application Outlook

Set db = CurrentDb ' référence à la base de données courante

' on ouvre le recordset basé sur la table T_Message_Reabonnement
Set rsMsg = db.OpenRecordset("T_Message_Reabonnement", dbOpenSnapshot)

' on vérifie si l'objet et le corps du message ont été saisis
If Nz(rsMsg!ObjetMessage) = "" Then
MsgBox ("Saisir un objet pour le message des destinataires !")
Exit Function
End If

If Nz(rsMsg!CorpsMessage) = "" Then
MsgBox ("Saisir un message pour les destinataires !")
Exit Function
End If

' on ouvre le recordset contenant la liste des réabonnement en attente n'ayant pas encore fait l'objet d'un envoi
Set rsReabo = db.OpenRecordset("select * from R_Echeances_Abonnements where (Envoi=False) and nz(Email,"""")<>"""";", dbOpenDynaset)

If Not rsReabo.EOF Then ' s'il y a des documents à envoyer

If MsgBox("Souhaitez-vous envoyer les documents pour les réabonnements ?", vbYesNo + vbQuestion) = vbYes Then ' si on confirme l'envoi

' Teste si outlook est ouvert, si pas ouvert le lance :
If Not IsOutLookRunning() Then
Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
oShell.Run "outlook"
Set oShell = Nothing
End If

'Assigner l'objet Outlook
Set objOutLook = CreateObject("Outlook.Application") 'New Outlook.Application

' création de l'objet FSO
Set fso = CreateObject("Scripting.FileSystemObject")

' indique le chemin du dossier de destination pour les fichiers générés, si pas de dossier d'enregistré dans la table T_Dossier_Documents,
' alors copie le chemin du dossier situé dans le répertoire de la base de données Access
nomDossier = Nz(DLookup("CheminDossier", "T_Dossier_Documents"), CurrentProject.Path & "\Réabonnements") ' indiquez ici le chemin de votre dossier de destination pour les fichiers pdf

If Dir(nomDossier, vbDirectory) = "" Then  '
fso.CreateFolder nomDossier
End If

Do Until rsReabo.EOF  ' on parcourt la liste des abonnements à renouveler

NomAbonne = rsReabo!NomAbonne & " " & rsReabo!PrenomAbonne ' on copie le nom complet de l'abonné dans la variable

' on copie le chemin complet dans la variable
cheminfichier = nomDossier & "\Réabonnement " & NomAbonne & ".pdf"

' ouverture de l'état filtré avec l'identifiant de l'abonné
DoCmd.OpenReport "E_Reabonnement", acViewPreview, , "IdAbonne=" & rsReabo!IdAbonne

' génération du document pdf à partir de l'état filtré avec l'identifiant de l'abonné
DoCmd.OutputTo acOutputReport, "E_Reabonnement", "PDF", cheminfichier

' fermeture de l'état
DoCmd.Close acReport, "E_Reabonnement"

' envoi du message au destinataire
If EnvoiEmail(cheminfichier, rsReabo!EMail, rsMsg!ObjetMessage, rsMsg!CorpsMessage, objOutLook) Then ' si l'envoi du mail s'est bien passé
rsReabo.Edit
rsReabo!DateEnvoiReabo = Date ' on met à jour le champ DateEnvoiReabo pour indiquer que l'envoi a bien été effectué
rsReabo.Update
End If

' on passe à l'enregistrement suivant
rsReabo.MoveNext

Loop

EnvoiDocuments = True ' on indique que les documents ont été envoyés
MsgBox "Documents envoyés !", vbExclamation ' on affiche un message pour indiquer que les documents ont bien été envoyés

End If

Else ' sinon, si pas de document à envoyer

If Not Automatique Then ' si la fonction ne s'exécute pas à l'ouverture de la base
MsgBox "Pas de document à envoyer pour les réabonnements !", vbExclamation ' on affiche un message pour indiquer qu'il n'y a pas d'abonnement à renouveler
End If

EnvoiDocuments = False ' la fonction renvoie False

End If

err_EnvoiDocuments:

' gestion d'erreur
If Err.Number <> 0 And Not EnvoiDocuments Then ' si une erreur s'est produite et que les documents n'ont pas été envoyés
MsgBox Err.Description, vbExclamation ' on affiche le message d'erreur
MsgBox "Erreur au cours de l'envoi !", vbExclamation
EnvoiDocuments = False
End If

' libère les variables objet
Set fso = Nothing

If Not (rsMsg Is Nothing) Then
rsMsg.Close
End If

If Not (rsReabo Is Nothing) Then
rsReabo.Close
End If

Set rsMsg = Nothing
Set rsReabo = Nothing

Set db = Nothing
Set objOutLook = Nothing

End Function

La fonction renvoie true si les documents ont bien été envoyés et false dans le cas contraire. Elle s'exécute au démarrage de l'application avec la macro Autoexec et sur le bouton d'envoi du formulaire.

IV - Base de données à télécharger

La base jointe est au format accdb.

Les adresses e-mails contenues dans la base sont juste là pour l'exemple, donc bien penser à mettre les vôtres avant de tester l'envoi des fichiers.

V - Remerciements

Je tiens à remercier Jean-Philippe André de m'avoir conseillé pour la réalisation de cet article, ainsi que Claude Leloup pour sa relecture.