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
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