Consulting

Results 1 to 10 of 10

Thread: Create PDF of Workbook or Individual sheets by last used row & column and send email

  1. #1
    VBAX Regular
    Joined
    Aug 2014
    Posts
    26
    Location

    Exclamation Create PDF of Workbook or Individual sheets by last used row & column and send email

    Hello Experts,
    I have attached the file for reference. I need a macro that will convert the used range(last used row and column) of the three sheets of this workbook into PDF and attach it by their sheet name into an outlook email and send it. I will add the mail body info and mail to list later. How do I do that in VBA ?Create PDF and Email.xlsx

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    For the 3 methods, and sample code, see below.

    Solutions:
    1. Add a new workbook and a sheet for each. Then close new workbook without saving.
    2. Create a pdf for each sheet and then use a 3rd party program to merge the pdf files.
    a. Merge methods: Acrobat (not reader), pdfCreator, pdfSam, etc.
    3. Add a sheet in existing workbook and copy data to it.

    Which do you prefer?

    The code is easily modified to use method 3 for your 3 worksheets. Since your data is formatted the same, method 3 may suffice.

    'http://www.excelforum.com/excel-programming-vba-macros/1164808-print-a-range-to-pdf-multiple-times.html
    Sub Main()  
      Dim i%, r As Range
      Dim glb_origCalculationMode%
      
      glb_origCalculationMode = Application.Calculation
      With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Cursor = xlWait
        .StatusBar = "Building PDF file in Temp Folder..."
        .EnableCancelKey = xlErrorHandler
      End With
      
      Worksheets.Add after:=Worksheets(Worksheets.Count)
      
      With Worksheets(1)
        For i = 1 To 10
          .Range("A1").Value = i
          .Range("A1:M20").Copy
          If i = 1 Then
            Set r = Range("A1")
            Else
              Set r = Cells(Rows.Count, "A").End(xlUp).Offset(1)
          End If
          r.PasteSpecial xlPasteColumnWidths
          r.PasteSpecial xlPasteFormats
          r.PasteSpecial xlValues
          ActiveWindow.SelectedSheets.HPageBreaks.Add Before:= _
            Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Next i
        
        ActiveSheet.ExportAsFixedFormat xlTypePDF, _
          Environ("temp") & "\Sheets1-10.pdf"
          
        Worksheets(Worksheets.Count).Delete
      End With
      
      With Application
        .Calculation = glb_origCalculationMode
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .CalculateBeforeSave = True
        .Cursor = xlDefault
        .StatusBar = False
        .EnableCancelKey = xlInterrupt
      End With
    End Sub

  3. #3
    VBAX Regular
    Joined
    Aug 2014
    Posts
    26
    Location
    Method 3 is fine.
    But when I copy and pasted the macro in a module, it gave me an error of 'Method "Add" of object 'Sheets' failed'. Also, I need to attach these two pdf's into an outlook email and send it..how do I do that ?

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Now I am more confused. You have 3 worksheets, not 2. Where does the 2 pdf files come from?

    Did you want an email with 3 separate PDF files, 3 emails with 1 pdf in each, 1 email with all 3 sheets in 1 pdf, or?

  5. #5
    VBAX Regular
    Joined
    Aug 2014
    Posts
    26
    Location
    My bad, it's 3.You are right.I want an outlook email(1 email) with 3 separate PDF files in the attachment.

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Getting late so I did not test this much. Change the string values to suit. Be sure to set the Outlook object as commented.

    'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
    'http://www.rondebruin.nl/win/s1/outlook/signature.htm
    
    
    'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
    Sub Main()
      Dim olApp As Outlook.Application, olMail As Outlook.MailItem
      Dim sTo$, sSubject$, fn$, ws As Worksheet, sBody$
      
      sTo = "ken@gmail.com"
      sSubject = "All worksheets from: " & ThisWorkbook.Name
      sBody = "Your files are attached. If you have questions, " _
        & "please contact me at (555) 555-1234."
      
      Set olApp = New Outlook.Application
      Set olMail = olApp.CreateItem(olMailItem)
      With olMail
        .To = sTo
        .Subject = sSubject
        .Body = sBody
        For Each ws In Worksheets
          fn = Environ("temp") & "\" & ws.Name & ".pdf"
          ws.ExportAsFixedFormat xlTypePDF, fn
          .Attachments.Add fn
          'kill fn
        Next ws
        '.Display
        .Send
      End With
      
      Set olMail = Nothing
      Set olApp = Nothing
    End Sub

  7. #7
    VBAX Regular
    Joined
    Aug 2014
    Posts
    26
    Location
    Ken,
    It works great for the file I sent! Awesome!

    But here is where I am facing issues:
    1.) When i try to copy paste the same program in a different workbook's module which has different worksheet name( Also 3 worksheets), it does not work (The error is "Invalid Procedure call or argument").
    2.) The only difference is that these worksheets have a data range of A1:K200. Any thoughts ?

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Did you set the Outlook Object reference in the other workbook? It is easy enough to use late binding if that is that big of a deal.
    Last edited by Kenneth Hobs; 12-01-2016 at 04:54 PM.

  9. #9
    VBAX Regular
    Joined
    Aug 2014
    Posts
    26
    Location
    Did u intend to type MSOutlook Object reference ? Yes, I went to tools--->reference and checked "MSoutlook Object 15.0". Is there any other way to make this applicable for multiple workbooks ( all containing three sheets but may have varying ranges ?

  10. #10
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Yes. Of course that code acts on the ActiveWorkbook. Whatever workbook the macro is played from, it must have the Outlook object set.

    Range can not cause your problem. It might be an issue if not set as the print range. You can do that in the macro or use something like the ws.UsedRange for the Export object rather than just ws.
     ws.UsedRange.ExportAsFixedFormat xlTypePDF, fn

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •