Comment envoyer la sélection par mail en 1 clic avec VBA sur Excel

Il y a quelques semaines de cela, je vous avais proposé un petit tuto pour vous présenter six astuces pour envoyer un mail simplement avec Excel. Aujourd’hui, je vous propose un petit tutoriel simple pour aller encore plus loin et envoyer le contenu des cellules sélectionnées dans un mail, en appuyant sur un unique bouton ! Ce tutoriel est une réponse à la question posée par « M C » dans les commentaires de la vidéo Youtube « Les 3 Objets Range INDISPENSABLES de VBA »

Ensuite, je vous propose d’aller un peu loin, en ajoutant des fonctionnalités supplémentaires (insérer plusieurs destinataires, puis joindre le classeur ou la feuille de calcul en tant que pièce jointe).

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 :

Partie 2 :

1. Présentation

Pour illustrer ce tutoriel, nous allons partir du fichier que nous avons eu l’occasion de mettre en place dans le cours dédié à l’apprentissage de la fonction TRI() d’Excel :

Comme vous pouvez l’imaginer, l’objectif est alors de pouvoir envoyer simplement le contenu de ce tableau par mail en appuyant sur un unique bouton !

Pour cela, nous allons évidemment devoir nous reposer sur l’utilisation d’une macro-commande développée en VBA.

Ne prenez pas peur, aucune compétence dans le développement en VBA n’est requise pour suivre ce tutoriel, nous allons bien entendu nous attarder sur chaque point étudié de manière détaillé.

Mais si vous souhaitez en savoir davantage au sujet de ce langage de programmation, je vous invite à suivre la formation « Apprendre le VBA » disponible gratuitement sur le blog.

Tout d’abord, sachez que pour pouvoir saisir du code en VBA, il est nécessaire que le classeur soit enregistré sous le format *.xlsm qui permet de sauvegarder les macro-commandes en VBA.

Pour cela, rendez-vous dans le menu Fichier du ruban, puis sélectionnez Enregistrer Sous > Parcourir :

Après avoir recherché le dossier dans lequel enregistrer le fichier Excel, dans le menu déroulant Type, choisissez d’enregistrer le fichier en tant que « Classeur Excel (prenant en charge les macros) (*.xlsm) » :

Cela étant fait, pour lancer l’éditeur de code en VBA, il suffit de lancer Visual Basic Editor en suivant l’une des méthodes détaillées dans le chapitre « Activer VBA en affichant le menu DÉVELOPPEUR sur EXCEL ».

Ici, nous allons simplement utiliser le raccourci clavier [Alt] + [F11].

2. Envoyer le contenu d’une cellule par mail

Maintenant que le fichier est prêt à recevoir nos lignes de code en VBA, nous allons pouvoir entrer dans le vif du sujet.

Et pour commencer simplement, nous allons tout d’abord nous contenter d’envoyer le contenu de la cellule sélectionnée en tant que contenu de l’email.

Nous verrons dans une seconde partie comment procéder pour envoyer tout le contenu de la sélection dans l’email.

Pour pouvoir lancer rapidement la macro commande depuis la feuille de calcul, nous allons simplement insérer un bouton qui sera rattaché à notre macro.

Pour cela, revenons sur la feuille de calcul sur laquelle nous souhaitons l’insérer.

Ensuite, nous allons créer un objet Zone de texte (menu Insertion > Zone de texte) :

Puis nous dessinons cet objet sur la feuille de calcul :

Ensuite, nous saisissons un texte au bouton, par exemple « Envoyer par mail », et nous le mettons en forme (en utilisant simplement un style prédéfini : Menu Mise en forme, puis nous sélectionnons un style depuis le menu déroulant) :

Une fois ces opérations terminées, nous pouvons simplement effectuer un clic-droit sur le bouton, pour choisir l’option Affecter une macro.

Nous saisissons un nom que nous souhaitons donner à la macro-commande (par exemple « envoyerParMail », évidemment sans utiliser d’espace, ni d’accent), puis nous cliquons sur le bouton Nouvelle pour qu’Excel créé automatiquement une nouvelle procédure au sein du projet, laquelle sera appelée dès que nous cliquerons sur le bouton :

Excel a alors inséré la nouvelle macro dans un module :

Il suffit alors de saisir nos lignes de code entre les lignes ajoutées.

