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,

 

[John_w]

Try this code in a standard module. You must set a reference to the Outlook nn.0 Object Library, via Tools -> References in the VBA editor.

Public Sub Send_Emails_For_Print_Areas()

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

    Set OutApp = New Outlook.Application
    
    'PDF file for each print range is temporarily saved in same folder as this workbook
    
    destFolder = ThisWorkbook.Path & "\"
    If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
    
    For Each wsh In ActiveWorkbook.Worksheets
    
        If wsh.PageSetup.PrintArea <> "" Then
        
            'Save print area for this sheet as a PDF file
            
            PDFfile = destFolder & wsh.Name & ".pdf"
            Set printRange = Range(wsh.PageSetup.PrintArea)
            printRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
            'Send email to address in cell A1 of this sheet with PDF file attached
            
            Set OutMail = OutApp.CreateItem(olMailItem)
            With OutMail
                .To = wsh.Range("A1").Value
                .Subject = "Subject line"
                .Body = "Body text"
                .Attachments.Add PDFfile
                .send
            End With

            'Delete the temporary PDF file
            
            Kill PDFfile
        
        End If

    Next
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

 

[united2017]

Thanks John, the above code works well, just a few tweaks required please:
- When I run the macro, all the worksheets are e-mailed out, is it possible to only e-mail the active worksheet, as the rest have not been completed?
- The file name goes as the worksheet name, i.e. 'Sheet1', is it possible to have that changed to a standard cell on each worksheet?

Many Thanks

 

[John_w]

With the two tweaks:

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 A2
        
        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 A1 of active sheet with PDF file attached
        
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = ActiveSheet.Range("A1").Value
            .Subject = "Subject line"
            .Body = "Body text"
            .Attachments.Add PDFfile
            .send
        End With

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

 

[united2017]

You are a legend, John! Thank you very much.

 

[richardinchicago]

John--Thanks for posting this...I'm trying to do something similar with VB code. I want to save a worksheet print area to a pdf and then email that opens outlook. I further tweaked the above code but when I run the above code I get an error message. I don't have the email in cell A1 nor the document name is cell A2. Can you help? Thanks in advance and much appreciated.

 

[John_w]

I further tweaked the above code but when I run the above code I get an error message. I don't have the email in cell A1 nor the document name is cell A2. Can you help? Thanks in advance and much appreciated.

Post your code. What is the exact error message and which line causes it?

Here is the code changed to hard-code the email address and file name, instead of getting them from A1 and A2:

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
        
        PDFfile = destFolder & "My Data.pdf"
        Set printRange = Range(ActiveSheet.PageSetup.PrintArea)
        printRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
        'Send email with PDF file attached
        
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = "email.address@email.com" ' CHANGE THIS
            .Subject = "Subject line"
            .Body = "Body text"
            .Attachments.Add PDFfile
            .send
        End With

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

 

[richardinchicago]

The error I get now is Kill PDFfile

Here is the code I inserted:

Sub Send_PDF_Via_Email()
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

PDFfile = destFolder & "My Data.pdf"
Set printRange = Range(ActiveSheet.PageSetup.PrintArea)
printRange.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Send email with PDF file attached

Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.Subject = ""
.Body = ""
.Attachments.Add PDFfile

End With
'Delete the temporary PDF file

Kill PDFfile

Set OutMail = Nothing
Set OutApp = Nothing

End If

End Sub

 

[John_w]

Ensure you have the trailing back slash on the folder path:

    destFolder = ThisWorkbook.Path & "\"
    If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"

Please use CODE tags.

 

[richardinchicago]

Thanks I'll give it a try. Completely a novice here. You've been immensely helpful!