Need to send predetermined cell range as pdf through excel shape (button)

[cdg786]

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. Email address of client is on the same sheet as a cell. 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("B10").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

 

[sos25]

Put this code in a new workbook and save it as a macro-enabled workbook (.xlsm file). With the macro workbook and the .xlsx workbook open and the PO-V1 (or PO-V2, PO-V3, etc.) sheet active run the Save_Range_As_PDF_and_Send_Email macro.

The macro expects B15 on the active sheet to contain the email address and C6 to contain the email subject.

Public Sub Save_Range_As_PDF_and_Send_Email()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
   
    With ActiveWorkbook
   
        Set PDFrange = .ActiveSheet.Range("A1:I110")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
       
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
   
    End With
   
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .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 Sub

how could we rename the PDF in the email to a set name and not have the same name as the excel file where the information on the PDF generated from?
I'm pretty new to VBA but was able to successfully use the above solution. Only thing i cant manage to figure out is changing the name of the PDF. Any help would be appreciated.

 

[John_w]

Welcome to MrExcel.

how could we rename the PDF in the email to a set name and not have the same name as the excel file where the information on the PDF generated from?

It doesn't use the same name as the Excel file, but the value of B10 on the active sheet - ActiveSheet.Range("B10").Value.

To use a set name, simply: PDFfile = destFolder & "My set name.pdf"

If you need more help please start a new thread and include the code you're trying to adapt.