Convert Sheet to PDF - Name PDF by Cell Data - Attach to Email

JeffDumps · Jun 24, 2015

Hello,

I am trying to piece together a macro that will convert a worksheet to PDF, save the PDF file name based off of cell data and attach it to a new email in Outlook.

I used Ron Debruin's code and combined it with Luke M's code (http://forum.chandoo.org/threads/vba-to-save-page-as-pdf-name-to-come-from-cell-values.11741/)

What I can't get to work is referencing the newly created PDF file to attach to the email.
When I run the code it creates the PDF and names it correctly - creates the new email in Outlook - but does not attach the newly created PDF.

I've attached the code below...

Help is much appreciated,

Jeff

Code:

Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
    Dim FileName As String

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more then one sheet selected," & vbNewLine & _
               "be aware that every selected sheet will be published"
    End If

    'Call the function with the correct arguments
    'Tip: You can also use Sheets("YourSheetName") instead of ActiveSheet in the code(sheet not have to be active then)
 
    'Saves active worksheet as pdf using concatenation
    'of G2,(" "),F3

Dim fName As String
With ActiveSheet
    fName = .Range("G2").Value & (" ") & .Range("F3").Value
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "C:\Users\Jeff\Desktop\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With


    'For a fixed file name use this in the FixedFilePathName argument
    'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"


    If fName <> "" Then
        RDB_Mail_PDF_Outlook FileNamePDF:="C:\Users\Jeff\Desktop\" & fName, _
                             StrTo:="person@email.com", _
                             StrCC:="", _
                             StrBCC:="", _
                             StrSubject:="This is the subject", _
                             Signature:=True, _
                             Send:=False, _
                             StrBody:="<H3><B>Dear Customer</B></H3><br>" & _
                                      "<body>See the attached PDF file with the last figures."
    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 Sub

'Here are the functions that are called out....

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                              StrCC As String, StrBCC As String, StrSubject As String, _
                              Signature As Boolean, Send As Boolean, StrBody As String)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        If Signature = True Then .Display
        .To = StrTo
        .CC = StrCC
        .BCC = StrBCC
        .Subject = StrSubject
        .HTMLBody = StrBody & "<br>" & .HTMLBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Sub RDB_Worksheet_Or_Worksheets_To_PDF()
    Dim FileName As String

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more then one sheet selected," & vbNewLine & _
               "be aware that every selected sheet will be published"
    End If

    'Call the function with the correct arguments
    'Tip: You can also use Sheets("YourSheetName") instead of ActiveSheet in the code(sheet not have to be active then)
    FileName = RDB_Create_PDF(Source:=ActiveSheet, _
                              FixedFilePathName:="", _
                              OverwriteIfFileExist:=True, _
                              OpenPDFAfterPublish:=True)

    'For a fixed file name use this in the FixedFilePathName argument
    'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"

    If FileName <> "" Then
        'Ok, you find the PDF where you saved it
        'You can call the mail macro here if you want
    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 Sub

[/CODE)]

chirayu · Jun 25, 2015

I think the Reason is that your Variables are not Global Variables.

Paste the Variables outside the code so that the Other Subs/ Functions can reference to them i.e.

Code:

Dim FileName as String
Dim fName as String
'Other variables here.
'Make sure each variable used has a unique name so the values don't get overwritten
Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail
'rest of code blah blah etc :P
End Sub
'Other functions n subs etc...

JeffDumps · Jun 26, 2015

Chirayu, thanks for the input. I did try it but had trouble getting results. It's most certainly my lack of knowledge! However I found my strength is brute force when I am ignorant. This is what I found after an hour of google searching, and I only modified one part which references the ActiveSheet.Range("A1").Value. I guess it was just luck and persistence. I have no real grasp of how this works, but just a simple logic for which I know not how to speak it's language!

I will say it is amazing how complicated you can make things and also how simple you can make them - and they both accomplish the same thing.

I'll post this in case anyone else comes after me looking for the same exact thing I was...

Again thanks for the help!

Code:

'==================================================================
Sub SendPDF()
' Create PDF of active sheet and send as attachment.
'
    Dim strPath As String, strFName As String
    Dim OutApp As Object, OutMail As Object
   
     'Create PDF of active sheet only
    strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"
   
    strFName = ActiveWorkbook.Name
    strFName = Left(strFName, InStrRev(strFName, ".") - 1) & "_" & ActiveSheet.Range("A1").Value & ".pdf"
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       
     'Set up outlook
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
     'Create message
    On Error Resume Next
    With OutMail
        .to = "***email address removed***" 'Insert required address here ########
        .CC = ""
        .BCC = ""
        .Subject = "Insert Subject Text Here"
        .Body = "Insert Body Text Here." & vbCr & "Best regards, etc." & vbCr
        .Attachments.Add strPath & strFName
        .Display   'Use only during debugging ##############################
        '.Send      'Uncomment to send e-mail ##############################
    End With
     'Delete any temp files created
    Kill strPath & strFName
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

chirayu · Jun 26, 2015

Yeah Excel VBA is pretty complicated. Check out "The Vault" section of this forum. I got a few posts in there with VBA tutorials/ explanations on my most used macros

Convert Sheet to PDF - Name PDF by Cell Data - Attach to Email

JeffDumps

New Member

chirayu

Well-Known Member

JeffDumps

New Member

chirayu

Well-Known Member