Automating Emails with VBA: Connecting Excel with Outlook

Generate drafts or send messages programmatically from Excel.

Create a draft with attachment

Sub SendReport()
    Dim olApp As Object, mail As Object
    Dim path As String: path = ThisWorkbook.Path & Application.PathSeparator & "report.pdf"

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    Set mail = olApp.CreateItem(0)
    With mail
        .To = "team@example.com"
        .Subject = "Monthly Report"
        .Body = "Hi," & vbCrLf & "see attached."
        If Dir(path) <> "" Then .Attachments.Add path
        .Display ' show draft
    End With
End Sub

Note: Organizational security policies may prompt the user.