ExcelBanter - View Single Post

Steved

Thankyou

"Ron de Bruin" wrote:

Send me the workbook and I look at it this evening Steve

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Steved" wrote in message ...
Hello Ron from Steved

Ron I'm getting an error message What have I not done please.
I Copied the Graph from a Worksheet to a new book but got the below

System Error &H80070057 (-2147024809). The Parameter is incorrect

Sub SaveSend_Embedded_Chart()
'Working in 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim Fname As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

'fill in the file path/name of the gif file
Fname = "C:\Steve's Folder\TestGraph.gif"

'if you hold down the CTRL key when you select the chart
'in 2000-2003 you see the name in the name box(formula bar)

ActiveWorkbook.Worksheets("TestGraph").ChartObject s("TestGraph").Chart.Export
_
Filename:=Fname, FilterName:="GIF"

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Test Graph"
.Body = "Hi there"
.Attachments.Add Fname
.Send 'or use .Display
End With
On Error GoTo 0

Kill Fname
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

"Ron de Bruin" wrote:

Hi Steve

See this Outlook example
http://www.rondebruin.nl/mail/folder2/chart.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Steved" wrote in message ...
Hello Ron from Steved

Is it possible please to change the below to email a graph Worksheet because
If I send it as is I'm getting an error message from Outlook telling me it
could not be sent. Thanks for taking timeout on my issue.

Sub AccidentGraph()
'Working in 97-2007
Dim wb As Workbook
Dim shName As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

shName = Array("AccidentGraph")
Addr = ")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xls": FileFormatNum = 56
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(shName) To UBound(shName)

TempFileName = "Sheet " & shName(N) & " " & Format(Now, "dd-mmm-yy
h-mm-ss")

ThisWorkbook.Sheets(shName(N)).Copy
Set wb = ActiveWorkbook

With wb.Worksheets(1).Cells
.Value = .Value
End With

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"Accident Graph From Steve Dee Regional Office"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub