View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Steved Steved is offline
external usenet poster
 
Posts: 519
Default to Ron de Bruin Please

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