Comment créer un publipostage automatique avec Excel ? (sans Word !)

Le publipostage est une fonctionnalité puissante pour la création de courriers personnalisés, que ce soit pour des campagnes d'emailing, des invitations, des avis de promotion ou pour gérer efficacement la relation client.

Dans ce tutoriel, je vais vous montrer comment Excel, avec sa capacité à organiser et manipuler de grandes quantités de données, peut simplifier considérablement ce processus.

Nous verrons les étapes essentielles pour mettre en place un publipostage complet et automatique, sans avoir besoin de jongler avec d’autres applications, comme Word.

Nous verrons ainsi comment préparer vos données, saisir votre courrier, et automatiser le publipostage à l'aide de macros VBA.

Que vous soyez débutant ou utilisateur chevronné, ce tutoriel vous permettra de maîtriser cette fonctionnalité clé d'Excel pour optimiser vos communications professionnelles.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Partie 1 : Création d'un publipostage courrier

Partie 2 : Création d'un publipostage e-mail

1. Qu’est-ce que le publipostage ?

Le terme de « publipostage » désigne une fonctionnalité très utile dans le cadre de la rédaction de courrier, ou de communication.

En effet, il s'agit d'une méthode permettant de créer une série de documents qui sont presque identiques dans leur contenu général, mais qui contiennent certains éléments spécifiques à chaque destinataire, tels que le nom, l'adresse ou toute autre information personnalisée.

Ce processus est souvent utilisé pour les campagnes d'emailing, les invitations, les avis de promotion ou les informations relatives à la gestion de la relation client.

S’il est plutôt d’usage d’utiliser une application de traitement de texte pour mettre en place le publipostage en utilisant la fonctionnalité directement intégrée qui permet d'insérer des champs de données provenant d'une source externe.

Mais lorsqu’il s’agit de réaliser un publipostage rapide, Excel peut alors se distingue par sa capacité à organiser et à manipuler de grands volumes de données avant de les exporter pour le publipostage sans nécessiter de passer d’une application à l’autre.

2. Préparation des données

La préparation des données est une étape préalable importante à la mise en place d’un publipostage efficace.

La structure de la base de données doit être claire et logique pour permettre une identification rapide et optimale des informations.

Pour illustrer ce tutoriel, nous allons pourvoir utiliser la mini-base de données suivante dans laquelle chaque information est présentée dans une colonne dédiée.

De plus, chaque enregistrement (ou chaque client) est placé sur une ligne distincte.

Cela permet non seulement de visualiser clairement les informations associées à chaque personne, mais aussi de faciliter l'extraction de ces données pour le publipostage.

3. Saisie du courrier

Une fois que la base est mise en place, nous pouvons passer à la rédaction du courrier.

Cette étape de rédaction est essentielle dans le processus de publipostage.

Pour simplifier cette rédaction, nous utiliserons ici une zone de texte qui va nous permettre de saisir le corps du courrier d’une manière structurée et flexible.

Pour insérer une zone de texte, rendez-vous dans le menu « Insertion » / « Texte » / « Zone de texte ».

Ensuite, nous allons faire apparaître les champs à personnaliser dans le cadre du publipostage entre crochets (« [Nom] », « [Prénom] », « [Adresse] »…).

Il est important que vérifier que les noms des champs utilisés correspondent bien aux titres de de colonne de la base de données.

4. Mise en place du publipostage

Maintenant que la base de notre publipostage est bien paramétrée, nous allons pouvoir créer celui-ci.

Et pour cela, nous allons mettre en place une petite macro commande en VBA.

Une macro-commande VBA (Visual Basic for Applications) est un ensemble d'instructions écrites dans le langage de programmation VBA qui peut être exécuté dans les applications Microsoft Office pour automatiser des tâches répétitives.

Ces macros peuvent effectuer des opérations complexes et répétitives en un seul clic, comme le formatage des données, le calcul de formules ou, dans notre cas, le publipostage.

Comme d’habitude, pour que tout le monde puisse suivre ce tutoriel, quelque soit le niveau de connaissance en VBA, je vais vous expliquer chacune des lignes de code.

