SOMMAIRE

I Introduction
II Les données du classeur Excel
III Code des macros de création et d’envoi d'email
IV Conclusion

I Introduction

II Les données du classeur Excel

Dans un premier temps nous allons définir le contenu de notre feuille de calcul Excel. Nous allons par exemple créer les colonnes suivantes :

• Nom du client ;
• Email ;
• Société ;
• Article ;
• Date de commande ;
• Date de livraison ;

Ces valeurs sont données à titre d’exemple vous pourrez remplir les cellules comme bon vous semble.

Nous allons maintenant accéder à l’onglet Développeur du ruban Excel. Si celui-ci n’apparaît pas dans votre ruban, vous pouvez l’ajouter par l’intermédiaire de la fenêtre des options Excel. Vous pouvez l’ouvrir en cliquant sur l’onglet Fichiers. Il suffit ensuite de cliquer sur le menu « Personnaliser le ruban ». Il suffit de sélectionner la valeur « Tous les onglets » dans la boîte de commandes qui a pour étiquette associée : « Choisir les commandes dans les catégories suivantes ».

Il faut ensuite sélectionner l’onglet « Développeur » dans la colonne de gauche et cliquer sur le bouton « Ajouter » pour qu’il puisse être ajouté dans la colonne de droite. Normalement ensuite l’onglet doit apparaître dans le ruban.

Sinon vous pourrez toujours accéder à la fenêtre de code en cliquant droit sur l’onglet du classeur et ensuite en sélectionnant la commande de menu « Visualiser le code » :

Une fois dans la fenêtre de code nous allons pouvoir personnaliser le code d’ouverture de mail Outlook.

II Code des macros de création et d’envoi d'email

1. Récupération des données issues du classeur actif

Une fois dans l’éditeur de code VBA d’Excel, il suffit de sélectionner l’élément « ThisWorkbook » dans la section « Projet ».

Nous allons interagir avec le classeur Excel avec l’événement BeforeDoubleClick. Pour pouvoir éditer la méthode Evénement « BeforeDoubleClick ». Dans un premier temps il faut sélectionner l’objet « Workbook » dans la liste de sélection des objets :

Puis dans la liste de sélection des méthodes associées, il faut sélectionner la méthode « BeforeDoubleClick » :

• La méthode SheetBeforeDoubleClick qui va nous permettre de récupérer les données de chaque cellule de la ligne sélectionnée ;
• La méthode MailSend qui va nous permettre de créer une instance de l’objet Outlook dans un premier temps et de créer un nouveau mail à partir des données récupérées précédemment. Puis dans un second temps nous allons modifier cette fonction pour pouvoir utiliser une référence de l'objet Microsoft CDO pour interagir avec un serveur SMTP.

Dim CustomerName As String  ‘permet de récupérer la donnée de la colonne A
Dim Email As String                    ‘permet de récupérer la donnée de la colonne B
Dim Society As String                 ‘permet de récupérer la donnée de la colonne C
Dim Article As String                  ‘permet de récupérer la donnée de la colonne D
Dim Price As String                     ‘permet de récupérer la donnée de la colonne E
Dim PurchasingDate As String ‘permet de récupérer la donnée de la colonne F
Dim DeliveryDate As String      ‘permet de récupérer la donnée de la colonne G
Dim FL As Worksheet                 ‘variable permettant de stocker la valeur du classeur actif
Dim LineNB, ColNB As Long      ‘variable permettant de récupérer la ligne sélectionnée

Ces différentes variables vont nous permettre de récupérer les valeurs de chaque cellule de la ligne sélectionnée.

Ensuite il nous faut sélectionner le classeur sur lequel nous allons travailler, ceci se fait par l’intermédiaire de la propriété Worksheets de l’objet Workbook, comme ceci :

Set FL = Worksheets("Feuil1") ‘Nous attribuons le classeur nommé « feuil1 »

Mais vous pouvez très bien utiliser la propriété ActiveSheet pour travailler sur le classeur sélectionné.

Set FL = ActiveSheet

Une fois que nous avons sélectionné le classeur, il faut garder en mémoire le numéro de la ligne sur laquelle nous avons cliqué. Pour ce faire on utilise la propriété Row de l’objet ActiveCell :

