VBA: Envoyer un email avec Excel (via Outlook) 28


Vous avez un listing de clients en Excel et vous voulez l'utiliser pour envoyer des emails personnalisés? Vous avez besoin d'avertir quelqu'un par email à la fin de votre programme? Alors lisez ce qui suit car en VBA, tout est possible (ou presque)!

Je vous propose ici le code VBA qui vous permettra d'envoyer un email avec Excel en passant (en arrière plan et sans l'intervention humaine) par Outlook. Il s'agit une fonctionnalité très pratique. Le code qui suit est conçu de manière à ce que même un débutant en VBA puisse l'implementer et l'utiliser facilement.

Les utilisateurs  plus avancées de VBA n'auront aucun problème d'insérer ce code dans une boucle et créer ainsi un puissant outil de publipostage – qui, de plus est, contrairement à d'autres outils, tout à fait gratuit.

Comment envoyer un email dans Excel (Access / Word,…) avec VBA?

Le code VBA qui suit vous permet donc d'envoyer un ou plusieurs emails à partir d'Excel. Ce code contient deux procédures. Une qui envoie les emails proprement dit (procédure EnvoyerEmail) et une autre qui gère l'application Outlook (pour faire en sorte que Outlook soit prêt à envoyer les emails – même si Outlook lui même n'est pas ouvert) – procédure PreparerOutlook.

Le code est écrit de manière à ce qu'il soit lisible et compréhensible. En gros, vous devez fournir à la procédure EnvoyerEmail les arguments suivants (sous forme de texte):

  • le sujet de l'email
  • le contenu de l'email
  • l'adresse email du destinataire
  • en option, le chemin complet du fichier qui doit être attaché à l'email en tant que pièce jointe

Le code va fonctionner directement, il suffit de le copier/coller. La seule exigence est d'avoir Microsoft Outlook installé et ajouter à votre projet la Référence à Outlook (voir la section suivante).

Il vous est donc très facile d'ajouter ce code à votre Projet et commencer à envoyer des emails.

Ajouter la Référence Microsoft Outlook X.0 Object Library

Ceci est une étape importante car si la Référence n'est pas ajouté, le code ci-dessous ne fonctionner pas.

Si vous connaissez la procédure d'ajout d'une Référence VBA
Si vous ne savez pas comment ajouter une Référence VBA

 

Code VBA complet pour envoyer des emails dans Excel

Ci-dessous se trouve donc le code VBA complet dont vous aurez besoin. Il suffit de le copier/coller dans un Module.

Sub EnvoyerEmail(ByVal Sujet As String, ByVal Destinataire As String, ByVal ContenuEmail As String, Optional ByVal PieceJointe As String)
'par Excel-Malin.com ( http://excel-malin.com )

On Error GoTo EnvoyerEmailErreur

'définition des variables
Dim oOutlook As Outlook.Application
Dim WasOutlookOpen As Boolean
Dim oMailItem As Outlook.MailItem
Dim Body As Variant

