Macro de envío de correo Excel Outlook en 4 niveles de dificultad

Introducción

4 niveles para crear una macro de envío de correo Excel Outlook. Automatizar el envío de correo desde Excel.

1. Nivel principiante - usa esta macro simple para enviar un correo Outlook desde Excel

¿Quieres poder enviar automáticamente un correo Outlook desde Excel? Sigue el código súper simple a continuación:

Sub envoi_mail_debutant()

Dim OutApp As Object 'Declaración del objeto aplicación Outlook
Dim OutMail As Object 'Declaración del objeto correo Outlook

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
    .To = "contact@sap-automation.com"
    .CC = "contact@sap-automation.com; youremailadress@gmail.com" 'direcciones en copia
    .BCC = Worksheets("mail").Range("B3") 'busca el valor en la celda B1 de la
                                          'hoja "mail" // direcciones en copia oculta

    .Subject = "Mail" & " a " & Date & " " & Time
              'separa dos valores con un &
              'el texto escrito 'en duro' siempre debe estar rodeado de ""
              'Date & Time son funciones que devuelven la fecha/hora de creación del correo

    .Body = "Este es un mensaje de prueba" & vbCrLf & "salto de línea"
            'añade & vbCrLf & para ir a la línea entre dos valores

    .Attachments.Add ActiveWorkbook.FullName 'Añade el libro activo como archivo adjunto
    '.Attachments.Add "C:\MiCarpeta\MiArchivo.xlsx"
    'Añade un archivo adjunto a tu correo, indica la ruta completa hacia el archivo que quieres
    'adjuntar

    .Display 'muestra el correo como borrador en Outlook
    '.Send 'envía directamente el correo
    '.Save 'guarda el correo

End With

Set OutMail = Nothing 'limpia la memoria limpiando las variables
Set OutApp = Nothing

End sub

2. Nivel Intermedio - formatea el correo (negrita, subrayado, cursiva, colores, saltos) con HTML simple

Gracias a HTML, puedes fácilmente:

• Dar formato al texto de tu correo
• Poner tu texto en cursiva, negrita, subrayado, en rojo, etc.
• Para más opciones de formateo de texto HTML, consulta este sitio - ¡las posibilidades son inmensas!

Sub envoi_mail_intermediaire()

    ' Declara los objetos aplicación y elemento de mensajería Outlook
    Dim OutApp As Object
    Dim OutMail As Object

    ' Crea una nueva instancia de la aplicación Outlook
    Set OutApp = CreateObject("Outlook.Application")
    ' Crea un nuevo elemento de mensajería
    Set OutMail = OutApp.CreateItem(0)

    ' Con el elemento de mensajería
    With OutMail
        ' Define la dirección de correo del destinatario
        .To = "contact@sap-automation.com"
        ' Muestra el correo
        .Display
        ' Define el asunto del correo para incluir la fecha y hora actuales
        .Subject = "Informe del: " & Date & " " & Time
        ' Define el cuerpo HTML del correo
        .HTMLBody = "Esto es una prueba" & "<br><br>" & _
                    "Este es un texto después de un salto de línea " & "<br>" & _
                    "Este es un texto en la línea" & "<br>" & _
                    "<b> este es un texto en negrita </b> " & "<br>" & _
                    "<u> este es un texto subrayado </u> " & "<br>" & _
                    "<b><u> este es un texto en negrita y subrayado </u></b> " & "<br>" & _
                    "<i> este es un texto en cursiva </i> " & "<br>" & _
                    "<FONT COLOR=RED> este es un texto en rojo </FONT>" & "<br>" & _
                    .HTMLBody

    ' Fin de la instrucción With
    End With

    ' Libera los objetos elemento de mensajería y aplicación Outlook
    Set OutMail = Nothing
    Set OutApp = Nothing

End sub

3. Nivel Avanzado - Correo Outlook con rango de datos Excel en el cuerpo del correo usando una función

Usando una función (muy simple), puedes integrar una tabla o una serie de datos en el cuerpo de tu correo.