LineNB = ActiveCell.Row

Dans la suite de la méthode nous allons donc attribuer à nos variables les valeurs des différentes cellules correspondantes. Pour récupérer la valeur inscrite dans une cellule nous utilisons la méthode Cells de l’objet Worksheet :

CustomerName = FL.Cells(LineNB, 1)
Email = FL.Cells(LineNB, 2)
Society = FL.Cells(LineNB, 3)
Article = FL.Cells(LineNB, 4)
Price = FL.Cells(LineNB, 5)
PurchasingDate = FL.Cells(LineNB, 6)
DeliveryDate = FL.Cells(LineNB, 7)

Maintenant nous avons toutes les données nécessaires pour créer le sujet du mail ainsi que le corps du mail.

Commençons par définir l’objet du mail. Disons que nous avons besoin de créer un objet de la forme suivante :

Objet : Nom du client – Société - Article – Date de Livraison

Pour pouvoir réaliser cette opération nous avons besoin d’utiliser le contenu des variables suivante :

• CustomerName ;
• Society ;
• Article ;
• DeliveryDate.

Il va falloir donc concaténer le contenu de ces variables comme ceci :

Subject = CustomerName + " - " + Society + " - " + Article + " - " + DeliveryDate

On va faire de même pour le contenu du corps du mail. Imaginons que nous voulons utiliser ce modèle de corps :

Voici comment définir le contenu HTML de la variable Body :

Body = "Bonjour Mr " + CustomerName + "

Le produit " + Article + ", que vous avez commandé le " + PurchasingDate + ", vous sera livré le " + DeliveryDate + ".

Nous vous en souhaitons une bonne réception.

Cordialement,"

Nous avons maintenant tous les éléments nécessaires pour pouvoir créer notre mail. Vous pouvez ajouter des balises html pour pouvoir personnaliser le style du texte.

Il va nous falloir maintenant créer une instance de l’objet Outlook et paramétrer ces propriétés avec les variables que nous venons de définir dans la méthode événement.

Mais avant de réaliser cette opération nous allons appeler la fonction que nous allons créer dans la méthode événement SheetBeforeDoubleClick :

Call MailSend(Subject, Body, Email)

Nous appelons une fonction par l’intermédiaire de la méthode Call et on lui passe en paramètre les variables que l’on a définies dernièrement.

2. Méthode de création et d'envoi d’un mail avec l’objet Outlook

Passons maintenant à la méthode permettant d’ouvrir Outlook et de créer un nouveau mail avec les données précédemment créées.

Nous devons dans un premier temps initialiser une instance de l’objet Outlook :

Dim outlookObj As Object
Set outlookObj = CreateObject("Outlook.Application")

Une fois l’instance de l’application Outlook créée on peut créer une nouvelle instance d’objet Mailque l’on va définir avec la ligne suivante :

Set Mail = outlookObj.CreateItem(0)

Une fois l’objet Mail créé il va falloir paramétrer quelques-unes de ces propriétés comme la source et le destinataire, le sujet ainsi que le corps. Pour définir le corps et le sujet de notre email, on utilise les propriétés HTMLBody et Subject de l’objet Mail. Il nous faut aussi configurer la liste des adresses email que l’on va définir dans le champ « To ». Pour ce faire on va utiliser la propriété Recipients de l’objet Mail. On va inclure toutes ses propriétés dans une boucle With … End With comme nous travaillons exclusivement avec l’objet Mail.

With Mail
        .SentOnBehalfOfName = "username@domain.com"
        .Subject = Subject
        .HTMLBody = Body
        Set Recipients = .Recipients
        Set mailRecipient = Recipients.Add(DstList)
        mailRecipient.Resolve
End With

La méthode Resolve essaye de faire la correspondance entre la liste des adresses données dans la variable DstList et le carnet d’adresse d’Outlook.

Une fois les propriétés de votre email définies il ne reste plus qu’à essayer de l’envoyer ou de l’afficher dans l’application Outlook.

La méthode pour envoyer un mail est Send.

La méthode pour afficher le mail complet dans Outlook est Display.

L’envoi de mail direct est très mal pris en compte car il ne résout pas directement la correspondance entre les adresses définies et le carnet d’adresse.

