Trying to convert a saved workbook to PDF, save then email

Alstoj79

New Member
Joined
Mar 7, 2018
Messages
22
Hi all, really need some help with this. searching the internet for a while now and can only get close and not sure how to amend code.
So basically all I want to do is
1, convert an already saved excel workbook to PDF
2, use the filename of the workbook that's already saved and save it in a separate subfolder folder
3, use an email address in a cell (say cell J1) and get it ready to send as an attachment in outlook to that email address

so far I have found this code to convert and save the workbook but it saves in the same location to my original. how would I get it to save in a separate subfolder i.e C:/foldername/foldername/subfoldername

then to add some code to open outlook, add the pdf to an attachment and send it to an email address that is in cell J1 of the workbook.
note, don't want it to automatically send I would like to hit the send button.

here is the code I have for the convert. Please can someone help and add the rest of the code I need. - Im a total newbie at this.

Sub EmailPDF()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName

If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(s(0))
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(s(0), s(1), ".pdf")

'//Export to PDF with new File Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sNewFilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
Else
'//Error: file path not found
MsgBox "Error: this workbook may be unsaved. Please save and try again."
End If

Set FSO = Nothing
End Sub


many thanks
 
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi Alstoj79,

Did you solve your problem? If it is solved, can you please place the code here?

Thanks,
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi keithmct,

Great that worked a treat. Creates, and saves the workbook as a pdf in a specified folder using the already saved filename (with .pdf at the end)

here's my final code for this:
Code:
Sub EmailPDF()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    
    If FSO.FileExists(s(0)) Then
        '//Change Excel Extension to PDF extension in FilePath
        s(1) = FSO.GetExtensionName(s(0))
        If s(1) <> "" Then
            s(1) = "." & s(1)
            sNewFilePath = Replace(s(0), s(1), ".pdf")
            
            '//Export to PDF with new File Path
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                fileName:="C:\Users\Asus Laptop\Documents\Invoices\PDF Invoices\" & ThisWorkbook.Name & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
        End If
    Else
        '//Error: file path not found
        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    End If
    
    Set FSO = Nothing
End Sub

Perfect, thanks,
Now I think I can make this attach to an email and get ready to send using an email in a cell by myself, if not, I'll be back!! haha

Just one question, why is there a macro warning every time I open the excel workbook? do I have to digitally sign the macro to be safe?

Thanks all
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

you will need to enable the macro and active X settings in excel options / trust center settings.
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi Keithmct,

Thanks for your information. I shall much appreciate if you could please send the code.

With best regards,
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

see post #9 . all the code is available at Ron de Bruin's site.
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Vivek Roshan, here is the code I use (credit Ron de Bruin) for something very similar. In your workbook, open Visual basic and insert a new Module, rename it Functions Module in the properties window. Paste this code without changing anything:
Code:
Option Explicit

'The code below are used by the macros in the other two modules
'Do not change the code in the functions in this module

Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
                        OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the pdf
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                                                  Title:="Create PDF")

            'If you cancel this dialog Exit the function
            If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If

        'If OverwriteIfFileExist = False we test if the PDF
        'already exist in the folder and Exit the function if that is True
        If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If

        'Now the file name is correct we Publish to PDF
        On Error Resume Next
        Myvar.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0

        'If Publish is Ok the function will return the file name
        If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
    End If
End Function



Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                              StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function
Insert another Module and paste this code. Make changes to recipient and folders etc as required.
Code:
Option Explicit

'Note: The macro's in this module call the functions in the "FunctionsModule"
'Be sure that you also copy the code from this module if you want to use it in your own workbook.


Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
    Dim FileName As String
    
    FileName = RDB_Create_PDF(ActiveSheet, "C:\folder\folder\" & "Invoice" & ".pdf", True, False)
    
    If FileName <> "" Then
        RDB_Mail_PDF_Outlook FileName, "someone@address.com", "subject goes in here", _
                             "message in email body e.g. have a nice day" _
                           & vbNewLine & vbNewLine & "your signature goes in here", False
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If
End Sub
on Sheet 1 or wherever, insert an active x button.
change to developer/design mode. right click the button to view code and when visual basic appears insert this code:
Code:
Private Sub CommandButton1_Click()
Call RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail
End Sub

hope this helps you out
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi keithmct,

Thank you very much. That code works a treat. amended a little to what I need and works perfectly.
Thanks
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Ok, just one last quick question,

How do I get outlook to use my default signature in the automated email it cerates?

Thanks
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

This is the code that I use. Hopefully should work for you:-


Code:
Rem Get default mail signature
    Signature = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(Signature, vbDirectory) <> vbNullString Then
        Signature = Signature & Dir$(Signature & "*.htm")
    Else:
        Signature = ""
    End If
    Signature = CreateObject("Scripting.FileSystemObject").GetFile(Signature).OpenAsTextStream(1, -2).ReadAll

    On Error Resume Next
    With OutMail
        .To = vaRecipient
        .CC = carbon_copy
        .BCC = ""
        .Subject = stSubject
        .htmlBody = vaMsg & "<br />" & "<br />" & Signature
        .Attachments.Add (PerfAttachment)
        .send   'or use .display
    End With
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi Stiuart_W,

Sounds like that will work, how would you go about adding your code to the above code in post #26 ?

I don't want to override my current setting / code for the top, cc, and others, I just need to add the default signature.
Your help is appreciated immensely.

Tanks
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,473
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top