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
- Ouvrir un nouveau classeur Excel
- Créer une table de contacts comme dans l’exemple ci-dessous (plage B15:E25) :
- En cellule C6, entrer l’objet du mail
- En cellule C8, entrer le texte du message
- 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
- Enregistrer le classeur
Création de la macro VBA
- Ouvrir l’éditeur VBA (raccourci Alt+F11)
- Insérer un module (Lire Comment créer une macro)
- 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 SubPuis 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 - Enregistrer le classeur
- 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…
Vous aimerez peut-être :
Transformation de type dynamique et en masse dans Excel, Power Query Langage M, Power BI
Voici 2 méthodes pour supprimer les accents dans Excel avec une macro VBA et une requête Power Query
Collectez des données dans Excel Online avec Microsoft Forms | Sondages en utilisant Office 365
Synthèse de données à l’aide d’histogrammes et de diagrammes de Pareto – PARTIE 1
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 …
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
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
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
Bonjour,
Super macro !
Comment peut-on choisir la boite mail depuis laquelle part l’email ?
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
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
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
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.
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
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
Bonsoit,
Et merci pour ce code ça marche parfaitement,
Mais comment je peux envoyer les messages en masse avec vba Excel sur whatsapp,
Il y a t-il des moyens plus spécifiques?
Bonjour,
c’est la première fois que je trouve un code VBA qui marche, je suis très grand débutant.
Sans vouloir abuser, est-il possible de mettre en pièce jointe non pas un fichier mais un onglet spécifique de mon classeur ?
J’ai un classeur avec un onglet par collaborateur dans lesquels je mets à jour leur résultats. J’aimerais leur envoyer automatiquement leur onglet par mail.
Merci pour votre retour et bravo pour ce partage
Bonjour, selon vous quel est le meilleur logiciel pour envoyer une newsletter à une liste de contacts sur Excel ? Merci
Bonjour, selon moi un logiciel d’emailing dédié fera toujours mieux le travail que n’importe quelle combinaison Excel + un autre outil. Vous pouvez essayer Systeme.io, c’est celui que j’utilise pour mon blog.
Ensuite, rien ne vous empêche d’exporter vos contacts dans Excel.