Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- thepython ------------------------------------------------------------------------ thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563 View this thread: http://www.excelforum.com/showthread...hreadid=502126 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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. . -- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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'? -- thepython ------------------------------------------------------------------------ thepython's Profile: http://www.excelforum.com/member.php...o&userid=30563 View this thread: http://www.excelforum.com/showthread...hreadid=502126 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Excel attachment, but wait till Outlook closes
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" <johnfulkersons.21spjb_1137529202.6222@excelforu m-nospam.com wrote in message news:johnfulkersons.21spjb_1137529202.6222@excelfo rum-nospam.com... 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. . -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 03 - Send Attachment (can't minimize Outlook window) | Excel Discussion (Misc queries) | |||
Force Macro to wait till Refreshall is done | Excel Programming | |||
Send current Excel File as an attachment using Outlook 2000 | Excel Programming | |||
Send Outlook Attachment within Excel | Excel Programming | |||
What 's wrong with the coding? -- about using outlook to send attachment in excel | Excel Programming |