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

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

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

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)

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

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

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)