Emailing PDF Macro

[Noddy13]

Hi All,

I am working on a macro the will open a workbook, send the first sheet as a pdf and then save and close the workbook. This is what I have so far after pilfering other variations.

Sub Send_Reports()
'
' Send_Reports Macro
    Dim sh As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileName As String
    ChDir "K:\Reports\Store Reports\Store Reports\NSW"
    Workbooks.Open FileName:="K:\Reports\Store Reports\Store Reports\NSW\081.xls"
    'Temporary path to save the PDF files
    TempFilePath = Environ$("temp") & "\"
    '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 h-mm-ss") & ".pdf"
            FileName = RDB_Create_PDF(sh, TempFileName, True, False)
 
            'If publishing is OK create the mail
            If FileName <> "" Then
                RDB_Mail_PDF_Outlook FileName, sh.Range("A1").Value, "TGGIS Report " & Format(Now, "dd-mmm-yy"), _
                                     "See the attached PDF file with the latest data" _
                                   & vbNewLine & vbNewLine & "Regards The Good Guys Installation Service", False
                'After the 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
    ActiveWorkbook.Save
    ActiveWindow.Close

As a Noob to macros I am struggling to get this to work.

Any help would be appreciated.

 

[repairman615]

Hello,

I see you have already discovered Ron de Bruins' site..

Are you also using:
RDB_Worksheet_Or_Worksheets_To_PDF macro.

What error are you getting and on what line?

 

[Noddy13]

Hi,

Yes I do try and source as many sites as I can to find the answer before posting on here. Sometimes it helps but on others it just makes me more confused. As is the case now.

The error I am getting is

Sub or Function not defined.

The first line

Sub Send_Reports

is highltighted yellow and

RDB_Create_PDF

is selected text.

Thanks
Jamie

 

[repairman615]

Hello Jamie
I have found great stuff at that site, a big Thank You to Ron and his work...err hobby.

Any rate, do you also have that function: RDB_Create_PDF?

In other words, you will need three bits of code from his site.

RDB_Create_PDF
RDB_Worksheet_Or_Worksheets_To_PDF
RDB_Mail_PDF_Outlook

do you have all three?

 

[Noddy13]

I think the issue that I am having is that the code I am trying to use is for a macro set up in the workbook that has the sheets in it. What I am trying to achieve is to have the macro in another workbook. This will then allow me to open multiple workbooks and email the each sheet.