Body = ContenuEmail

    'vérification si le Contenu du mail n'est pas vide. Si oui, email n'est pas envoyé. Si vous voulez pouvoir envoyer les email vides, mettez en commentaire les 4 lignes de code qui suivent.
    If (Body = False) Then
        MsgBox "Mail non envoyé car vide", vbOKOnly, "Message"
        Exit Sub
       End If
    
    'préparer Outlook
    PreparerOutlook oOutlook
    Set oMailItem = oOutlook.CreateItem(0)
    
    'création de l'email
    With oMailItem
        .To = Destinataire
        .Subject = Sujet
        
        'CHOIX DU FORMAT
        '----------------------
        'email formaté comme texte
            .BodyFormat = olFormatRichText
            .Body = Body
            
            'OU
            
        'email formaté comme HTML
            '.BodyFormat = olFormatHTML
            '.HTMLBody = "<html><p>" & Body & "</p></html>"
        '----------------------
        
        If PieceJointe <> "" Then .Attachments.Add PieceJointe

       .Display   '<- affiche l'email (si vous ne voulez pas l'afficher, mettez cette ligne en commentaire)
       .Save      '<- sauvegarde l'email avant l'envoi (pour ne pas le sauvegarder, mettez cette ligne en commentaire)
       .Send      '<- envoie l'email (si vous voulez seulement préparer l'email et l'envoyer manuellement, mettez cette ligne en commentaire)
    End With
    
   'nettoyage...
    If (Not (oMailItem Is Nothing)) Then Set oMailItem = Nothing
    If (Not (oOutlook Is Nothing)) Then Set oOutlook = Nothing
    
   Exit Sub

EnvoyerEmailErreur:
    If (Not (oMailItem Is Nothing)) Then Set oMailItem = Nothing
    If (Not (oOutlook Is Nothing)) Then Set oOutlook = Nothing
  
    MsgBox "Le mail n'a pas pu être envoyé...", vbCritical, "Erreur"
End Sub

Private Sub PreparerOutlook(ByRef oOutlook As Object)
'par Excel-Malin.com ( http://excel-malin.com )

'------------------------------------------------------------------------------------------------
'Ce code vérifie si Outlook est prêt à envoyer des emails... Et s'il ne l'est pas, il le prépare.
'------------------------------------------------------------------------------------------------
On Error GoTo PreparerOutlookErreur


On Error Resume Next
    'vérification si Outlook est ouvert
    Set oOutlook = GetObject(, "Outlook.Application")
    
    If (Err.Number <> 0) Then 'si Outlook n'est pas ouvert, une instance est ouverte
        Err.Clear
        Set oOutlook = CreateObject("Outlook.Application")
    Else    'si Outlook est ouvert, l'instance existante est utilisée
        Set oOutlook = GetObject("Outlook.Application")
        oOutlook.Visible = True
    End If
    Exit Sub

PreparerOutlookErreur:
    MsgBox "Une erreur est survenue lors de l'exécution de PreparerOutlook()..."
End Sub

 

Pour que le code fonctionne correctement, vous devez copier/coller son entièreté dans un Module de votre Projet VBA.

Choix du format de l'email: texte ou HTML

Ce code VBA vous permet de choisir quel format de l'email vous voulez utiliser: texte ou HTML (formatage comme une page web). Selon votre choix, vous devez mettre en commentaire une de ces deux possibilités.

        'CHOIX DU FORMAT
        '----------------------
        'email formaté comme texte
            .BodyFormat = olFormatRichText
            .Body = Body
            
            'OU
            
        'email formaté comme HTML
            '.BodyFormat = olFormatHTML
            '.HTMLBody = "<html><p>" & Body & "</p></html>"
        '----------------------

 

Pour le format texte, vous pouvez laisser le code initial inchangé (lignes 32 et 33 actives, lignes 38 et 39 en commentaire).

Pour le format HTML, faites le contraire – mettez les lignes 32 et 33 en commentaire et rendez les lignes 38 et 39 actives.

Quelle est la différence? En gros, le format HTML vous permet d'utiliser les balises & le code HTML pour formater le contenu de votre email.

Vous pouvez voir la différence entre ces deux formats dans la section suivante – les exemples d'utilisation.

Exemples d'utilisation du code VBA pour envoyer les emails depuis Excel

Et voici les exemples de l'utilisation du code précédent. Il suffit d'appeler la procédure EnvoyerEmail avec les arguments nécessaires – c'est à dire "Sujet", "Destinataire", "Contenu" et éventuellement "Pièce jointe".

Le code qui suit envoie un email sans pièce jointe et un autre avec une pièce jointe.

Sub TestEnvoiEmail()
'par Excel-Malin.com ( http://excel-malin.com )

    'test envoi de l'email sans pièce jointe
        Call EnvoyerEmail("Test email 1", "contact@test.fr", "Ceci est un test...")
     
     'test envoi de l'email avec pièce jointe
        Call EnvoyerEmail("Test email 2", "contact@test.fr", "Ceci est un test avec pièce jointe...", "C:\MonDossier\MonFichier.pdf")
 
 MsgBox "Test terminé..."

End Sub

Exemple d'utilisation avec des variables

Vous pouvez également fournir les arguments nécessaires via des variables. Attention, vous devez avoir déclaré ces variables pour pouvoir les transférer une procédure (Sub) à une autre (ici de la procédure TestEnvoiEmail_Variables vers EnoyerEmail). Ceci est particulièrement pratique si vous voulez envoyer plusieurs emails. Il suffit alors d'utiliser une boucle au sein de laquelle on attribue les valeurs aux variables et ensuite, l'email est envoyé. La boucle recommence une autre itération en chargeant les nouvelles valeurs dans les variables. Et ainsi de suite…

 

Sub TestEnvoiEmail_Variables()
'par Excel-Malin.com ( http://excel-malin.com )

'définition des variables
Dim MonSujet As String
Dim MonDestinataire As String
Dim MonContenu As String
Dim MaPieceJointe As String

'attribution des valeurs aux variables
MonSujet = "Test d'email d'Excel avec variables"
MonDestinataire = "jean.dupont@dupont.fr"
MonContenu = "Bonjour Jean, voici un petit message de test... Avec un fichier en plus."
MaPieceJointe = "C:\Test\MonFichierExcel.xlsx"
    
    'test envoi de l'email
        Call EnvoyerEmail(MonSujet, MonDestinataire, MonContenu, MaPieceJointe)

MsgBox "Test terminé..."
End Sub

 

Exemple de code pour envoyer en email en format HTML

Comme mentionné plus haut, vous pouvez créer et envoyer des emails en format HTML. Ainsi, Outlook envoie l'email comme si c'était une page web. Cela a pour l'avantage la possibilité de formater le message à votre guise. A condition de maîtriser (au moins) un peu le langage HTML (remarquez les balises HTML dans l'exemple).