Mais avant cela, nous allons devoir activer la référence à la librairie Microsoft Outlook au sein du projet, afin de pouvoir agir directement sur l’application de gestion des mails.

Pour cela, c’est très simple, rendez-vous dans le menu Outils > Références, puis dans la liste des librairies disponibles (celles-ci étant classées par ordre alphabétiques), repérez la référence « Microsoft Outlook 16.0 Object Library » (le numéro de version va dépendre de votre version d’Office).

Une fois celle-ci activée, valider simplement en appuyant sur la touche [Entrée] du clavier :

De cette manière, nous allons maintenant pouvoir utiliser simplement des variables, objets ou encore procédure permettant de manipuler Outlook directement depuis Excel.

C’est parti pour le code VBA !

La première chose à faire, maintenant que la librairie permettant de manipuler Outlook est accessible dans le projet va être de créer un nouvel objet de gestion de l’application Outlook que nous allons appeler oOutlook

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

Ensuite, nous allons insérer un nouveau mail dans cette application en utilisant un objet dédié que nous allons appeler oMail.

La création de ce mail est possible en utilisant la méthode CreateItem(0) de 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 cela, nous allons utiliser l’instruction With de VBA qui va nous éviter d’avoir à ressaisir à chaque fois l’appel du mail :

    With oMail
    
    End With

La première chose à faire va alors être de renseigner le nom du destinataire du mail :

    With oMail
        .To = "contact@monmail.fr"

Ensuite, le sujet du mail et son contenu :

        .Subject = "Extrait du classeur "  & ThisWorkbook.Name
        .Body = ActiveCell

Comme vous pouvez le constater, nous insérons ici le nom du classeur dans le sujet du mail, et le contenu de la cellule active directement dans le corps de mail.

Pour récupérer le contenu de la cellule, nous nous contentons d’appeler celle-ci (ActiveCell), mais nous pourrions également appeler la propriété de cette cellule ActiveCell.Value :

        .Body = ActiveCell.Value

En réalité, la propriété Value étant la propriété par défaut de tous les objets Range, c’est elle qui sera appelée automatiquement en cas d’omission.

Maintenant, nous avons deux possibilités :

• Soit nous souhaitons afficher le mail ainsi créé 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 nous souhaitons envoyer directement le 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

Ensuite, pour tester la macro, il ne reste plus qu’à revenir sur la feuille de calcul pour cliquer sur le bouton que nous avons créé juste avant :

3. Envoyer le contenu de la sélection par mail

Maintenant, imaginons que nous souhaitions, non plus envoyer seulement la cellule active, mais l’ensemble des cellules sélectionnées…

Les choses vont commencer à devenir un peu plus complexe, mais pas de panique, il va simplement suffire de vous laisser guider.

Pour commencer, nous allons créer un objet oMail.GetInspector.WordEditor que nous allons appeler oObjectWord et qui sera un sous-objet de notre mail (de cette manière l’objet va être directement inséré dans le corps du mail)

Cet objet va nous permettre d’insérer un nouvel objet dans le corps du mail qui va agir comme une page Word.

        Dim oObjetWord As Object
        Set oObjetWord = .GetInspector.WordEditor

Ensuite, nous allons simplement pouvoir copier le contenu des cellules sélectionnées directement à l’intérieur de cet objet (à la place de la propriété .Body, devenue sans objet étant donné que oObjectWord est déjà inséré dans le corps du mail) :

        Selection.Copy
        oObjetWord.Range(0).Paste

Note : Le .Range(0) permet d’inclure le collage en tout début de mail

Et voilà !

Vous pouvez maintenant tester la procédure et constater que la sélection est insérée dans le mail :

4. Envoyer le mail à plusieurs destinataires

Maintenant, voyons comment partir de ce travail pour automatiser l’envoi du mail non plus à un seul utilisateur c’était le cas ici, mais directement à plusieurs destinataires, dont les adresses seront insérées directement dans une feuille de calcul Excel.

Pour cela, commençons par revenir dans le classeur Excel, et nous allons y insérer une nouvelle feuille que nous allons appeler « Destinataires » :

Dans celle-ci, nous allons simplement saisir les adresses mails des différents destinataires :

Nous commençons par saisir la première adresse mail dans la cellule A1, puis chacun des mails suivants sur la cellule du dessous, en ne laissant aucune cellule vide.

