Link to home
Start Free TrialLog in
Avatar of Mark Drelinger
Mark DrelingerFlag for United States of America

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_PDF()
'This statement Saves the Full Doc (Balance Sheet and Income Statement) as a PDF File to \\fileserver\Admin\Monthend 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.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 = "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
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark Drelinger

ASKER

thank you...