Si nous essayons de l’envoyer directement nous obtenons ce type de message d’erreur :

Pour résoudre ce problème vous pouvez suivre la procédure que vous trouverez ici.

3. Méthode de création d’un mail en utilisant CDO

Cette deuxième méthode a le mérite de ne pas dépendre de l’outil de messagerie Outlook de Microsoft.

Donc cela est important si vous ne disposez pas de la suite bureautique Office ou simplement d’Outlook.

Mais avant de commencer à utiliser l’objet CDO il va falloir créer une référence à celui-ci au sein d’Outlook.

Pour ce faire il suffit d’accéder au menu Tools->References (ou Outils->Références) dans la fenêtre de développement VBA.

Une fois que nous avons ajouté cette référence nous allons pouvoir écrire notre méthode de création d’un mail en utilisant l’objet CDO.

Pour commencer il suffit de créer une instance de l’objet CDO.Message pour ce faire nous créons une nouvelle variable oCdo :

Dim oCdo As Object
Set oCdo = CreateObject(“CDO.Message”)

Un travail supplémentaire est à notre charge dans cette méthode nous devons paramétrer les propriétés de l’objet pour que l’on puisse se connecter à un service de messagerie.

La plupart du temps il s’agira d’un serveur SMTP que l’on a configuré dans notre entreprise ou d’un serveur classique comme Gmail.

C’est ce dernier type de serveur SMTP que nous allons configurer dans ce tutoriel.

Voici les paramètres nécessaires pour se connecter à serveur SMTP Gmail :

• Nom du serveur : smtp.gmail.com ;
• Port SMTP : 465 ;
• Utilisation du protocole SSL/TLS ;
• Connexion par authentification ;
• User : adresse mail du compte de messagerie gmail ;
• Mot de passe : mot de passe associé.

Nous devons donc maintenant associer ces paramètres à notre objet nouvellement créé.

Pour ce faire on utilise une boucle With … End With With comme ceci :

With oCdo

  With .Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"     ' FQDN ou IP du serveur SMTP
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 ' port utilisé
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1           ‘S’authentifier
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = "true"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "account@gmail.com"              ' Si autentifié le nom d'utilisateur
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "***************"              ' Si authentifié le mot de passe
    .Update

Une fois que nous avons paramétré ces propriétés nous pouvons passer à la configuration du mail à envoyer.

Nous devons donc configurer comme précédemment le sujet, le corps du mail à partir des valeurs contenues dans les variables que nous avons créées.

Nous devons utiliser à cet effet les propriétés From, Subject, To et TextBody de l’instance de l’objet CDO que nous venons de créer :

       .From = "test@gmail.com"                                                            ' adresse de l'expéditeur
      .Subject = Subject
      .To = DstList ' adresse du destinataire
      .TextBody = Body
 

A savoir que la propriété TextBody ne prend pas en compte le langage HTML donc si vous insérer des balises à l’intérieur elles apparaîtront littéralement dans le contenu.

Pour pouvoir envoyer le mail il suffit d’utiliser la méthode Send.

    .Send
End With

Si vous voulez ajouté en pièce jointe par exemple un fichier qui se trouve sur votre disque dur à l’emplacement suivant C:\Test.txt, il faut utiliser la méthode AddAttachment comme ceci :

     .AddAttachment("C:\Test.txt")

Une bonne pratique est de libérer l’espace mémoire occupé par notre objet lorsqu’il ne nous sert plus.

Pour réaliser cette opération on utilise la ligne de commande suivante :

Set oCdo = Nothing

IV Conclusion

Nous avons vu à travers ce tutoriel deux méthodes nous permettant de créer un template d'email à partir de données issues des cellules d’un classeur Excel.

Nous avons vu une méthode nous permettant d’interagir avec l’application Outlook de la suite bureautique Office. Et une deuxième permettant de créer et d’envoyer un mail en passant par un serveur SMTP, nous avons choisi le service Gmail dans ce tutoriel mais ces informations sont tout à fait valables pour tout autre type de serveur SMTP. Cette deuxième méthode nosu permet de nous affranchir de l'application Outlook et permet d'envoyer directement l'email sans vérification des adresses email.