CODEWITHSUNDEEP
excelvbaoutlook
Chadi N
Chadi N

Reputation: 441

Export worksheet as PDF and attach to Outlook e-mail

I found this code online and it works, but whenever it exports the worksheet as PDF, the PDF have about 4000 pages. I just need the sheet called "Grille".

Sub SendWorksheet_AsPDFAttachment_OutlookEmail()
    Dim objFileSystem As Object
    Dim strTempFile As String
    Dim oApp As Object
    Dim oMail As Object

   
    'Specify the worksheet name
    Sheets("Grille").Activate
    ActiveSheet.UsedRange.Select
    ThisWorkbook.Sheets(Array("Grille")).Select


    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    strTempFile = objFileSystem.GetSpecialFolder(2).Path & "\" & ActiveSheet.Name & " in " & ThisWorkbook.Name & ".pdf"


    'export the specific worksheet as PDF
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


    'Create a new email
    Set oApp = CreateObject("Outlook.application")
    Set oMail = oApp.CreateItem(0)


    'Attach the PDF file
    objMail.Attachments.Add strTempFile
    objMail.Display '==>display this email


    'Delete the temp PDF file
    objFileSystem.DeleteFile (strTempFile)
End Sub

Upvotes: 0

Views: 94

Answers (1)

norie
norie

Reputation: 9857

Instead of exporting Selection as a PDF export the sheet.

 'export the specific worksheet as PDF
    ThisWorkbook.Sheets("Grille").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

Upvotes: 1

Related Questions