Cela étant fait, nous pouvons revenir dans le code VBA et nous allons y insérer une mini-fonction en VBA qui va permettre de récupérer la liste des e-mails correctement formattée et directement à la suite de la macro envoyerParMail :

Function listeMails As String()
 
End Function

Cette fonction va nous retourner une chaîne de caractère contenant toutes les adresses mails, séparées par un point-virgule, nous la typons donc en tant que String.

Pour commencer, nous y déclarons deux variables :

• La variable r est typée en tant que Range, c’est-à-dire que celle-ci est une cellule qui va permettre de passer en revue toutes les cellules dans lesquelles se trouvent des adresses mails,
• La variable liste est typée en tant que String, c’est-à-dire une chaîne de caractères et elle va permettre de créer le texte contenant les adresses mails

    Dim r As Range
    Dim liste As String

Nous pouvons maintenant définir que r correspond à la cellule A1 de la feuille Destinataire :

Set r = Sheets("Destinataires").[a1]

Puis stocker la valeur de cette cellule dans la variable liste :

    liste = r

Et passer à la ligne suivante en utilisant l’instruction Offset qui permet d’effectuer un décalage de cellule :

set r = r.Offset(1, 0)

À partir de maintenant, nous allons mettre en place une boucle While qui va nous permettre de répéter la même opération tant que la cellule stockée dans la variable r n’est pas vide :

    Do While r <> ""
        liste = liste & "; "  & r
        Set r = r.Offset(1, 0)
    Loop

Lorsque la boucle arrive sur une cellule vide, celle-ci s’arrête et nous pouvons retourner à la fonction listeMails la chaîne de caractères enregistrée dans la variable liste :

    listeMails = liste 

Lorsque la fonction listeMails est terminée, il ne reste plus qu’à insérer son résultat en tant que propriété To dans la macro envoyerParMail :

        .To = listeMails 

Puis de tester la macro :

Les adresses mails sont effectivement insérées correctement !

5. Envoyer le classeur en tant que pièce jointe du mail

Maintenant, découvrons une autre méthode qui va cette fois-ci nous permettre de joindre tout le classeur en tant que pièce jointe du mail.

Pour cela, nous allons commencer par dupliquer le bouton déjà inséré sur la feuille de calcul.

Pour cela, nous cliquons sur celui-ci tout en appuyant sur la touche [Ctrl] du clavier pour le sélectionner, puis nous appuyons sur les touches [Ctrl]+[D] pour le dupliquer directement :

Une fois le bouton dupliqué, nous pouvons le placer à un autre endroit, modifier le texte et éventuellement son apparence :

Ensuite, nous allons le lier avec une nouvelle macro en effectuant un clic-droit > Affecter une macro :

Nous saisissons le nom de la nouvelle macro (ici joindrePieceJointe) et nous cliquons sur créer :

La nouvelle macro est créée et insérée dans un nouveau module.

Contrairement à ce que nous pourrions penser, celle-ci sera beaucoup plus simple à mettre en place que ce que nous avons déjà eu l’occasion de découvrir dans ce tutoriel.

En effet, une seule instruction va être nécessaire pour pouvoir créer un nouveau mail et y joindre le classeur en tant que pièce jointe !

Cette instruction est la méthode SendMail de l’objet Workbook (ici ThisWorkBook) :

SendMail attend trois arguments :

• Recipients : il s’agit de l’adresse du ou des destinataires (nous utiliserons un Array ,
• Subject : le sujet du mail
• ReturnReceipt : permet de demander un accusé de lecture du mail. Par défaut, cet argument est égal à False, c’est-à-dire qu’aucun accusé n’est demandé

    ThisWorkbook.SendMail "monmail@monmail.com",  "Voici le classeur " & ThisWorkbook.Name

Il suffit maintenant de cliquer sur le bouton créé sur la feuille de calcul pour expédier le mail

Un message s’affiche alors pour demander la confirmation d’expédition du mail :

En effet, contraire à la méthode précédente, le mail sera ici directement expédié, sans que nous ne puissions effectuer de contrôle.

Il est donc important de bien valider que le contenu est tout à fait correct.

Lorsque nous sommes certains, nous pouvons évidemment cliquer sur le bouton [Accepter] pour valider le départ du mail.