Creating and Mailing a PDF From Excel (Using Outlook)

[Ctmich89]

Hey guys,

So I have spread sheet with a Data Input Tab and once the info is in there I created a button to export it as a PDF & Save the document as an Excel file on my desktop. I have posted the code below and was curious how to add on or make a separate button to export the PDF as an email attachment through outlook. Ideally I would like to grab the email address from a certain cell, say D12, and have it create an email from that. Thanks for the help in advance!

Code Currently:

Sub SavePDF()
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\cmichalski\Desktop\" & Range("I1") & " Pilot Agreement " & Format(Date, "MM - DD - YYYY"), _
OpenAfterPublish:=True
ActiveWorkbook.SaveAs Filename:="C:\Users\cmichalski\Desktop\" & Range("I1").Value & " Pilot Agreement " & Format(Date, "MM - DD - YYYY"), FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

 

[Domenic]

Try...

Option Explicit

Sub SavePDF()

    Dim OL              As Object 'Outlook
    Dim MI              As Object 'Mail Item
    Dim sFile           As String
    
    sFile = "C:\Users\cmichalski\Desktop\" & Range("I1") & " Pilot Agreement " & Format(Date, "MM - DD - YYYY")

    Sheet2.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=sFile, _
                                OpenAfterPublish:=True
    
    ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Set OL = CreateObject("Outlook.Application")
    Set MI = OL.CreateItem(0)
    
    With MI
        .To = "john@gmail.com"
        .Subject = "Your Subject"
        .Body = "Your message here..."
        .Attachments.Add sFile & ".pdf"
        .Display 'Send
    End With
        
End Sub

Note that for testing purposes the code only displays the email. To actually send it, replace .Display with .Send. Also, to overwrite any already existing workbook, try...

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True

Hope this helps!