Send Excel attachment, but wait till Outlook closes

[thepython]

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.

 

[Ron de Bruin]

Hi thepython

Outlook or Outlook Express

 

[Ron de Bruin]

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 = "(e-mail address removed)"
.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

Hi thepython

Outlook or Outlook Express

 

[thepython]

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

 

[johnfulkerson@s]

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.
(e-mail address removed).

 

[thepython]

Hi John

How did you manage to get in on this thread with your query?

Have you tried formatting the 5 digit cells as 'Date'?

 

[Tom Ogilvy]

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"