Solved

I need vba in excel to email worksheet as pdf attachment

Posted on 2016-10-24
8
101 Views
Last Modified: 2016-10-29
I have code (Pasted further below) which sends out an email with a .pdf attachment of the active worksheet.
However, I need it to send an email with a page range of two different worksheets (shown below).
I'm looking to merge the two functions to email a .pdf of the correct pages:

code that prints the correct page range:
Worksheets("BS").PrintOut From:=1, To:=4
Worksheets("PL").PrintOut From:=24, To:=48

'code that send active worksheet as pdf:
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "Drelinger@SchmidtEquipment.com" ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub
0
Comment
Question by:drelinger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 41857202
Do you know the cell ranges of pages 1 to 4 of worksheet BS and of pages 24 to 48 of worksheet PL, and are they fixed?
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41857215
The best source for vba code to email from Excel is Ron de Bruin. His site contains examples for most needs
0
 

Author Comment

by:drelinger
ID: 41857322
the cell ranges will vary based on user selections.  Columns are hidden / displayed based on financial statement month's selected. So, if the user selects January, the range is A0:N1321. But it is different for each month selected.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 6

Expert Comment

by:Michael
ID: 41857356
Ok, thank you.

One more question. Does it need to be one pdf file containing both the BS and PL? Or is a solution with two separate pdf files for BS and PL as attachments to the same email also possible?
0
 

Author Comment

by:drelinger
ID: 41857500
Single pdf would be better, but two separate pdf would also work.
0
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 41858121
The code below should create two pdf files (PdfFileBS & PdfFilePL) and add them to the email message.
I couldn't fully test the code, so let me know if you run into any issues.

PS. One comment says "Not sure for what the Title is"; it's used as the title of the email message ;)

'code that send active worksheet as pdf:
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, PdfFileBS As String, PdfFilePL As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFileBS = PdfFile & "_" & "BS" & ".pdf"
  PdfFilePL = PdfFile & "_" & "PL" & ".pdf"
 
  ' Export Worksheets("BS") as PDF
  With Worksheets("BS")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFileBS, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=1, To:=4, OpenAfterPublish:=False
  End With

  ' Export Worksheets("PL") as PDF
  With Worksheets("PL")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFilePL, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=24, To:=28, OpenAfterPublish:=False
  End With

 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "Drelinger@SchmidtEquipment.com" ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFileBS
    .Attachments.Add PdfFilePL
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Open in new window

0
 

Author Closing Comment

by:drelinger
ID: 41864311
I didn't mean to abandon this question. I typed comments the other day buy must not have clicked submit. Your solution was perfect.  Flawless. thank you for teaching me something new. Regards, Mark.
0
 
LVL 6

Expert Comment

by:Michael
ID: 41864945
No problem. I'm glad I could be of help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question