S
Seanie
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(xlCellTypeConstants)
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
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(xlCellTypeConstants)
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