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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Alstoj79,

I too am a newbie, but as coincidence would have it, your scenario is pretty close to the one that brought me here for help a few days ago. I'm going to post the code that works for me. Maybe you might be able to glean a tip or 2 from it, and craft a hybrid of what you have with what I have.
Code:
Sub Create_PDF_from_Excel_and_send_with_Outlook()


Dim OutlookApp As Object
Dim Mess As Object, Recip
Dim strbody As String
Dim Path As String
Dim fileName As String
Dim rng As Range
Dim lrow As Long




  
Set rng = Range("A3:h450")
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)


Path = "C:\Users\Me\Desktop\VBA Files\"
fileName = Range("f2") & " " & Range("c4")
Recip = [h2].Value
subject = [f2].Value
strbody = "Have a nice day!"


With ActiveSheet
    .Range("A3:H13" & Cells(Rows.Count, 1).End(xlUp).Row).Name = "PDFRng"
    Set Myrange = ActiveSheet.Range("PDFRng")
    ActiveSheet.PageSetup.PrintArea = "PDFRng"
    Myrange.ExportAsFixedFormat Type:=xlTypePDF, _
    fileName:=Path & fileName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    Orientation = xlLandscape
    .PageSetup.CenterFooter = "&P"
    
End With
             
With Mess
        .Display
        .Sensitivity = 3
        .To = Recip
        .bcc = "someone@somecompany.com"
        .subject = subject
        .Attachments.Add Path & fileName & ".pdf"
        .HTMLBody = strbody & "
" & .HTMLBody
        SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}{End}", True
        '.Send
 End With


End Sub

Hopefully someone with more experience will reply too.

Best of luck!

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

Thanks Mike,
its all really confusing me now.

Is there a line of code in my original post that I can change so that the workbook saves to a specific folder? if not how do I add this?

That is the first step I think.

Then all I need to do is figure out how to send it to outlook as an attachment using an email in cell J1.

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

Where exactly do you want to save the PDF?
 
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

I would like to save it to here:

C:\Users\Asus Laptop\Documents\Invoices\PDF Invoices

Then attach it to an email using and email address in cell J1 but not send it.

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

Is the workbook you want to convert to PDF and email the workbook the code is in or is it another workbook?

If it's the latter will it be a particular workbook that is already open or would you want to select a workbook to open, convert, send etc.?
 
Last edited:
Upvote 0
Re: Newbie to VBA and Macros - Trying to convert a saved workbook to PDF, save then email

Hi,
So basically I am creating an invoice template in excel, then once the file has been filled and saved as an excel file I want to export to pdf and email. So I think the code will always be in the workbook???

The current code works great at creating a pdf, but just need it save in the location as mentioned above and attach to an email with the email address taken from cell J1

Hope this helps.

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

Hi all,

Sorry been away for a couple of days. Just checking back to see if anyone has come to my rescue!!
If anyone can help me I (and my father) would really appreciate it.

This will probably be the first of many questions on this forum so please be kind..

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

Hi keithmct,

not come across that site before. will check it out.
thanks
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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