Excel/formulas

Advertisement


Question
Tom

I have this code below that works fine allowing us to click a maco to run this code.  question is - is there way we can have it create another worksheet to be included in the one that this email will have created, currently it creates the worksheet only into a pdf file which opens up an outlook and attaches that worksheet as a pdf file.

we wanted to add the worksheet named OwnershipB and have them both in the same email.

here is the code:

Sub SendPDF500PA()
' Create PDF of active sheet and send as attachment.


   Dim Sh1 As Worksheet
   Dim strPath As String, strFName As String
   Dim OutApp As Object, OutMail As Object
   
   
       Set bk1 = ThisWorkbook
       bk1.Activate
    
    
       Set Sh1 = bk1.Worksheets("RGNPA")
    
    'Create PDF of active sheet only
   strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"
   
   strFFName = Sh1.Range("E20")
   strFName = Sh1.Range("E20") & " - " & "Purchase Agreement" & ".pdf"
   strEname = Sh1.Range("P29")

   strPName = Sh1.Range("G21")
   strDPName = Sh1.Range("E3")
   strOName = Sh1.Range("O28")

   strCCName = Sh1.Range("P28")
   
   
       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       strPath & strFName, Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       
    'Set up outlook
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
    'Create message
   On Error Resume Next

   With OutMail
       .To = strEname 'Insert required address here ########
       .CC = strCCName
       .BCC = ""
       .Subject = strFFName & " Purchase Agreement"
       .Body = vbNewLine & vbNewLine & _
       "Please find attached the Purchase Agreement, " & vbNewLine & _
       "reflecting all figures pertaining to this purchase dated, " & vbNewLine & _
       Format(strDPName, "mmmm dd yyyy") & "." & vbNewLine & vbNewLine & _
       "Please review, sign and return either by email or fax." & vbNewLine & vbNewLine & vbNewLine & _
       "Thank you for your Business!" & vbNewLine & vbNewLine & _
        strOName



        
       .Attachments.Add strPath & strFName
       .Display   'Use only during debugging ##############################
       '.Send      'Uncomment to send e-mail ##############################
   End With
    'Delete any temp files created
   Kill strPath & strFName
   On Error GoTo 0
   Set OutMail = Nothing
   Set OutApp = Nothing

  Application.ScreenUpdating = True
  
    success.Show
       
End Sub

any help would be appreciated!

Tony

Answer
Tony,

Untested, but this should get you started.


Sub SendPDF500PA()
' Create PDF of active sheet and send as attachment.


   Dim Sh1 As Worksheet
   Dim strPath As String, strFName As String, strFNameXLS As String
   Dim OutApp As Object, OutMail As Object, bkXLS As Workbook
   Dim bk1 As Workbook
   
   
       Set bk1 = ThisWorkbook
       bk1.Activate
    
    
       Set Sh1 = bk1.Worksheets("RGNPA")
    
    'Create PDF of active sheet only
   strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"
   
   strFFName = Sh1.Range("E20")
   strFName = Sh1.Range("E20") & " - " & "Purchase Agreement" & ".pdf"
   strEname = Sh1.Range("P29")

   strPName = Sh1.Range("G21")
   strDPName = Sh1.Range("E3")
   strOName = Sh1.Range("O28")

   strCCName = Sh1.Range("P28")
   
   strFNameXLS = OwnershipB.xlsx
   ActiveSheet.Copy
   Set bkXLS = ActiveWorkbook
   Application.DisplayAlerts = False
   bkXLS.SaveAs Filename:=sPath & strFNameXLS, FileFormat:=xlOpenXMLWorkbook
   bkXLS.Close SaveChanges:=False
   Application.DisplayAlerts = True
   
       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       strPath & strFName, Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       
    'Set up outlook
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
    'Create message
   On Error Resume Next

   With OutMail
       .To = strEname 'Insert required address here ########
       .CC = strCCName
       .BCC = ""
       .Subject = strFFName & " Purchase Agreement"
       .Body = vbNewLine & vbNewLine & _
       "Please find attached the Purchase Agreement, " & vbNewLine & _
       "reflecting all figures pertaining to this purchase dated, " & vbNewLine & _
       Format(strDPName, "mmmm dd yyyy") & "." & vbNewLine & vbNewLine & _
       "Please review, sign and return either by email or fax." & vbNewLine & vbNewLine & vbNewLine & _
       "Thank you for your Business!" & vbNewLine & vbNewLine & _
        strOName



        
       .Attachments.Add strPath & strFName
       .Attachments.Add strPath & strFNameXLS
       .Display   'Use only during debugging ##############################
       '.Send      'Uncomment to send e-mail ##############################
   End With
    'Delete any temp files created
   Kill strPath & strFName
   Kill strPath & strFNameXLS
   On Error GoTo 0
   Set OutMail = Nothing
   Set OutApp = Nothing

  Application.ScreenUpdating = True
  
    success.Show
       
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.