none
Macro to convert a worksheet to PDF and then to email RRS feed

  • Question

  • Hi All

    Please can someone help me with the following issue I have. I have created a Purchase order (PO) in Excel which i would like to convert to PDF and save in my purchase order file (the save file name will be linked to the PO number on the excel PO sheet), at the same time I want that saved PDF file to be emailed to a specific person that is indicated on the PO sheet.

    I manged to download the following code and adjust it to be able to save the file name the the way i wanted it (the PO #) and to file it where the excel workbook is saved. So basically i need the a macro code that will then email the saved PDF file from the location that it has been saved to.

    BELOW IS THE CODE SO FAR (WHICH WORKS)

    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, " ", ""), ".", "_") _
                & "_" _
                & Range("G14").Value _
                & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile

    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and 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

    Many thanks

    Regards

    Jason edwards


    Tuesday, November 11, 2014 11:10 AM

Answers

All replies