I've created some timesheets for the company I work for and have created a Create PDF where it saves to the location the excel sheet is in, (everyone keeps there timesheets indifferent locations) or they can choose a different location. It then opens the PDF so it can be signed by the employee. After that is done I would like a button that emails the document but how do I get the location they just saved to?
Here is the current Create PDF code I am using.
Private Sub CommandButton1_Click()
'Sub CreatePDF()
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
strName2 = ActiveSheet.Range("H3")
strName3 = ActiveSheet.Range("H1")
strName4 = "Timesheet"
'replace spaces and periods in sheet name
strName = Replace(wsA.Name, "", " ")
strName = Replace(strName, ".", "-")
'create default name for savng file
strFile = strName2 & " - " & strName3 & " - " & strName & " " & strName4 & ".pdf"
strPathFile = strPath & strFile
'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
'Check if file already exist
If Len(Dir(strFile)) > 0 Then
xYesorNo = MsgBox(strPathFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill strPathFile
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If
'export to PDF if a folder was selected
If myFile <> False Then
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If
exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
Thank you.