Mais si vous souhaitez aller plus loin et découvrir comment les macro-commandes peuvent révolutionner votre utilisation d’Excel, cliquez ici pour découvrir mon livre « Le VBA pour les débutants ».

Dans ce livre je vous présente les bases du langage VBA et comment créer vos propres macros, même si vous n’avez aucune connaissance en développement à ce jour.

Donc pour créer la macro VBA, nous allons commencer par ouvrir l’éditeur de code VBA en appuyant sur les touches [Alt]+[F11].

Celui-ci s’ouvre alors sur le projet en cours de xxx, c’est-à-dire sur le classeur actif.

Ensuite, nous allons insérer un nouveau module dans ce projet afin de pouvoir y insérer notre macro, en nous rendant dans le menu « Insertion » et en cliquant sur « Module ».

Une fois le module ajouté, nous allons y saisir les lignes de code de la macro.

Pour cela, nous commençons par créer la nouvelle macro en utilisant la commande « Sub », suivi du nom que nous souhaitons donner à celle-ci, ici nous l’appellerons simplement « publipostage » :

Sub publipostage

Lorsque nous validons la création de la macro en appuyant sur la touche [Entrée], VBA va alors ajouter la ligne « End Sub » à notre code, ce qui va permettre de fermer la macro, et donc tout ce que nous allons saisir entre ces deux lignes s’exécutera à chaque fois que nous appellerons la macro « publipostage ».

Maintenant, dans la macro, nous allons pourvoir déclarer deux variables pour stocker les noms des classeurs utilisés dans le processus :

• « classeurOrigine » sera le nom du classeur actif dans lequel se trouve les données, et a partir duquel nous appelons la macro,
• et « classeurPublipostage » sera le nom du nouveau classeur que nous allons créer pour le publipostage.

Une variable est une zone allouée dans la mémoire de l’ordinateur qui permet de stocker et de manipuler des informations.

Pour la créer, nous utilisons le mot-clé « Dim », suivi du nom que nous souhaitons lui attribuer.

Nous pouvons également un type à celles-ci, c’est-à-dire que nous allons préciser le type d’information que nous allons souhaiter manipuler avec ces variables.

Ici, étant donné que nous souhaitons y stocker des noms de feuilles, nous les typons en tant que « String », c’est-à-dire en tant que chaîne de caractères.

Dim classeurOrigine As String, classeurPublipostage As  String

Nous pouvons maintenant enregistrer le nom du classeur actif dans la variable « classeurOrigine » que nous venons de déclarer grâce à l’instruction « ActiveWorkbook.Name »

classeurOrigine = ActiveWorkbook.Name

Ensuite, nous allons souhaiter que le publipostage se mette en place dans un nouveau classeur dans lequel chaque courrier nominatif sera une feuille de classeur dédiée.

Pour créer un nouveau classeur, nous utilisons la commande « Workbooks.Add » :

Workbooks.Add

Puis, nous stockons son nom dans la variable « classeurPublipostage » :

classeurPublipostage = ActiveWorkbook.Name

Nous ajoutons ensuite, une cellule en A1 indiquant la date et l'heure du publipostage automatique :

[A1] = "Publipostage automatique réalisé  le " & Format(Now, "dd/mm/yyyy hh:mm")

Cela étant fait, nous pouvons revenir dans le classeur d'origine pour continuer à y travailler.

Windows(classeurOrigine).Activate

Maintenant, nous allons enregistrer le tableau dans lequel se trouvent les informations de publipostage dans une nouvelle variable que nous nommons « table ».

Nous typons cette variable comme un objet « Range », c’est-à-dire comme une plage de cellules.

Dim table As Range

Pour déterminer automatiquement l’ensemble des cellules qui le compose, nous pouvons utiliser l’instruction VBA « CurrentRegion » qui étant automatiquement une cellule donnée à l’ensemble de la zone qui la compose.

Ici, nous partons de la cellule [A8], qui est la première cellule du tableau.