Sub TestEnvoiEmail_FormatHTML()
'par Excel-Malin.com ( http://excel-malin.com )

    'test envoi de l'email sans pièce jointe
        Call EnvoyerEmail("Test email HTML 1", "contact@test.fr", "Ceci est un <strong>test</strong><br>avec les formats <span style=""color: #f55; font-weight: bold;"">HTML</span>...")

MsgBox "Test terminé..."
End Sub

 

Remarques

  • Ce code est indépendant et peut être utilisé dans les différentes application de Microsoft Office (Excel, Access, Word,…). Mais n'oubliez pas d'ajouter la Référence à Microsoft Outlook X.0 Object Library comme mentionné plus haut.
  • Il est possible d'aller encore plus en profondeur: ajouter des CC, des CCI, ajouter plusieurs pièces jointes en même temps etc. Par souci de lisibilité (surtout pour les utilisateurs moins avancés), j'ai utilisé dans ce code les options de base (et nécessaires). N'hésitez pas à laisser un commentaire si vous avez envie d'en savoir plus sur les autres fonctionnalités possibles…
  • Selon vos besoins, vous pouvez envoyer directement le mail ou le garder prêt et l'envoyer manuellement. L'explication comment faire se trouve dans les commentaires dans le code.

67 0




Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

28 commentaires sur “VBA: Envoyer un email avec Excel (via Outlook)

  • Guillaume VAILLANT

    Bonjour,

    J'aimerai envoyer dans mon message non pas une string simplement comme dans l'exemple mais un copier/coller de plusieurs cellules (Range("A1:B6) par exemple).

    Merci de m'indiquer comment faire svp.

    Cdt

    Guillaume

    • Guillaume VAILLANT

      Bonjour,

      Petite relance svp. Est-ce possible ou pas de réaliser cette opération svp ?

      PS : Beau travail en tout cas, marche du 1er coup ;o)

      • excel-malin.com Auteur du billet

        Bonjour Guillaume,
        je n'étais pas dispo plus tôt…

        Pour votre question:
        un simple copier/coller n'est pas possible. Je vois deux possibilités:
        1) créer une image à partir de la plage souhaité (voir le code ici) et ensuite l'ajouter comme pièce jointe (voir le code sur cette page)
        2) créer un tableau HTML avec les valeurs qui se trouvent dans la plage (désavantage, le formatage ne sera pas copié). Pour cela, le code rassemblerait à quelque chose comme ceci:


        HTMLtableau = ""
        Plage = "A1:B6"
        nbColonnes = Range(Plage).Columns.Count
        nbLignes = Range(Plage).Rows.Count
        
        For Ligne = 1 To nbLignes
            HTMLtableau = HTMLtableau & ""
                For Colonne = 1 To nbColonnes
                
                    HTMLtableau = HTMLtableau & ""
                
                Next Colonne
            HTMLtableau = HTMLtableau & ""
        Next Ligne
        
        HTMLtableau = HTMLtableau & "
        " & Range(Plage).Cells(Ligne, Colonne).Value & "
        "

        Vous ajoutez le résultat à l'endroit voulu dans le body et voilà…
        Après, il y a moyen de jouer avec les styles etc. pour formater le tableau.

        J'espère que cela vous aide!
        Bàv, Martin

        • Guillaume VAILLANT

          Bonjour et merci beaucoup pour cette réponse.

          La 1ère solution ne convient pas car je veux copier/coller plusieurs groupes de cellules les unes en dessous des autres afin de constituer un mail récapitulatif et visuel (pas de PJ).

          Pour la seconde effectivement on perd le formatage et ça devient quasi-illisible.

          Pas possible de faire un mix des 2 ? Pas possible de copier les images dans le body du mail ?

          Cdt

          Guillaume

          • excel-malin.com Auteur du billet

            J'ai un peu cherché et j'ai une bonne nouvelle – c'est possible!
            Donc:
            1) vous créez l'image JPG de la plage via le code dans mon précédent commentaire
            2) vous ajoutez cette image comme pièce jointe à l'email (avec le code sur cette page)
            3) il faut maintenant placer cette image dans le body HTML du mail. Pour cela, il suffit d'ajouter ce code avant le .Send


            .HTMLBody = .HTMLBody & "
            Mon image:

            "

            Vous pouvez le modifier bien sûr. Et vous pouvez le formater et ajouter d'autres tags HTML.
            Exemple:

             .HTMLBody = .HTMLBody & ""

            pour spécifier ses dimensions.

            Attention donc au paramètre SRC –> il a le format cid: (ça, c'est par défaut pour dire que l'image se trouve dans des pièces jointes) & NomDuFichier.jpg

            Avec ce code-ci, vous pouvez même facilement ajouter plusieurs images au différents endroits dans un seul email. Ce qui devrait résoudre votre problème.

            Bàv, Martin

          • Guillaume VAILLANT

            Bonjour et merci pour tout !

            N'ayant pas besoin des images en PJ, j'ai préféré utiliser la commande suivante pour incorporation de l'image dans le mail :

            MonChemin = "C:\…\Temp\MonImageExcel.jpg"
            HTMLtableau = ""

            Bon j'ai encore du boulot pour que le mail puisse partir en automatique avec les données que je souhaite mais la base est là.

            Cdt

            Guillaume

          • Guillaume VAILLANT

            Bonjour,

            J'ai vraiment bien avancé mais me reste 2 dernières petites questions 🙂

            J'essaye de changer la police en vain. Par défaut, j'ai une police Time New Roman en 12 que j'aimerai diminuer.

            J'ai tenté de modifier le code original comme ceci :
            MonStyle = "10pt"
            'email formaté comme HTML
            .BodyFormat = olFormatHTML
            .HTMLBody = "" & "" & Body & "" & ""

            Ou encore ici :

            MonContenu = "" & "Bonjour, blablabla" & ""

            Mais rien ne change, toujours Time New Roman en 12.

            L'autre question est relative à la création du mail. Est-ce possible de mettre des personnes en copie ? Actuellement on ne peut avec le code que mettre des personnes POUR.

            Merci par avance pour votre aide.

            Cdt

            Guillaume

          • excel-malin.com Auteur du billet

            Bonsoir,
            pour la copie, c'est facile:
            tout comme .To pour le destinataire, vous pouvez utiliser .CC pour les copies et & .BCC pour les copies "invisibles":
            .To = "destinataire.principal@hotmail.com"
            .CC = "abc@def.fr"
            .BCC = "xyz@def.fr"


            Pour la taille de texte, j'essayerai de placer un HTML tag DIV tout au début du Body (avec fermeture tout à la fin). Dans ce tag, vous pouvez ajouter le style.
            Par exemple:

            .HTMLBody = "<div style="font-size: 60%;">" & Body & "</div>"

            Bien à vous, Martin

  • Guillaume VAILLANT

    Aie ça passe pas bien… Je ne sais mettre comme vous le code en visual.

    En gros j'ai mis les balises FONT=MonStyle (avec MonStyle = "10pt") mais ça ne fonctionne pas.

  • Cous

    Bonjour,

    Le tuto est très bien fait. Je ne sais pas comment je me débrouille, mais une fois le code copié dans un module, je ne sais pas comment l’exécuter. J'ai créer un bouton de formulaire et cliquer sur affecter une macro, mais celle ci n'apparait pas dans la liste.

    Pourant je la vois bien dans VB. Mon fichier Excel est bien en .xlsm.

    Merci de votre aide.

  • Nherve

    Bonjour,

    Tout est très bien expliqué et va me faire gagner beaucoup de temps dans mon travail.
    J'ai cependant un petit problème. J'ai deux adresses mail dans outlook.
    Comment définir l'adresse qui servira a l'envoie?

    Cordialement

  • geo

    Bonjour,

    Merci beaucoup pour cet article, la macro fonctionne parfaitement et j'ai pu créer une boucle d'envoi.
    J'ai un petit soucis cependant : je n'arrive pas à afficher ma signature avant l'envoi du mail.
    En cherchant j'ai trouvé le super site de ron de bruin qui parle de ce sujet, mais je ne comprends pas comment l'adapter au code que vous nous proposez.

    Pouvez vous m'indiquer comment l'insérer ?

    Merci de votre aide !

    • geo

      Finalement j'ai fini pas trouver ! il fallait rajouter un ".display" supplémentaire juste après la commande "with oMailItem".

      • Guillaume VAILLANT

        Bonjour Geo,

        Peux-tu m'en dire sur ta solution de rajouter un .Display stp ?
        J'ai une signature enregistrée par défaut mais elle ne se met pas.

        FAut-il préciser un nom et/ou un chemin pour aller la chercher ?

        Merci par avance

        Guillaume

        • geo

          Bonjour Guillaume,

          Pour être plus concret tu trouveras ci-dessous l'extrait du code donné dans cet article que j'ai modifié pour afficher la signature.
          Il faut rajouter un ".display" au début de cette séquence et modifier la partie HTLM par ".HTMLBody = Body & "" & .HTMLBody"

          Le code :
          'création de l'email
          With oMailItem
          .Display
          .To = Destinataire
          .Subject = Sujet

          'CHOIX DU FORMAT
          '———————-
          'email formaté comme texte
          '.BodyFormat = olFormatRichText
          '.Body = Body

          'OU

          'email formaté comme HTML
          '.BodyFormat = olFormatHTML
          .HTMLBody = Body & "" & .HTMLBody
          '———————-

          If PieceJointe "" Then .Attachments.Add PieceJointe

          .Display '<- affiche l'email (si vous ne voulez pas l'afficher, mettez cette ligne en commentaire)

    • excel-malin.com Auteur du billet

      Ce serait mieux de faire une procédure "spécifique" plutôt que de modifier cette fonction générale.

      En fait, dans le code, il suffit de répéter:
      .Attachments.Add "C:\monfichier1.pdf" autant de fois que nécessaire.
      Donc vous pourriez avoir:

       With oMailItem
              .To = "jean.dupont@france.fr"
              .Subject = "Mon sujet"
              
                  .BodyFormat = olFormatRichText
                  .Body = Body
                  .Attachments.Add "C:\monfichier1.pdf"
                  .Attachments.Add "C:\monfichier2.pdf"
                  .Attachments.Add "C:\monfichier3.pdf"
                  .Attachments.Add "C:\monfichier4.pdf"
      
             .Send     
          End With
  • Soukayna

    Bonjour Malin,

    Félicitation pour la qualité du travail, c'est bien expliqué 🙂
    ma question est la suivante : j'ai créé une macro qui génère un un fichier Word a partir d'un userform Outlook. Je souhaite envoyer un mail personnalisé avec pièce jointe mon fichier Word (si moyen de le changer en PDF) généré.

    Merci par avance de votre retour 🙂

    • excel-malin.com Auteur du billet

      Bonjour Soukayna,
      1) pour ce qui concerne le fichier PDF – normalement, dans Office, il est possible de sauvegarder un fichier en tant que PDF. Cela doit se trouver au niveau de ".SaveAs". Vous pouvez regarder ici:
      http://www.outlookcode.com/article.aspx?id=34

      2) pour joindre le fichier, vous pouvez utiliser le code qui se trouve plus haut. Il vous suffit de connaître le chemin complet (dossier + nom) du fichier à envoyer et l'ajouter comme pièce jointe grâce au code .Attachments.Add où vous pouvez utiliser le chmin du fichier comme variable:

      PieceJointe = "C:\MonDossier\MonFichier.pdf"
      oMailItem.Attachments.Add PieceJointe

      ou bien comme un texte directement encodé
      oMailItem.Attachments.Add "C:\MonDossier\MonFichier.pdf"

      cela dépend de vos besoins…

      J'espère que cela répond à votre question…
      Bien à vous,
      Martin

  • Geoffray

    Bonjour,

    Merci pour ce Tuto vraiment Top. Tout marche tip-top enfin presque.
    Le gros souci c'est que l'adresse du destinataire n'arrive pas au bon format dans outlook et du coup, il ne reconnait pas l'adresse et me plante l'envoi. enfin rien n'arrive.

    Une idée?

    Merci d'avance.

    ——————————–

    Ah pardon, le problème ne vient pas de l'adresse du destinataire mais de l'expéditeur
    j'ai deux compte sur mon poste (un local et une adresse .fr) malgré que l'adresse.fr soit par défaut c'est le local qui arrive.
    J'ai spécifié avec un .SentOnBehalfOfName mais ça n'a pas l'air d'être top top…

    ——————————–

    Bon pardon du dérangement, j'ai trouver!

    Si besoin : il faut modifier dans outlook et paramétre du compte "les fichiers de données "par défaut et mettre le bon compte!

    Merci pour le reste au top!!

  • P_Fabre

    Bonjour merci pour ce super tuto 😉

    J'ai tout qui fonctionne sauf une chose x)

    Les mails restent dans la boite d'envoie au lieux d’être envoyé :/

    Comment je résous ce soucis ?