Mail multiple charts instead of multi. sheets

[Guest]

I have tried mailing sheets using the code at the bottom.
Code taken from Ron DeBruin's web site:
http://www.rondebruin.nl/sendmail.htm

It works fine for sheets. However, I am interested in
sending charts instead of sheets.

I can send individual charts but would like to group them
into multiple pages of charts in one workbook.

Suggestions?

Ken

Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
Dim wb As Workbook
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail"). _
Cells(1, a).Value = "" Then Exit Sub
Application.ScreenUpdating = False
strdate = Format(Now, "dd-mm-yy h-mm-ss")
With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), _
..Cells(Rows.Count, a + 1).End(xlUp))
End With
last = ThisWorkbook.Sheets("mail").Cells
(Rows.Count, a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells
(shname, a).Value
Next shname
ThisWorkbook.Worksheets(Arr).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, ThisWorkbook.Sheets
("mail").Cells(1, a + 2).Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Next a
End Sub

 

[keepitcool]

Ken

You haven't taken one of Ron's simpler samples
This one is a bit more complex than some others


Would have been nice to have the code documented...
now you have to first analyse what it does.

Apparently the worksheet "mail" contains:
the addresses to be mailed in COl B (step3)
the sheets to be mailed in COL A (step3)

Then it start selecting WORKSHEETS instead of SHEETS
thus it wont find charts!

Change this line
'ThisWorkbook.Worksheets(Arr).Copy
Into
ThisWorkbook.Sheets(Arr).Copy


and Ron's code should work fine.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >




I have tried mailing sheets using the code at the bottom.
Code taken from Ron DeBruin's web site:
http://www.rondebruin.nl/sendmail.htm

It works fine for sheets. However, I am interested in
sending charts instead of sheets.

I can send individual charts but would like to group them
into multiple pages of charts in one workbook.

Suggestions?

Ken

 

[Mark Bigelow]

One way to do that is to export the chart as a gif and then create a
mail object that sends that gif as an attachment. You could embed it in
the body of the email using HTML code and setting the type of email as
HTML.

Let me know if you need/want details.

Mark