Urgent help Needed -Troubleshooting - Email Tabs as Separate PDFs

Sam123456789

New Member
Joined
Oct 17, 2018
Messages
1
All,

I'm new to using VBA and am trying to update my predecessors workbook and am running into an error. The macro is trying to email each tab as a separate pdf but provides the error 'Sub or Function not defined' and highlights 'Sub Mail_Every_Worksheet_With_Address_In_A1_PDF()' along with 'Create PDF'.

Any help would be greatly appreciated.

Code:
Sub Mail_Every_Worksheet_With_Address_In_A1_PDF()'Working only in 2007 and up
    Dim sh As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileName As String


    'Temporary path to save the PDF files
    'You can also use another folder like
    'TempFilePath = "C:\Users\Ron\MyFolder\"
    TempFilePath = "C:\Users\


    'Loop through every worksheet
    For Each sh In ThisWorkbook.Worksheets
        FileName = ""


        'Test A1 for a mail address
        If sh.Range("A1").value Like "?*@?*.?*" Then


            'If there is a mail address in A1 create the file name and the PDF
            TempFileName = TempFilePath & sh.Name _
                         & " " _
                         & Format(Now, "dd-mmm-yy") & ".pdf"


            FileName = Create_PDF(Source:=sh, _
                                      FixedFilePathName:=TempFileName, _
                                      OverwriteIfFileExist:=True, _
                                      OpenPDFAfterPublish:=False)


            'If publishing is OK create the mail
            If FileName <> "" Then
                Mail_PDF_Outlook FileNamePDF:=FileName, _
                                     StrTo:=sh.Range("A1").value, _
                                     StrCC:="", _
                                     StrBCC:="", _
                                     StrSubject:="Spending Report by Function", _
                                     Signature:=True, _
                                     Send:=False, _
                                     StrBody:="<H3>Good afternoon,</H3><br>" & _
                                              "******>Please see the attached PDF file with the latest Monthly Spending Report. If you have any questions please let me know.</H3><br> Best,"
            Else
                MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
                       "Microsoft Add-in is not installed" & vbNewLine & _
                       "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
                       "The path to Save the file in arg 2 is not correct" & vbNewLine & _
                       "You didn't want to overwrite the existing PDF if it exist"
            End If


        End If
    Next sh
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Create_PDF is the name of the function being called. So you'll need to make sure that you've copied the code for that function into your workbook as well. Same thing for Mail_PDF_Outlook, you'll need to copy the code for that procedure into your workbook.
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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