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

 

[John_w]

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.

With the macro recorder running, select A1:I110 and Save As a PDF (you have to choose 'Selection' in the save options), to generate the code which can be incorporated into your macro.

In effect, the only things you need to change are the printRange.ExportAsFixedFormat statement to ActiveSheet.Range("A1:I110").ExportAsFixedFormat and get rid of all the lines with the PrintRange variable and the If ActiveSheet.PageSetup.PrintArea <> "" Then line and its corresponding End If

 

[cdg786]

Thanks @John_w that is a huge help. Recording the procedure is simple enough, can you coach me through where to insert that code properly? Removing the printRange and replacing was straight forward enough. I recorded this per your instruction:

' RectangleRoundedCorners10_Click Macro
'

'
    Range("A1:I110").Select
    Application.WindowState = xlNormal
    Windows("Gilbert Doubletree ROM - RDC 2022.xlsx").Activate
    Windows("rdc. Procurement Prime Budget Template V1.0 22_0121.xlsx").Activate
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "S:\Sourcing and Procurement\Templates\rdc. Procurement Prime Budget Template V1.0 22_0121.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    Range("L7").Select

 

[John_w]

Your code shows at least 2 workbooks open. Do you want range A1:I110 in the "rdc. Procurement Prime Budget Template V1.0 22_0121.xlsx" workbook to be saved as a PDF? What is the sheet name?

Do you want the macro to be in a separate workbook (.xlsm file) or in the "rdc. Procurement Prime Budget Template V1.0 22_0121" workbook, in which case you'll need to save that workbook as a .xlsm file instead of .xlsx?

 

[cdg786]

Interesting. I want the A1:I110 of Sheet 3 named PO-V1 to export only. I want to macro to apply each time the workbook is used and/or saved under a new name. Sheet 3 (PO-V1) will be copied to become multiple PO's (i.e. PO-V2, PO-V3, etc...). Thank you again for your help.

 

[John_w]

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

 

[cdg786]

Thank you for your help! Email is sending properly. However iIt is sending a .xlsm version of the first sheet in the workbook, rather than the identified range in the active sheet. Current code as below.

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:I40")
        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 bid request. 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

Thanks again!

 

[John_w]

To fix the 'User-defined type not defined' error, click Tools -> References in the VBA editor and tick Microsoft Outlook nn.00 Object Library, where nn.00 is your Outlook version. The code show now compile without error.

However iIt is sending a .xlsm version of the first sheet in the workbook, rather than the identified range in the active sheet.

I don't see how the code is sending a .xlsm file, nor the first sheet.

.Attachments.Add PDFfile attaches a PDF file to the email, where PDFfile is created by the PDFrange.ExportAsFixedFormat and PDFrange is defined by the range .ActiveSheet.Range("A1:I40") in the active workbook.

 

[smozgur]

Thank you for your help! Email is sending properly. However iIt is sending a .xlsm version of the first sheet in the workbook, rather than the identified range in the active sheet. Current code as below.

The marked solution post has been switched to the actual answer post in this thread.

 

[cdg786]

To fix the 'User-defined type not defined' error, click Tools -> References in the VBA editor and tick Microsoft Outlook nn.00 Object Library, where nn.00 is your Outlook version. The code show now compile without error.


I don't see how the code is sending a .xlsm file, nor the first sheet.

.Attachments.Add PDFfile attaches a PDF file to the email, where PDFfile is created by the PDFrange.ExportAsFixedFormat and PDFrange is defined by the range .ActiveSheet.Range("A1:I40") in the active workbook.

Thanks for all of your help. I fixed it. My main excel file was saved as an xlsm file rather than xls. Fixed it. All working as it should.