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 :

4 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

Répondre à Gilles Clermont Annuler la réponse

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