Let me preface this my stating that I am a VBA noob (sorry). I can't just start writing VBA and know what I'm doing, but I often find code online for what I'm trying to do and can modify it to suit my needs, but I am having some trouble with something and am hoping someone can help me.
The end goal is to have a current macro I have which converts a sheet into a PDF and emails it out, to also include an extra sheet (preferably with an easy option to include or not e.g. a cell with a yes/no validation list). The current macro sends out a report built on a pivot table. It changes a pivot table filter to a new variable, creates the PDF and saves it with a specific file name, adds it to an email with subject and body and sends it.
The Function is as follows;
- Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
- OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
- Dim FileFormatstr As String
- Dim Fname As Variant
- 'Test If the Microsoft Add-in is installed
- If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
- & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
- If FixedFilePathName = "" Then
- 'Open the GetSaveAsFilename dialog to enter a file name for the pdf
- FileFormatstr = "PDF Files (*.pdf), *.pdf"
- Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
- Title:="Create PDF")
- 'If you cancel this dialog Exit the function
- If Fname = False Then Exit Function
- Fname = FixedFilePathName
- End If
- 'If OverwriteIfFileExist = False we test if the PDF
- 'already exist in the folder and Exit the function if that is True
- If OverwriteIfFileExist = False Then
- If Dir(Fname) <> "" Then Exit Function
- End If
- 'Now the file name is correct we Publish to PDF
- On Error Resume Next
- Myvar.ExportAsFixedFormat _
- Type:=xlTypePDF, _
- FileName:=Fname, _
- Quality:=xlQualityStandard, _
- IncludeDocProperties:=True, _
- IgnorePrintAreas:=False, _
- On Error GoTo 0
- 'If Publish is Ok the function will return the file name
- If Dir(Fname) <> "" Then Create_PDF = Fname
- End If
- End Function
and in the macro that seems to call this function I see reference to 'Create_PDF' as follows (apart from the code that sets the pivot table filter, this is also the only section that I can find reference to the sheet "Report");
So "Report" is the main sheet that is converted to PDF and sent, and I am hoping that "Report" and "Extra Page" can be converted in the same PDF. As mentioned above, preferably it could add the extra page if a cell variable was "Yes", otherwise only send "Report".
Any ideas? I played around in the red underlined section from this text: 'Create_PDF(Sheets("Report"), FilePath & FileName, True, False)', to see if I could add "Extra Page" but nothing I tried worked.
Many thanks for any assistance.