Send Excel attachment, but wait till Outlook closes

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.

Hi thepython

Outlook or Outlook Express







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


"thepython" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
.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" <[email protected]> wrote in message news:[email protected]...
> Hi thepython
>
> Outlook or Outlook Express
>
>
>
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "thepython" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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

I have spent 17 hours and read four excel reference books. No luck.

The probelm...when I type in a date in a cell, a five digit number comes up. I look up at the top of the screen and I cannot find a formula anywhere. I try to delete it and nothing happens. The five digit number is in all of the cells in two rows. When I change the date, the five digit number appears to change. I am lost. [email protected].

Dates are stored as 5 digit numbers.

from the immediate window:

? date
01/17/2006
? clng(date)
38734
? format(38734,"mmm dd, yyyy")
Jan 17, 2006
? format(38734,"mm/dd/yyyy")
01/17/2006

select any date in Excel and format the cell as General
(format=>Cells=>Number format, select general) and you will see a five digit
number unless it is an old date, then the digits may be fewer.

the five digit number represents the number of days since the base date.
For windows this is the beginning of 1900.

2.5 would 12 Noon on 2 Jan 1900

3.75 would be 3 Jan 1900 at 6:00 PM

If the number is an integer, time is not represented or it is midnight.

But Chip Pearson explains it all in much more detail:

http://www.cpearson.com/excel/datetime.htm

--
Regards,
Tom Ogilvy


"johnfulkerson@s"
<[email protected]> wrote in
message news:[email protected]...
>
> I have spent 17 hours and read four excel reference books. No luck.
>
> The probelm...when I type in a date in a cell, a five digit number
> comes up. I look up at the top of the screen and I cannot find a
> formula anywhere. I try to delete it and nothing happens. The five
> digit number is in all of the cells in two rows. When I change the
> date, the five digit number appears to change. I am lost.
> [email protected].
>
>
> --
> johnfulkerson@s
> ------------------------------------------------------------------------
> johnfulkerson@s's Profile:
http://www.excelforum.com/member.php...o&userid=30572
> View this thread: http://www.excelforum.com/showthread...hreadid=502126
>