Enfin, étant donné que la variable « table » est un objet VBA, nous devons utiliser le mot-clé « Set » pour lui affecter une plage de cellule :

Set table = [a8].CurrentRegion

Nous allons maintenant mettre en place une boucle qui va permettre de parcourir toutes les lignes de la base de données, puis sur chacune de ces lignes, nous analyserons les colonnes correspondantes.

Pour cela, nous initialisons deux variables « ligne » et « colonne » qui vont stocker les numéros de lignes et de colonnes :

Dim ligne As Integer, colonne As Integer

Puis, pour passer en revue toutes les lignes, nous démarrons une boucle « For », en commençant directement à la deuxième ligne pour éviter les en-têtes.

Une boucle « For » est une instruction VBA qui permet répéter une opération tant qu’une condition est remplie.

Ici, nous allons utiliser la variable « ligne » pour mettre en place cette boucle, en commençant par lui donner la valeur de 2, et incrémentant cette valeur jusqu’à ce que celle-ci soit égale au nombre de lignes de la base de données

For ligne = 2 To table.Rows.Count

À l'intérieur de cette boucle, nous déclarons encore une nouvelle variable nommée « enregistrements » qui va permettre de recueillir les données de la ligne actuelle.

Dim enregistrements as String 
 Enregistrements = ""

À l'intérieur de la boucle de ligne, nous démarrons une autre boucle pour parcourir cette fois-ci les colonnes de la table et construire la chaîne « enregistrements ».

Celle-ci va être consitutuée de couple de données avec le nom de la colonne, et l’information en cours d’analyse, séparés par des points-virgules.

Puis, chaque cellule sera ajoutée à la suite, en utilisant cette fois-ci des points d'exclamation comme caractère de séparation :

For colonne = 1 To table.Columns.Count
    enregistrements = enregistrements & table(1,  colonne) & ";" & table(ligne, colonne) & "!"
Next

Cette manière d’organiser les données va ensuite nous permettre de retrouver l’arborescence de la base de données de manière simplifiée en utilisant l’instruction Split(), comme nous le verrons juste après.

En effet, avant cela, nous allons souhaiter transférer la zone de texte contenant le texte à publiposter sur le nouveau classeur, au sein d’une nouvelle feuille de calcul.

Pour simplifier ce processus, comme nous devons nous assurer qu’un seul objet se trouve sur la feuille de calcul, ce qui nous permettra de le sélectionner rapidement à partir de l’instruction :

ActiveSheet.Shapes(1).Copy

Ensuite, nous allons revenir sur le classeur de publipostage afin d’insérer une nouvelle feuille de calcul :

Windows(classeurPublipostage).Activate
Sheets.Add After:=ActiveSheet

Puis nous y collons la zone de texte :

ActiveSheet.Paste

Il ne restera alors plus qu’à remplacer les balises placées entre crochets par la valeur correspondante, en sachant que nous avions enregistré les paires de données titre/valeur dans la variable « enregistrement ».

Pour cela, nous allons maintenant déclarer une nouvelle variable « enregistrement » (au singulier cette fois) pour traiter chaque paire de données et une variable « nouveauTexte » afin de manipuler le contenu de la zone de texte :

Dim enregistrement As Variant, nouveauTexte As String

Nous pouvons maintenant démarre une boucle « For Each » pour traiter chaque paire de données de la variable « enregistrements ».

Cette déclinaison de la boucle « For » de passer en revue tous les éléments d’un ensemble de données.

Ici, nous utiliserons donc la fonction VBA Split() qui va utiliser le caractère de séparation de chacune des lignes de la variable « enregistrements », en nous rappelant que nous avions utilisé le point d’exclamation comme séparateur de ligne un peu plus tôt :

For Each enregistrement In Split(enregistrements,  "!")

À l'intérieur de cette boucle, nous vérifions si l'enregistrement n'est pas vide, puis remplaçons le texte entre crochets par les données correspondantes, après avoir enregistré le contenu de la zone de texte dans la variable « nouveauTexte » :

    If enregistrement <> "" Then
        nouveauTexte =  ActiveSheet.Shapes(1).TextFrame.Characters.Text
        nouveauTexte = Replace(nouveauTexte, "["  & Split(enregistrement, ";")(0) & "]",  Split(enregistrement, ";")(1), 1)
        ActiveSheet.Shapes(1).TextFrame.Characters.Text =  nouveauTexte
    End If

Comme vous pouvez le constater, nous accédons et nous modifions le contenu de cette zone de texte avec l’instruction « ActiveSheet.Shapes(1).TextFrame.Characters.Text ».

Ensuite, nous pouvons revenir le classeur « classeurOrigine » afin de passer à l’analyse de la ligne suivante :

Windows(classeurOrigine).Activate

Et voilà, notre macro est maintenant terminée.

Pour la tester, il suffit de revenir sur la feuille de calcul, d’appuyer sur les touches [Alt]+[F8] afin de lancer la fenêtre d’exécution des macros.

Sur cette fenêtre, nous sélectionnons la macro « publipostage », puis nous attendons qu’Excel ait terminé son travail.

Lors du lancement de la macro, il est possible que vous soyez confronté à une erreur ‘1004’

Si c’est le cas, pas d’inquiétude, cette erreur est liée au fait qu’Excel n’a pas eu le temps de copier la zone de texte avant que nous lui demandions de se placer sur la nouvelle feuille afin de la coller.

Dans ce cas, il suffit d’ajouter une légère pose entre ces deux opérations en insérant la ligne suivante, juste après « ActiveSheet.Shapes(1).Copy »

Application.Wait (Now +  TimeValue("0:00:01"))

Normalement maintenant tout devait rentre dans l’ordre.

Une fois le classeur de publipostage générée, vous pourrez éditer l’ensemble des courriers très rapidement en sélectionnant « Imprimer le classeur entier » dans les options d’édition :

Et voilà, le publipostage est maintenant terminé !

5. Effectuer le publipostage par email

Maintenant, continuons sur notre lancée pour découvrir cette fois-ci comment mettre en place un publipostage par email. Cette méthode permet de diffuser rapidement et efficacement vos documents personnalisés à chaque destinataire, en utilisant leur adresse mail.

Ici, nous allons utiliser la zone de texte qui nous servait déjà d’exemple pour la publication classique, mais bien entendu, il sera nécessaire d’adapter le texte pour que celui-ci corresponde au contexte de l’envoi d’un email.

Entrons maintenant dans le vif du sujet en lançant d’éditeur de code VBA (pour rappel celui-ci s’affiche en utilisant le raccourci clavier [Alt]+[F11]).

Nous y retrouvons alors la macro précédente, mais pour éviter tout risque de confusion, nous allons souhaiter créer un nouveau module pour celle-ci.

Dans ce module, nous insérons la macro « creerEmail » :

Sub creerEmail()
End Sub

Comme nous l’avons vu un peu plus tôt, cela permet de créer une macro, dans laquelle nous saisirons les lignes de code permettant d’envoyer le mail.

Avant de commencer, nous devons activer la référence à la librairie Microsoft Outlook dans notre projet pour interagir directement avec l'application de messagerie.

Pour cela, allez dans le menu Outils > Références, puis trouvez la ligne « Microsoft Outlook 16.0 Object Library » dans la liste alphabétique des librairies (le numéro de version varie selon votre Office).

Activez la référence en cochant la case et confirmez avec le bouton [OK].

Ainsi, nous pourrons utiliser des variables, objets et procédures pour contrôler Outlook directement depuis Excel.

Maintenant, nous allons pouvoir créer un nouvel objet de gestion de l'application Outlook, que nous nommerons oOutlook.

Sub creerEmail()
    Dim oOutlook As Object
    Set oOutlook =  CreateObject("Outlook.Application")

Cela étant fait, nous insérerons un nouveau mail dans l'application en utilisant un objet spécifique nommé « oMail ».