Sub envoi_mail_avance()

Dim OutApp As Object
Dim OutMail As Object
Dim plage_mail As Range 'Declara un rango de celdas para el correo

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set plage_mail = ThisWorkbook.Sheets("mail").Range("A1").CurrentRegion 'Define el rango de celdas para el correo

    With OutMail
        .To = "contact@sap-automation.com"
        .Display 'muestra el correo como borrador en Outlook
        '.Send 'envía directamente el correo
        '.Save 'guarda el correo
        .Subject = "Informe del: " & Date & " " & Time
        .HTMLBody = "Por favor encuentre el informe a continuación," & "<br><br>" & _
                    RangetoHTML(plage_mail) & "<br><br>" & _
                    .HTMLBody 'Define el cuerpo del correo en HTML
    End With

Set OutMail = Nothing 'Libera el objeto correo
Set OutApp = Nothing 'Libera el objeto aplicación Outlook

End sub

Para que esta macro funcione, debes añadir la función a continuación en el mismo módulo:

Public Function RangetoHTML(rng_mail As Range)
    Dim fso As Object 'Declara el objeto sistema de archivos
    Dim ts As Object 'Declara el objeto flujo de texto
    Dim TempFile As String 'Declara la variable de cadena para el archivo temporal
    Dim TempWB As Workbook 'Declara el libro temporal

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    'Define la ruta del archivo temporal

    'Copia el rango y crea un nuevo libro para pegar los datos
    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

    'Publica la hoja en un archivo 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

    'Lee todos los datos del archivo htm en 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=")

    'Cierra TempWB
    TempWB.Close savechanges:=False

    'Elimina el archivo htm utilizado en esta función
    Kill TempFile

    Set ts = Nothing 'Limpia el objeto flujo de texto
    Set fso = Nothing 'Limpia el objeto sistema de archivos
    Set TempWB = Nothing 'Limpia el libro temporal

End Function

4. Nivel Avanzado 2 - Correo Outlook con gráfico como archivo adjunto

Añade un gráfico como archivo adjunto al correo Outlook.

Sub envoi_mail_graphique()

Dim OutApp As Object 'Declara el objeto aplicación Outlook
Dim OutMail As Object 'Declara el objeto correo Outlook
Dim xChartName As String 'Declara una variable de cadena para contener el nombre del gráfico
Dim xChartPath As String 'Declara una variable de cadena para contener la ruta del gráfico
Dim xPath As String 'Declara una variable de cadena para contener la ruta
Dim xChart As ChartObject 'Declara una variable ChartObject para contener el objeto gráfico

On Error Resume Next 'Ignora cualquier error y continúa la ejecución

xChartName = "chart" 'Asigna el nombre del gráfico
If xChartName = "" Then Exit Sub 'Sale de la subrutina si el nombre del gráfico está vacío
Set xChart = ThisWorkbook.Sheets("graph").ChartObjects(xChartName) 'Define el objeto gráfico en el gráfico especificado de la hoja "graph"

If xChart Is Nothing Then Exit Sub 'Sale de la subrutina si el objeto gráfico no está definido
Set OutApp = CreateObject("Outlook.Application") 'Crea una nueva instancia de Outlook
Set OutMail = OutApp.CreateItem(0) 'Crea un nuevo elemento de mensajería

xChartPath = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp" 'Define la ruta del gráfico con la ruta del libro actual, el nombre de usuario y la fecha/hora actuales
xChart.Chart.Export xChartPath 'Exporta el gráfico a la ruta especificada

With OutMail
    .To = "contact@sap-automation.com" 'Define el destinatario del correo
    .Subject = "Informe de gráfico del: " & Date & " " & Time 'Define el asunto del correo
    .Attachments.Add xChartPath 'Añade el gráfico exportado como adjunto
    .HTMLBody = "Por favor encuentre el gráfico adjunto." & "<br><br>" & _
                .HTMLBody 'Define el cuerpo del correo
    .Display 'Muestra el correo
End With

