Email 2 sheet ranges as 2 pdf files to email specified in a cell

[fleyd]

Hello,

i have a macro that prints a range in CSS sheet into pdf and attach that pdf to a Outlook email. Also that email has a subject, destination and cc according to cells in that same sheet. It works great for my purposes.


However sometimes i have the need to send 1 email with that same pdf attached and also a pdf attached of a second sheet VSP, with same rules, range defined in VSP cell AY10, email destination, body text... The same.

First macro for CSS is great, don´t wanna change anything

I just need a second macro (very similar to first) that will create an email with first pdf (css sheet) and also a second pdf (vsp sheet), with name as in VSP cell AY16, attach both files to a Outlook email with body text as in vsp AY 14, email destination AY19, CC as in AY12.


Can someone help me with the code to create email that attach both pdf´s ?

Attached will be a example file with code working great for 1 sheet

Many thanks and hoping someone can help.

[davesexcel]

You can create the pdf for the second sheet and then add another attachment.
I had to remove the body html line, you will need to replace it in your code.
Forum does not like html tags in replies.

Sub Mail_PDF2()

    Dim PDF_To_Mail_1 As String
    Dim PDF_To_Mail_2 As String
    Dim OutApp As Object
    Dim OutMail As Object

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

    With Sheets("CSS")
        PDF_To_Mail_1 = .Range("n9").Value & "\" & .Range("N10").Value & ".PDF"
        .Range(.Range("N4").Value).ExportAsFixedFormat 0, PDF_To_Mail_1
    End With
    
    With Sheets("VSP")
        PDF_To_Mail_2 = .Range("AY15").Value & "\" & .Range("AY16").Value & ".PDF"
        .Range("AQ17") = PDF_To_Mail_2
        .Range(.Range("AY10").Value).ExportAsFixedFormat 0, PDF_To_Mail_2
    End With

    On Error Resume Next

    With OutMail
        .To = ActiveSheet.Range("n5")
        .CC = ActiveSheet.Range("n6")
        .BCC = ""
        .Subject = ActiveSheet.Range("n7")
        '================Remeoved Line for forum to accept-replace with original line
        .Attachments.Add(PDF_To_Mail_1).FullName
        .Attachments.Add(PDF_To_Mail_2).FullName
        .Display
    End With
    On Error GoTo 0

    Kill PDF_To_Mail_1
    Kill PDF_To_Mail_2
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

[fleyd]

Hi,

I already copied your code to my original file and is working great! Many many thanks for the help.

Have a nice day.