Re Excel 2002

I am using the following to open an Outlook new mail window with the
attached workbook:-

objExcel.Dialogs(xlDialogSendMail).Show strInvoiceEmailAddress, "Latest
invoice attached"

I first saved the file so that the attachment would get a more
meaningful name.

I then want to delete the saved file with Kill... but the code does not
wait until the Outlook window is closed.

My query is this: is it possible to give the attachmnet a different
name to the default "Book1.xls", or how do I pause the code so that I
can delete the saved file and not get a message to say the file is in
use?

Many thanks.


--
thepython
------------------------------------------------------------------------
thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563
View this thread: http://www.excelforum.com/showthread...hreadid=502126

Hi thepython

Outlook or Outlook Express







--
Regards Ron de Bruin
http://www.rondebruin.nl


"thepython" wrote in message
...

Re Excel 2002

I am using the following to open an Outlook new mail window with the
attached workbook:-

objExcel.Dialogs(xlDialogSendMail).Show strInvoiceEmailAddress, "Latest
invoice attached"

I first saved the file so that the attachment would get a more
meaningful name.

I then want to delete the saved file with Kill... but the code does not
wait until the Outlook window is closed.

My query is this: is it possible to give the attachmnet a different
name to the default "Book1.xls", or how do I pause the code so that I
can delete the saved file and not get a message to say the file is in
use?

Many thanks.


--
thepython
------------------------------------------------------------------------
thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563
View this thread: http://www.excelforum.com/showthread...hreadid=502126

Must go now

But for Outlook you can do this

Untested

Sub Mail_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
Dim Fname As String

Fname = "C:\newfilename.xls"
ThisWorkbook.SaveCopyAs Fname

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Attachments.Add Fname
.display 'or use .Send
End With

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi thepython

Outlook or Outlook Express







--
Regards Ron de Bruin
http://www.rondebruin.nl


"thepython" wrote in message
...

Re Excel 2002

I am using the following to open an Outlook new mail window with the
attached workbook:-

objExcel.Dialogs(xlDialogSendMail).Show strInvoiceEmailAddress, "Latest
invoice attached"

I first saved the file so that the attachment would get a more
meaningful name.

I then want to delete the saved file with Kill... but the code does not
wait until the Outlook window is closed.

My query is this: is it possible to give the attachmnet a different
name to the default "Book1.xls", or how do I pause the code so that I
can delete the saved file and not get a message to say the file is in
use?

Many thanks.


--
thepython
------------------------------------------------------------------------
thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563
View this thread: http://www.excelforum.com/showthread...hreadid=502126

Hi Ron,

I must apologise. I am using Outlook 2002.

Again, apologies as I should have also mentioned that I am creating an
instance of Excel from within Access 2002.

I have actually cured the problem by moving the Kill statement further
to the end of the procedure (like a fool , I was trying to
'Kill' the temporary file before using objExcel.Quit).

All is well now.

Rgds, thepython


--
thepython
------------------------------------------------------------------------
thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563
View this thread: http://www.excelforum.com/showthread...hreadid=502126