Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting an Excel file to PDF Q
I use Ron de Bruin's excellent code to e-mail out Excel sheets as a
PDF. I've recently added Charts to these Excel sheet and I'm finding they are not appearing on the PDF sheet and I can't figure it out. I've ranged the Area to include the Chart, it produces 2 pages but where the chart should be on the 2nd page, its blank. Any ideas? Sub Mail_Area1() Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim sh As Worksheet Dim cell As Range Dim strbody As String Dim FilenameStr As String Dim TempWb As Workbook Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(olMailItem) Set Sourcewb = ActiveWorkbook If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _ & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") < "" Then Sourcewb.Sheets(Array("AM1")).Copy Set TempWb = ActiveWorkbook 'Change all cells in the worksheets to values if you want With TempWb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False For Each cell In TempWb.Sheets("AM1") _ .Columns("BJ").Cells.SpecialCells(xlCellTypeConsta nts) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) FilenameStr = Application.DefaultFilePath & "\" & "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm") & ".pdf" ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=FilenameStr, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False 'Close the new workbook you create file without saving TempWb.Close False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) For Each cell In ThisWorkbook.Sheets("AM1").Range("BV1:BV15") strbody = strbody & cell.Value & vbNewLine Next On Error Resume Next With OutMail .To = strto .CC = "" .BCC = "" .Subject = ThisWorkbook.Sheets("AM1").Range("BJ1").Value .Body = strbody .Attachments.Add FilenameStr .ReadReceiptRequested = False .Importance = 1 .DeferredDeliveryTime = ThisWorkbook.Sheets("AM1").Range("BQ1").Value .SendUsingAccount = OutApp.Session.Accounts.Item(1) .Send End With On Error GoTo 0 'Delete the file you send Kill FilenameStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With Else MsgBox "PDF add-in Not Installed" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting an Excel file to PDF Q
Maybe you should consider using a PDF printer instead of a AddIn
for example you could use CutePDF writter ... I think is gratis ... And then change your macro to print your range and your objects for me it just works great... Well, luck. LMiguel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONVERTING EXCEL FILE TO DBF FILE IN OFFICE 2007 | Excel Programming | |||
Converting an Excel File to CSV | New Users to Excel | |||
Converting Excel file to text delimited file | Excel Discussion (Misc queries) | |||
Converting excel file to tab delimited (txt) file | Excel Discussion (Misc queries) | |||
converting to excel file | Excel Programming |