Envoyer des e-mails en masse avec Excel et Outlook

(Last Updated On: 26 avril 2020)

Dans cet article, nous allons voir ensemble comment utiliser conjointement Excel VBA et Microsoft Outlook pour envoyer automatiquement des e-mails à une liste de contacts avec un document en pièce jointe.

Scénario

Nous souhaitons envoyer par e-mail à chacun de nos collaborateurs un document personnel comme une fiche de salaire.  Nous allons donc devoir générer un e-mail pour chaque contact avec une pièce jointe individuelle.

Nous avons pour cela au préalable créé une liste de contacts contenant le nom de la personne, son adresse e-mail, ainsi que le chemin d’accès au document à envoyer. Pour simplifier la tâche, tous les documents sont localisés dans un même dossier.

Création du fichier Excel

  1. Ouvrir un nouveau classeur Excel
  2. Créer une table de contacts comme dans l’exemple ci-dessous (plage B15:E25) :
    envoi-email-excel-outlook
  3. En cellule C6, entrer l’objet du mail
  4. En cellule C8, entrer le texte du message
  5. Dans la tableau, renseignez pour chaque individu les colonnes ID (identifiant unique), Prénom Nom, Adresse e-mail et Chemin d’accès au document à joindre
  6. Enregistrer le classeur

Création de la macro VBA

  1. Ouvrir l’éditeur VBA (raccourci Alt+F11)
  2. Insérer un module (Lire Comment créer une macro)
  3. Coller le code suivant dans le module :
    Option Explicit
    Private OL_App As Object
    Private OL_Mail As Object
    Private sSubject As String, sBody As String
    
    Sub SendDocuments()
    ' Generate e-mails to be sent to a list of mail recipients, with a customized attachment and message for each person
    
    Dim i As Long
    Dim tabContactNames As Variant, tabContactEmails As Variant, tabFNames As Variant
    
    ' Init
    Application.ScreenUpdating = False
    ' Open Outlook
    On Error Resume Next
    Set OL_App = GetObject(, "Outlook.Application")
    If OL_App Is Nothing Then
    Set OL_App = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    ' Read E-mail parameters
    sSubject = Range("C6").Value
    sBody = Range("C8").Value
    ' Read Contact list
    tabContactNames = Range("C16:C25").Value
    tabContactEmails = Range("D16:D25").Value
    tabFNames = Range("E16:E25").Value
    ' Generate e-mails
    For i = 1 To UBound(tabContactNames, 1)
    If tabContactNames(i, 1) <> vbNullString Then
    Call CreateNewMessage(tabContactNames(i, 1), tabContactEmails(i, 1), tabFNames(i, 1))
    End If
    Next i
    
    MsgBox "The process has been entirely completed."
    
    Set OL_App = Nothing
    Set OL_Mail = Nothing
    Application.ScreenUpdating = True
    
    End Sub
    

    Puis copier la deuxième macro dans le module :

    Private Sub CreateNewMessage(strContactName, strContactTo, strFName)
    ' Create a new message with the following inputs :
    
    Set OL_Mail = OL_App.CreateItem(0)
    With OL_Mail
    
     .To = strContactTo
     '.CC = "alias1@domain1.com"
     '.BCC = "alias2@domain1.com"
     
     .Subject = sSubject
     .Body = sBody
     .BodyFormat = 1 'Format : 0=undetermined; 1=plain text; 2= HTML; 3=rich text
     .Importance = 2 'Importance : 0=low; 1=normal; 2= high
     .Sensitivity = 3 'Confidentiality : 0=normal; 1=personal; 2=private; 3=confidential
     .Attachments.Add (strFName)
     
    ' Display or send the message
     .Display
     '.Send
    End With
    
    Set OL_Mail = Nothing
    End Sub
    
  4. Enregistrer le classeur
  5. Lancer la macro SendDocuments (Alt+F8 puis Exécuter)

Par défaut, les e-mails seront générés et affichés à l’écran. Pour les envoyer directement, supprimer l’instruction .Display de la fonction CreateNewMessage, et enlever l’apostrophe devant l’instruction .Send

Et voilà ! Vous disposez désormais d’un outil simple d’envoi d’e-mails en masse.

N’hésitez pas à me faire part de vos commentaires ci-dessous, idées d’amélioration, difficultés rencontrées, etc…

📥Télécharger le classeur

Téléchargez le classeur Excel avec les macro VBA et des options supplémentaires intéressantes! (Personnalisation des messages, planification d’envoi des e-mails, mode vérification des adresses e-mails, etc…) en cliquant sur le bouton ci-dessous :

Cliquez-ici pour télécharger l’utilitaire (17€)

 

 

Enregistrer

Enregistrer

Enregistrer

Enregistrer

Enregistrer

Enregistrer

Enregistrer

Souscrivez à ma newsletter :

