VBA - Excel Worksheet Selected Range to PDF - Email via Outlook

[united2017]

Dear Forumers,

I am very new to the concept of VBA. I have a workbook with an identical template in each sheet. Each sheet represents different areas of the business. What I want to do is, when I have all the information in these sheets, I want to send the selection of the print area as a PDF to an e-mail address in a given cell in the worksheet. Each worksheet has a unique e-mail address.
So once I finish the data on 'Sheet 1', I want to run the macro to send e-mail to the address in for example A1 of that sheet, than the same for sheet 2, 3, etc.

It has to be the selected print area, as there will be other workings on the same sheet which I do not want to send out.

Your help will be highly appreciated.

Thanks,

 

[cdg786]

@John_w I am facing a similar issue. I am trying to create a a button via insert shape, and then assign a module to that shape when clicked. The goal is to send just a predetermined set of cells as a pdf via outlook. Here is what I am currently inserting, and it is not working. Clearly. I have the print area set to A1:I110. Ideally, I could just code the module to read that set of cells, rather than print area. Any help is appreciated. New to this.

Sub RectangleRoundedCorners1_Click()

Public Sub Send_Email_For_Print_Area()

    Dim destFolder As String, PDFfile As String
    Dim printRange As Range
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem

    Set OutApp = New Outlook.Application
    
    'PDF file for print range is temporarily saved in same folder as this workbook
    
    destFolder = ThisWorkbook.Path & "\"
    If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
    
    If ActiveSheet.PageSetup.PrintArea <> "" Then
    
        'Save print area for active sheet as a PDF file, file name from cell C6
        
        PDFfile = destFolder & ActiveSheet.Range("A2").Value & ".pdf"
        Set printRange = Range(ActiveSheet.PageSetup.PrintArea)
        printRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
        'Send email to address in cell B15 of active sheet with PDF file attached
        
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = ActiveSheet.Range("B15").Value
            .Subject = ActiveSheet.Range("C6").Value
            .Body = "Please see attached PO. We look forward to your response and collaboration. Do not hesitate to reach out with any questions. Thank you."
            .Attachments.Add PDFfile
            .send
        End With

        'Delete the temporary PDF file
        
        Kill PDFfile
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End If
    
End Sub

End Sub