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 ?Attachment 17735
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.
Code:'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
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 ?
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?
My bad, it's 3.You are right.I want an outlook email(1 email) with 3 separate PDF files in the attachment.
Getting late so I did not test this much. Change the string values to suit. Be sure to set the Outlook object as commented.
Code:'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
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 ?
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.
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 ?
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.
Code:ws.UsedRange.ExportAsFixedFormat xlTypePDF, fn