Email sending macro in Excel and Outlook in 4 difficulty levels

Lucas
Macro Excel VBA envoie mail Outlook(3)

4 levels to create an email-sending macro with Excel and Outlook. Automate email sending from Excel.

Summary

1. Beginner level – use this simple macro to send an Outlook email from Excel.

Do you want to automatically send an Outlook email from Excel? Follow the super simple code below:

mail_vba_debutant

2. Intermediate level – format the email (bold, underline, italics, colours, line breaks) with simple HTML.

Thanks to HTML, you can easily:

  • Format the text of your email
  • Set your text in italics, bold, underlined, in red, etc.
  • For more HTML text formatting options, check out this site – the possibilities are huge!
mail_vba_intermediaire

3. Advanced level – Outlook email with an Excel data range in the body of the email using a function

By using a (very simple) function, you can insert a table or a set of data into the body of your email.

mail_vba_avance

For this macro to work, you must add the function below in the same module:

				
					Public Function RangetoHTML(rng_mail As Range)
    Dim fso As Object 'Déclare l'objet système de fichiers
    Dim ts As Object 'Déclare l'objet flux de texte
    Dim TempFile As String 'Déclare la variable de chaîne pour le fichier temporaire
    Dim TempWB As Workbook 'Déclare le classeur temporaire
    
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Définit le chemin du fichier temporaire

    'Copie la plage et crée un nouveau classeur pour coller les données
    rng_mail.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Excel.Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
    
    'Publie la feuille dans un fichier htm
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Lit toutes les données du fichier htm dans RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
    'Ferme TempWB
    TempWB.Close savechanges:=False

    'Supprime le fichier htm que nous avons utilisé dans cette fonction
    Kill TempFile

    Set ts = Nothing 'Nettoie l'objet flux de texte
    Set fso = Nothing 'Nettoie l'objet système de fichiers
    Set TempWB = Nothing 'Nettoie le classeur temporaire

End Function

4. Advanced level 2 – Outlook email with a chart as an attachment

Add a chart as an attachment to the Outlook email.

5. Expert level – Outlook email with a table and a chart in the body of the email

Add a table and a chart in the body of the Outlook email.

mail_vba_expert

For this macro to work, you must add the function below in the same module:

				
					Function ConvertRangeToHTML(rng As Range) As String

Dim htmlTable As String 'Déclare la variable de chaîne pour le tableau HTML
Dim row As Range 'Déclare l'objet de plage pour la ligne
Dim cell As Range 'Déclare l'objet de plage pour la cellule

'Début du tableau HTML
htmlTable = "<thead><tr>"
For Each cell In rng.Rows(1).Cells
    htmlTable = htmlTable & "<th>" & cell.Value & "</th>" 'Ajoute chaque cellule de la première ligne de la plage à l'en-tête du tableau
Next cell
htmlTable = htmlTable & "</tr></thead><tbody>"

'Ajoute les lignes et les cellules au tableau
For Each row In rng.Rows
    htmlTable = htmlTable & "<tr>"
    For Each cell In row.Cells
        htmlTable = htmlTable & "<td>" & cell.Value & "</td>" 'Ajoute chaque cellule de la ligne aux données du tableau
    Next cell
    htmlTable = htmlTable & "</tr>"
Next row

'Fin du tableau HTML
htmlTable = htmlTable & "</tbody>"

ConvertRangeToHTML = htmlTable 'Renvoie le tableau HTML


End Function
Categories: Outlook, VBA

Related articles