Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Save As PDF to Current Location then Email from tha location

0

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.

Answer
Discuss

Discussion

RWSM

Please edit your question, select your code and use the CODE button to enclose it in  tags (so it displays properly for easier reading). Also, kindly use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and sample data. 

It's too late here for me to look at this but I'll try to do so some time tomorrow.
John_Ru (rep: 4312) Nov 2, '22 at 7:07 pm
RWSM- I sent you an Answer any way (see below) but kindly add CODE tags to your question, as described above.
John_Ru (rep: 4312) Nov 3, '22 at 3:21 pm
Did you try my Answer? 
John_Ru (rep: 4312) Nov 7, '22 at 3:02 am
Did it help? 
John_Ru (rep: 4312) Nov 16, '22 at 8:19 am
Add to Discussion

Answers

0

RWSM

In your code above, this line:

myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

gives you the full path (and file name) the user chooses. You just have to use the variable myFile in your email code . To test this, you could add this after that line

MsgBox myFile

Incidentally, you then do some tests on the INITIAL filename strFile, in the few lines following this one:

If Len(Dir(strFile)) > 0 Then

but I think you should change those to myFile (since the user may have changed directory and file name specified in strFile).

If you don't know how to send emails, you might start with Don's tutorial Send Emails from Excel - Automatically and by Hand

Hope this helps.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login