Mark Drelinger
asked on
Adjust VBScript to merge two created PDF documents into one before attaching to email and sending
I have a great script that creates 2 PDF documents from Excel, and sends them via email.
But I'd like the two .pdf documents to be combined into one before attaching to the email.
Can anyone suggest a solution ?
Current code (thanks to Michael on Experts-Exchange):
Sub SaveFinancialStatements_PD F()
'This statement Saves the Full Doc (Balance Sheet and Income Statement) as a PDF File to \\fileserver\Admin\Monthen d Reports\A_DropBox\
response = MsgBox("Are You Ready to print this Report?" & vbNewLine & vbNewLine & "Have you Refreshed the Data from EQUIP?" & vbNewLine & vbNewLine & "Have you Changed the Report Date?", vbYesNo)
If response = vbNo Then
MsgBox ("Macro Ending")
Exit Sub
End If
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
Dim st As Range
Set st = Range("SendTo")
Dim dt As Range
Set dt = Range("report_date")
' 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_" & dt & ".pdf"
PdfFilePL = PdfFile & "_PL_" & dt & ".pdf"
' 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:=1, To:=23, 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 = "Schmidt Financial Statement - PDF"
.To = st ' <-- Put email of the recipient here
' .CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Schmidt Equipment Financial Statements are 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
But I'd like the two .pdf documents to be combined into one before attaching to the email.
Can anyone suggest a solution ?
Current code (thanks to Michael on Experts-Exchange):
Sub SaveFinancialStatements_PD
'This statement Saves the Full Doc (Balance Sheet and Income Statement) as a PDF File to \\fileserver\Admin\Monthen
response = MsgBox("Are You Ready to print this Report?" & vbNewLine & vbNewLine & "Have you Refreshed the Data from EQUIP?" & vbNewLine & vbNewLine & "Have you Changed the Report Date?", vbYesNo)
If response = vbNo Then
MsgBox ("Macro Ending")
Exit Sub
End If
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
Dim st As Range
Set st = Range("SendTo")
Dim dt As Range
Set dt = Range("report_date")
' 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_" & dt & ".pdf"
PdfFilePL = PdfFile & "_PL_" & dt & ".pdf"
' PdfFileBS = PdfFile & "_BS.pdf"
' PdfFilePL = PdfFile & "_PL.pdf"
' Export Worksheets("BS") as PDF
With Worksheets("BS")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFileBS, Quality:=xlQualityStandard
End With
' Export Worksheets("PL") as PDF
With Worksheets("PL")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFilePL, 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 = "Schmidt Financial Statement - PDF"
.To = st ' <-- Put email of the recipient here
' .CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Schmidt Equipment Financial Statements are 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER