Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
to Ron de Bruin Please
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
to Ron de Bruin Please
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
to Ron de Bruin Please
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
to Ron de Bruin Please
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
to Ron de Bruin Please
Hello Ron From Steved
All's fine, Thankyou. "Steved" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ron de Bruin | Excel Programming | |||
Q4 Ron de Bruin | Excel Programming | |||
Ron de Bruin (ADO help) | Excel Programming | |||
? for Ron de Bruin | Excel Programming | |||
for Ron de Bruin | Excel Programming |