Vba Macro - print a specified range to Pdf - send by Outlook - specified subject/body in a cell.

[fleyd]

Hello,

I´m not and expert in VBA, and i searched the web for a Macro that would do this:
- can print to A4 PDF a specified range in a cell
- Save that PDF to a specified destination in a cell
- Save that PDF with a specific name specified in a cell
- send by Outlook that PDF attachement
- with specified subject/body as a specific a cell
- Email destination and cc as specified in a cell

I managed to find a Macro that does part of this, but not everything. Here´s an Example sheet

Can someone help with the rest of the code to do this?

Feel free to download the file. Many thanks in advance for the help.

 

[fleyd]

Hi,

It has an error here:

.Range(.Range("N4").Value).ExportAsFixedFormat 0, PDF_To_Mail

In the cell N4 i have tried " c:\ " and other folders.... without quotations marks

Sub Mail_PDF()

    Dim PDF_To_Mail As String
    Dim OutApp As Object
    Dim OutMail As Object

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

    With Sheets(Range("n3").Value)
        PDF_To_Mail = Range("n9").Value & "\" & Range("N11").Value & ".PDF"
            .Range(.Range("N4").Value).ExportAsFixedFormat 0, PDF_To_Mail
    End With

    On Error Resume Next

    With OutMail
        .To = ActiveSheet.Range("n5")
        .CC = ActiveSheet.Range("n6")
        .BCC = ""
        .Subject = ActiveSheet.Range("n7")
        .body = ""
        .Attachments.Add(PDF_To_Mail).FullName
        .Send
    End With
    On Error GoTo 0

    Kill PDF_To_Mail

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[fleyd]

Hello again,

It´s working !!!! The problem was that in filename i was placing a bar " \ " . And name files can´t have bars...

Thanks a lot for the help!

 

[jolivanes]

N4 should have the Range to print.
Also you should not have a backslash.
The line above adds the backslash.

Otherwise, show us what you have in all the cells that are relevant.

N3 should have the Sheet Name to be saved to PDF
N9 should have the Path without backslash
N11 should have the File Name that you want it saved as. Anything will do because you'll delete it after anyway
N4 should have the Range (like A1:J45) on the Sheet from N3 that you want saved/printed to PDF

 

[jolivanes]

OK, while I was sending the reply you found the problem.
Thanks for letting us know and good luck

 

[fleyd]

Many thanks again!