Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
Could someone please help me with the folowing code? It is called
from a button and attaches a file to an email. It works fine except two things. 1 It will get an error on Set EmailItem =OL.CreateItem(OLMailItem) if outlook is not already open. How can I change it so outlook does not have to be already open? 2 Application.Quit closes the current workbook, but does not close excel completely. I need it to shut down completely. Sub Button6_Click() Dim OL As Object Dim EmailItem As Object Dim FileName As String 'Shut Down Screen and Events Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True ' Setup OutLook Object Application.EnableEvents = True Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(OLMailItem) FileName = "Attachment.xls" ' Load Email ActiveWorkbook.SaveAs "C:\" & FileName On Error Resume Next With EmailItem .Subject = ActiveSheet.Name .Body = ActiveSheet.Name .Importance = 2 ' 0 = Low 1 = Normal 2 = High .Attachments.Add "C:\" & FileName .Display ' Load The Email End With 'Shut down Excel ActiveWorkbook.Close False Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True Kill "C:\" & FileName Set OL = Nothing Set EmailItem = Nothing Application.Quit End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
If you use Late binding use
CreateItem(0) -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ... Could someone please help me with the folowing code? It is called from a button and attaches a file to an email. It works fine except two things. 1 It will get an error on Set EmailItem =OL.CreateItem(OLMailItem) if outlook is not already open. How can I change it so outlook does not have to be already open? 2 Application.Quit closes the current workbook, but does not close excel completely. I need it to shut down completely. Sub Button6_Click() Dim OL As Object Dim EmailItem As Object Dim FileName As String 'Shut Down Screen and Events Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True ' Setup OutLook Object Application.EnableEvents = True Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(OLMailItem) FileName = "Attachment.xls" ' Load Email ActiveWorkbook.SaveAs "C:\" & FileName On Error Resume Next With EmailItem .Subject = ActiveSheet.Name .Body = ActiveSheet.Name .Importance = 2 ' 0 = Low 1 = Normal 2 = High .Attachments.Add "C:\" & FileName .Display ' Load The Email End With 'Shut down Excel ActiveWorkbook.Close False Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True Kill "C:\" & FileName Set OL = Nothing Set EmailItem = Nothing Application.Quit End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
I am not sure what you mean.
On Sun, 1 Oct 2006 20:42:01 +0200, "Ron de Bruin" wrote: If you use Late binding use CreateItem(0) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
Look at the Late binding info on one of my Outlook object model pages
http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ... I am not sure what you mean. On Sun, 1 Oct 2006 20:42:01 +0200, "Ron de Bruin" wrote: If you use Late binding use CreateItem(0) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
Add this line just before your Ap.Quit statement:
ActiveWorkbook.Saved = True It works for me. " wrote: Could someone please help me with the folowing code? It is called from a button and attaches a file to an email. It works fine except two things. 1 It will get an error on Set EmailItem =OL.CreateItem(OLMailItem) if outlook is not already open. How can I change it so outlook does not have to be already open? 2 Application.Quit closes the current workbook, but does not close excel completely. I need it to shut down completely. Sub Button6_Click() Dim OL As Object Dim EmailItem As Object Dim FileName As String 'Shut Down Screen and Events Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True ' Setup OutLook Object Application.EnableEvents = True Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(OLMailItem) FileName = "Attachment.xls" ' Load Email ActiveWorkbook.SaveAs "C:\" & FileName On Error Resume Next With EmailItem .Subject = ActiveSheet.Name .Body = ActiveSheet.Name .Importance = 2 ' 0 = Low 1 = Normal 2 = High .Attachments.Add "C:\" & FileName .Display ' Load The Email End With 'Shut down Excel ActiveWorkbook.Close False Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True Kill "C:\" & FileName Set OL = Nothing Set EmailItem = Nothing Application.Quit End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
Still does not work. I even put it on a blank workwook with nothing
but the button and the button code. Does the same on both computers here and at work. On Sun, 1 Oct 2006 13:29:01 -0700, JLGWhiz wrote: Add this line just before your Ap.Quit statement: ActiveWorkbook.Saved = True It works for me. " wrote: Could someone please help me with the folowing code? It is called from a button and attaches a file to an email. It works fine except two things. 1 It will get an error on Set EmailItem =OL.CreateItem(OLMailItem) if outlook is not already open. How can I change it so outlook does not have to be already open? 2 Application.Quit closes the current workbook, but does not close excel completely. I need it to shut down completely. Sub Button6_Click() Dim OL As Object Dim EmailItem As Object Dim FileName As String 'Shut Down Screen and Events Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True ' Setup OutLook Object Application.EnableEvents = True Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(OLMailItem) FileName = "Attachment.xls" ' Load Email ActiveWorkbook.SaveAs "C:\" & FileName On Error Resume Next With EmailItem .Subject = ActiveSheet.Name .Body = ActiveSheet.Name .Importance = 2 ' 0 = Low 1 = Normal 2 = High .Attachments.Add "C:\" & FileName .Display ' Load The Email End With 'Shut down Excel ActiveWorkbook.Close False Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True Kill "C:\" & FileName Set OL = Nothing Set EmailItem = Nothing Application.Quit End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA coding - Outlook and Shut down issue
I ran across this code posted on another site (see reference below)
that opens Outlook if it isn't running. This might give you what you need for #1. 'Get Outlook if it's running Set oOutlookApp = GetObject(, "Outlook.Application") If Err < 0 Then 'Outlook wasn't running, start it from code Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If This is posted on the Word MVP Site (http://word.mvps.org/FAQs/InterDev/SendMail.htm) by Astrid Zeelenberg (http://word.mvps.org/AboutMVPs/astrid_zeelenberg.htm). (I'm not sure what the proper etiquette is for referencing another's code, but we're all here to share, right?) As for #2, in the recent posts from this Google site, I just read one that talks about a similar issue. Don't know if that might get you on the right track... http://groups.google.com/group/micro...d56c4101871f17 Good luck! --Cinda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coding issue with Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 coding issue | Excel Discussion (Misc queries) | |||
outlook shut off, Is all info lost forever | Excel Discussion (Misc queries) | |||
Excel VBA coding - Outlook and Shut down issue | Excel Worksheet Functions | |||
What 's wrong with the coding? -- about using outlook to send attachment in excel | Excel Programming |