11 commentaires

  1. Gilles Clermont dit

    Merci! Malgré 30 ans d’utilisation approfondie d’excel, je n’arrivais pas à résoudre cette erreur 429 causée par le passage de Microsoft Office Object Library de 15.0 à 16.0 …

  2. Margaux dit

    Bonjour,
    Je ne suis pas une pro en macro c’est le début pour moi mais votre macro m’aide beaucoup.

    J’aimerais juste améliorer et pouvoir avoir une colonne Email pour mettre en copie une liste de contact comme ce que vous avez fait en colonne D mais que ça me mette en copie les contacts.

    Merci

    • Lionel dit

      Bonjour Margaux,

      Merci pour votre message.

      Vous pouvez modifier le code VBA de la manière suivante pour envoyer l’email à une liste de contacts en copie (CC) :

      Option Explicit
      Private OL_App As Object
      Private OL_Mail As Object
      Private sSubject As String, sBody As String

      Sub SendDocuments()
      ‘ Generate e-mails to be sent to a list of mail recipients, with a customized attachment and message for each person

      Dim i As Long
      Dim tabContactNames As Variant, tabContactEmails As Variant, tabCCEmails As Variant, tabFNames As Variant

      ‘ Init
      Application.ScreenUpdating = False
      ‘ Open Outlook
      On Error Resume Next
      Set OL_App = GetObject(, « Outlook.Application »)
      If OL_App Is Nothing Then
      Set OL_App = CreateObject(« Outlook.Application »)
      End If
      On Error GoTo 0
      ‘ Read E-mail parameters
      sSubject = Range(« C6 »).Value
      sBody = Range(« C8 »).Value
      ‘ Read Contact list
      tabContactNames = Range(« C16:C25 »).Value
      tabContactEmails = Range(« D16:D25 »).Value
      tabCCEmails = Range(« E16:E25 »).Value
      tabFNames = Range(« F16:F25 »).Value

      ‘ Generate e-mails
      For i = 1 To UBound(tabContactNames, 1)
      If tabContactNames(i, 1) vbNullString Then
      Call CreateNewMessage(tabContactNames(i, 1), tabContactEmails(i, 1), tabCCEmails(i, 1), tabFNames(i, 1))
      End If
      Next i

      MsgBox « The process has been entirely completed. »

      Set OL_App = Nothing
      Set OL_Mail = Nothing
      Application.ScreenUpdating = True

      End Sub

      Private Sub CreateNewMessage(strContactName, strContactTo, strListCC, strFName)
      ‘ Create a new message with the following inputs :

      Set OL_Mail = OL_App.CreateItem(0)
      With OL_Mail

      .To = strContactTo
      .CC = strListCC
      ‘.BCC = « alias2@domain1.com »

      .Subject = sSubject
      .Body = sBody
      .BodyFormat = 1 ‘Format : 0=undetermined; 1=plain text; 2= HTML; 3=rich text
      .Importance = 2 ‘Importance : 0=low; 1=normal; 2= high
      .Sensitivity = 3 ‘Confidentiality : 0=normal; 1=personal; 2=private; 3=confidential
      .Attachments.Add (strFName)

      ‘ Display or send the message
      .Display
      ‘.Send
      End With

      Set OL_Mail = Nothing
      End Sub

      Ensuite dans Excel, insérez une colonne après la colonne D et renseignez les adresses en copie en les séparant par un « ; » comme ceci : « personne1@email.com;personne2@email.com ».

      Voilà, ça devrait fonctionner !

      Lionel

      • Margaux dit

        Merci Beaucoup Lionel ça m’aide beaucoup.

        J’aurais besoin d’une dernière aide j’aimerais avoir dans cette macro toujours la possibilité de dire « si il trouve pas de fichier correspondant il n’envoi pas le mail sur les lignes concernées mais qu’il envoi quand même aux autres lignes où il trouve un fichier correspondant »

        Merci d’avance

  3. benoit dit

    bonjour,
    merci pour ce partage.
    J’utilise votre macro qui va très bien. En revanche l’étiquette de confidentialité C1 interne se met systématiquement, je voudrais pouvoir la changer ou carrément l’enlever.

    Je n’arrive pas à trouver l’astuce.

    merci pour votre aide

    • Lionel dit

      Bonjour,
      Les étiquettes de niveau de confidentialité sont gérées au niveau de votre organisation, je ne sais pas si vous pourrez l’enlever facilement, mais il y a une option qui permet de gérer les étiquettes dans le menu Accueil -> Niveau de confidentialité.

      Lionel

  4. kent dit

    Bonjour,

    Merci pour cette macro elle fonctionne à merveille
    J’ai rajouté la sélection du compte outlook car j’ai plusieurs adresses mail
    Cependant je bute sur un dernier point :
    Comment garder la signature par défaut du compte afin qu’elle apparaisse en fin de mail ?

    Merci pour votre aide

    • Lionel dit

      Bonjour, je ne sais pas s’il existe une propriété signature pour l’object message dans Outlook, à regarder. Sinon, c’est une limitation de l’automatisation par une macro VBA.

  5. LEFORT dit

    Bonjour, que cette macro est géniale! Par contre, je ne sais pas comment faire pour joindre 2 fichiers par mail? Je pensais mettre le chemin du 2ème fichier dans la colonne F et rajouter dans la macro tabFNames2(i, 1) mais ca ne fonctionne pas. Merci pour votre réponse

    • Lionel dit

      Bonjour, merci pour votre commentaire.
      Pour ajouter un deuxième fichier, il faut ajouter une ligne .Attachments.Add (strFName2) dans la fonction CreateNewMessage comme ceci :

      Private Sub CreateNewMessage(strContactName, strContactTo, strFName, strFName2)
      ‘ Create a new message with the following inputs :


      .Attachments.Add (strFName)
      .Attachments.Add (strFName2)

      Ensuite, vous appelez la fonction dans la macro principale comme ceci :
      tabFNames = Range(« E16:F25« ).Value // ajout d’une plage à 2 colonnes
      Call CreateNewMessage(tabContactNames(i, 1), tabContactEmails(i, 1), tabFNames(i, 1), tabFNames(i, 2) ) // ajout du 2ème fichier en argument

      Je n’ai pas testé mais ça devrait fonctionner 😉

      Lionel

Répondre à Margaux Annuler la réponse

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *