Solved

I need vba in excel to email worksheet as pdf attachment

Posted on 2016-10-24
8
32 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
  • 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 17

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now