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

[Alstoj79]

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

 

[Alstoj79]

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

In the meantime, can someone help me with what I need??
Just while I learn VBA a little more.

Thanks

 

[Alstoj79]

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

Hi All,

I am still having problems getting my head round the code I need. I have tried amending other peoples code but its all just guess work as to what I need to amend and its doing my head in a little.

Pleas please can someone help me get the right code. The code I have at the moment works great, but I just cant seem to add a line of code to save it to another folder.
then I presume I can just copy and paste the email code I have underneath this code.
Many Thanks

 

[Stiuart_W]

Here is some example code I use.

First this one opens a save as dialogue so you can select folder and enter file name that is required:-

fname = Application.GetSaveAsFilename
 

Worksheets(Array("Performance Graphs", "Items", "Debits", "Supp_Scars")).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            fname, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False

Alternatively you can build the variable with your filepath and filename

Suggest you post your code else we're flying blind

 

[Alstoj79]

Hi Stiuart_W,

Thanks for this code. My current code to generate a PDF and save it in the same file directory as the original is below. Just need to be able to save it to another folder i.e. C:\Users\Asus Laptop\Documents\Invoices\PDF Invoices.
here is my current code:

[I][COLOR=#000080]Sub EmailPDF()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String[/COLOR][/I]
[I][COLOR=#000080]    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[/COLOR][/I]
[I][COLOR=#000080]End Sub[/COLOR][/I]

I think I have the add to email attachment figured out. Just need to amend the above code to save in a specific folder.
Thanks

 

[Patcheen]

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

I uses this site..... hope it helps

https://online2pdf.com/convert-excel-to-pdf

 

[Alstoj79]

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

Thanks Patcheen, but really don't want to use and external web based service. would like to keep it on my excel worksheet and assign a button so as once I have saved my invoice I can then press a button and it will save the workbook to a pdf file and attach it to an email ready to send.

 

[Stiuart_W]

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

I'm not entirely clear what you are trying to achieve. But your code errors out on this line:-

s(1) = FSO.GetExtensionName(s(0))

If you know the file name what are you trying to do?

Are you trying to save the pdf in 2 places?

 

[Dryver14]

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

realistically if you are happy with the path you have at the top then a lot of what is being done is redundant in the code.

you could just say sNewFilePath = "C:\Users\Asus Laptop\Documents\Invoices\PDF Invoices.pdf" or whatever you want to call it

 

[Alstoj79]

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

Not sure about that. It works fine on my PC
All I am trying to do is save it in this folder C:\Users\Asus Laptop\Documents\Invoices\PDF Invoices rather than the original folder where the excel file is saved.
then send it as email.

P.s I got this code from somewhere on this forum.

Thanks

 

[keithmct]

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

maybe try this:
fileName: = "C:\Users\Asus Laptop\Documents\Invoices" & "sNewFilePath" & ".pdf",

or maybe with a backslash after Invoices