Ce mail peut être créé en employant la méthode « CreateItem(0) » sur l'objet « oOutlook ».

    Dim oMail As Object
    Set oMail = oOutlook.CreateItem(0)

Et voilà le mail est créé, il ne reste plus qu’à le paramétrer !

Pour faciliter le processus de personnalisation du mail, nous utiliserons l'instruction « With » de VBA, qui nous évitera de devoir répéter l'appel du mail à chaque fois.

En effet, cette instruction « With » est utilisée pour simplifier l'écriture du code lorsque vous devez exécuter plusieurs opérations sur un même objet. Au lieu de répéter le nom de l'objet à chaque fois que vous accédez à ses propriétés ou méthodes, vous pouvez l'encapsuler dans un bloc With, ce qui rend le code plus propre, plus facile à lire et à maintenir, surtout lorsque vous travaillez avec des objets ayant de nombreuses propriétés à configurer, comme c'est le cas pour un email dans une application de messagerie.

    With oMail  
    End With

La première chose à faire va alors être de renseigner l’adresse du destinataire du mail :

        .To = email

Cette adresse mail que nous allons passer en tant qu’argument de la fonction « creerMail », en saisissant simplement le nom de cette variable entre les parenthèses de la macro, et en utilisant le type « String » :

Sub creerEmail (email as String)

Nous allons également utiliser cette technique pour générer le corps du mail, avec l’argument « corps » et la propriété « Body » de l’objet « oMail » :

Sub creerEmail(email As String, corps As  String)
    …
        .Body = corps

Enfin, nous allons également insérer le titre du mail avec la propriété « .Subject » :

        .Subject = "Résiliation de votre  abonnement"

Maintenant, le mail est préparé, nous pouvons soit l’afficher dans l’interface d’Outlook, ce qui va nous permettre de contrôler l’ensemble des champs avant de l’envoyer manuellement, et dans ce cas, nous allons utiliser la méthode .Display de oMail,

Soit, et uniquement si nous sommes certains du contenu de celui-ci, nous pouvons également opter pour l’envoi direct du mail de manière transparente et dans ce cas, nous utiliserons plutôt la méthode .Send (attention dans ce cas, le mail va partir immédiatement sans possibilité de le contrôler !)

Dans notre exemple, nous allons préférer afficher le message avant son envoi :

      .Display

La macro que nous venons de créer va donc permettre d’envoyer un mail vers une adresse donnée.

Nous allons maintenant pouvoir l’exploiter dans le cadre du publipostage automatique.

Pour cela, revenons dans la macro « publipostage » que nous avions créé dans la partie précédente, dans laquelle il va nous suffire d’appeler cette nouvelle macro.

Pour commencer, plaçons-nous dans la boucle de génération du publipostage afin d’y déclarer une nouvelle variable que nous appelons simplement « email » et qui va permettre de capturer l’adresse email du destinataire lors de la création du publipostage :

Dim ligne As Integer, colonne As Integer
    For ligne = 2 To table.Rows.Count
        Dim enregistrements As String, email As  String

Ensuite, dans la seconde boucle qui se trouve à l’intérieur de celle-ci et qui permet d’effectuer les remplacements de champs, nous allons vérifier si le champ en cours d’analyse est celui qui correspond à la colonne « Mail » de notre base. Lorsque ce sera effectivement le cas, alors nous pourrons l’enregistrer dans cette variable « email » :

                If Split(enregistrement,  ";")(0) = "Mail" Then
                    email = Split(enregistrement,  ";")(1)
                End If

Ensuite, juste après cette boucle, il ne reste plus qu’à appeler notre nouvelle macro « creerMail » en passant en argument l’email et le contenu du texte modifié :

        creerEmail email, nouveauTexte

Et voilà, la macro est maintenant terminée, nous pouvons revenir sur la feuille de calcul, adapter le contenu de la zone de texte, et cliquer sur le bouton permettant de générer le publipostage !

Ce processus de publipostage par email maximise l'efficacité de vos communications en vous permettant d'envoyer des messages personnalisés à un grand nombre de destinataires de manière rapide et organisée.