none
Save active sheet to pdf with custom filename then email it RRS feed

  • Question

  • I am trying to accomplish the following

    1. I enter details onto a spreadsheet in Excel 2013 then at the press of a button...
    2. The sheet is exported to a pdf file with a filename that goes worksheet name, date, data from cell B7.pdf
    3. The pdf is saved to my computer
    4. Outlook opens a message prepared for sending with the pdf attached. It is important that I can edit the message and recipients before sending

    At the moment, I have used the following vba code:

    Sub PDFActiveSheet()
    Dim ws As Worksheet
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    On Error GoTo errHandler

    Set ws = ActiveSheet

    'enter name and select folder for file
    ' start in current workbook folder
    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
                & "_" _
                & Format(Now(), "yyyymmdd\_") _
                & Range("B7").Value _
                & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile

    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")

    If myFile <> "False" Then
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False

        MsgBox "PDF file has been created."
    End If

    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub

    This saves a pdf file successfully with the filename exactly how I want it, but I am struggling with step 4 as detailed above, I have tried to put some vba on the end to get it to email said pdf but I can still only get it to save the pdf, not email it. Can anyone help me?


    • Edited by Ianclark863 Tuesday, December 23, 2014 5:52 PM
    Tuesday, December 23, 2014 5:51 PM

Answers

  • Hi lanclark863,

    You can call the Outlook Object Model to send the email with the pdf file as the attachment, firstly please take a look at this MSDN tutorial:

    OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook

    Sample code here:

    Dim OutApp As Object
        Dim OutMail As Object
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
       ' Change the mail address and subject in the macro before you run it.
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hello World!"
            .Attachments.Add ActiveWorkbook.FullName
            ' You can add other files by uncommenting the following line.
            '.Attachments.Add ("C:\test.txt")
            ' In place of the following statement, you can use ".Display" to
            ' display the mail.
            .Send   
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing

    If you want to edit the message before sending it. you could use VBA to prompt a input box, input your message details then send the mail. Or you could just display the NewMail window to edit the mail content, and send it manually. Before display the NewMail window, you can set the mail attachment firstly. Check this sample code:

    With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hello World!"
            .Attachments.Add ActiveWorkbook.FullName
            ' You can add other files by uncommenting the following line.
            '.Attachments.Add ("C:\test.txt")
            ' In place of the following statement, you can use ".Display" to
            ' display the mail.
            .Display
        End With


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 26, 2014 3:02 AM
    Moderator