Kill xChartPath 'Elimina el archivo temporal del gráfico
Set OutMail = Nothing 'Limpieza
Set OutApp = Nothing 'Limpieza

End Sub

5. Nivel Experto - Correo Outlook con tabla y gráfico en el cuerpo del correo

Añade una tabla y un gráfico en el cuerpo del correo Outlook.

Sub envoi_mail_expert()

Dim OutlookApp As Object 'Declara el objeto aplicación Outlook
Dim OutlookMail As Object 'Declara el objeto correo Outlook
Dim consolidationSheet As Worksheet 'Declara el objeto para la hoja de consolidación
Dim chartObject As chartObject 'Declara el objeto gráfico
Dim tableRange As Range 'Declara el objeto de rango para la tabla
Dim chartFilePath As String 'Declara la variable de cadena para la ruta del archivo gráfico
Dim tableHTML As String 'Declara la variable de cadena para el HTML de la tabla

'Define la hoja de consolidación
Set consolidationSheet = ThisWorkbook.Worksheets("CONSOLIDATION")
Set chartObject = consolidationSheet.ChartObjects("chart_example") 'Define el objeto gráfico
'Define el rango de la tabla
Set tableRange = consolidationSheet.ListObjects("CONSOLIDATION").Range

'Guarda la imagen del gráfico como archivo temporal
chartFilePath = Environ$("temp") & "\chart.jpg"
chartObject.Chart.Export chartFilePath, "JPG"

tableHTML = ConvertRangeToHTML(tableRange) 'Convierte el rango de la tabla a HTML

Set OutlookApp = CreateObject("Outlook.Application") 'Crea un nuevo correo Outlook
Set OutlookMail = OutlookApp.CreateItem(0)

With OutlookMail
    .To = "contact@sap-automation.com"
    .Subject = "Informe de consolidación del: " & Date & " " & Time
    .HTMLBody = .HTMLBody & "<p>Hola, por favor encuentre a continuación la tabla y el gráfico del día:</p>"
    .HTMLBody = .HTMLBody & "<table border='1' cellpadding='5' style='border-collapse: collapse;'>"
    .HTMLBody = .HTMLBody & tableHTML
    .HTMLBody = .HTMLBody & "</table>"
    .HTMLBody = .HTMLBody & "<br><img src='cid:chart' width='600' height='400'>"
    .HTMLBody = .HTMLBody & "</body></html>"

    'Adjunta la imagen del gráfico como imagen integrada
    .Attachments.Add chartFilePath, 1, 0, "chart.jpg"
    .Attachments("chart.jpg").PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001F", "chart"
    .Display 'Muestra el correo (cambia a .Send para enviar sin mostrar)
End With

Kill chartFilePath 'Elimina el archivo temporal
Set OutlookMail = Nothing 'Limpieza
Set OutlookApp = Nothing 'Limpieza

End Sub

Para que esta macro funcione, debes añadir la función a continuación en el mismo módulo:

Function ConvertRangeToHTML(rng As Range) As String

    Dim htmlTable As String 'Declara la variable de cadena para la tabla HTML
    Dim row As Range 'Declara el objeto de rango para la fila
    Dim cell As Range 'Declara el objeto de rango para la celda

    'Inicio de la tabla HTML
    htmlTable = "<thead><tr>"
    For Each cell In rng.Rows(1).Cells
        htmlTable = htmlTable & "<th>" & cell.Value & "</th>" 'Añade cada celda de la primera fila al encabezado de la tabla
    Next cell
    htmlTable = htmlTable & "</tr></thead><tbody>"

    'Añade las filas y celdas a la tabla
    For Each row In rng.Rows
        If row.row <> rng.Rows(1).row Then
            htmlTable = htmlTable & "<tr>"
            For Each cell In row.Cells
                htmlTable = htmlTable & "<td>" & cell.Value & "</td>"
            Next cell
            htmlTable = htmlTable & "</tr>"
        End If
    Next row

    htmlTable = htmlTable & "</tbody>"

    ConvertRangeToHTML = htmlTable

End Function