Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

I need vba in excel to email worksheet as pdf attachment

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
drelinger
Asked:
drelinger
  • 4
  • 3
1 Solution
 
MichaelBusiness AnalystCommented:
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
 
Roy CoxGroup Finance ManagerCommented:
The best source for vba code to email from Excel is Ron de Bruin. His site contains examples for most needs
0
 
drelingerAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MichaelBusiness AnalystCommented:
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
 
drelingerAuthor Commented:
Single pdf would be better, but two separate pdf would also work.
0
 
MichaelBusiness AnalystCommented:
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
 
drelingerAuthor Commented:
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
 
MichaelBusiness AnalystCommented:
No problem. I'm glad I could be of help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now