Mark Drelinger
asked on
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.Appl ication")
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@SchmidtEquipmen t.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
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
End With
' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Appl
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@SchmidtEquipmen
.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
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?
The best source for vba code to email from Excel is Ron de Bruin. His site contains examples for most needs
ASKER
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.
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?
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?
ASKER
Single pdf would be better, but two separate pdf would also work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
No problem. I'm glad I could be of help.