Solved

I need vba in excel to email worksheet as pdf attachment

Posted on 2016-10-24
8
45 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 18

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 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