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