Hello all,
I have an excel file to send out emails based on data entered. I have a raw data tab where I paste information, a master pivot tab and then a tab for each customer which is filtered on their specific data. I have used code from Ron de bruins site to try to send each page as PDF to the address in cell A1 of each sheet, however when I run it I get "Compile Error: Sub or Function not defined" with "RDB_Create_PDF" highlighted in blue. The code i have copied is below - any help would be great thank you.
I have an excel file to send out emails based on data entered. I have a raw data tab where I paste information, a master pivot tab and then a tab for each customer which is filtered on their specific data. I have used code from Ron de bruins site to try to send each page as PDF to the address in cell A1 of each sheet, however when I run it I get "Compile Error: Sub or Function not defined" with "RDB_Create_PDF" highlighted in blue. The code i have copied is below - any help would be great thank you.
VBA Code:
Sub Mail_Every_Worksheet_With_Address_In_A1_PDF()
'This example works in Excel 2007 and Excel 2010.
Dim sh As Worksheet
Dim TempFilePath As String
Dim TempFileName As String
Dim FileName As String
'Set a temporary path to save the PDF files.
'You can also use another folder similar to
'TempFilePath = "C:\Users\Ron\MyFolder\"
TempFilePath = Environ$("temp") & "\"
'Loop through each worksheet.
For Each sh In ThisWorkbook.Worksheets
FileName = ""
'Test A1 for an e-mail address.
If sh.Range("A1").Value Like "?*@?*.?*" Then
'If there is an e-mail address in A1, create the file name and the PDF.
TempFileName = TempFilePath & "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
FileName = RDB_Create_PDF(sh, TempFileName, True, False)
'If publishing is set, create the mail.
If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, sh.Range("A1").Value, "This is the subject", _
"See the attached PDF file with the last figures" _
& vbNewLine & vbNewLine & "Regards Ron de bruin", False
'After the e-mail is created, delete the PDF file in TempFilePath.
If Dir(TempFileName) <> "" Then Kill TempFileName
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & 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