Mailing from Excel and message question

  • Thread starter Thread starter ZABU
  • Start date Start date
Z

ZABU

I have a workbook that will email itself to a list of people and then
perform a series of actions in a macro. I have a couple of questions.

1. I am using the SendMail function and it allows me to list recipients and
put a subject in the subject line but will not allow me to add a message,
any ideas on how to add a message?

2. after the step above the macro performs a number of operations that take
some time (20-30 seconds). I would like to have a message box that tells
the user to wait while the operation takes place and then say the worksheet
will close in 5 seconds. The available message box choices I can find have
buttons that require the user to reply by pressing the OK button and the
macro will not continue until this is done. Is there a way to display only
a box with no buttons that contains a message? Also is there an easy way to
display a countdown timer for the 5 second box?

Thanks for you help in advance

ZABU
 
Sendmail provides a simple command interface, but what can be done is
limited. There is no provision for putting in a message.

you can use other methods. See these sites:

Dick Kusleika's site

http://www.dicks-clicks.com/excel/olSending.htm

Ron de Buin's site

http://www.rondebruin.nl/sendmail.htm

you can put up a modeless userform if your users are using xl2000 or later.
Then have you code unload it at the end.

Your code would have to do the counting down (and updating of the count in
the userform) - Excel VBA isn't multithreaded.
 
Hi ZABU,

The links from Tom are very wonderful on sedning email in Excel.
Furthermore, I'd also suggest the two kb articles may provide some
assistance for you.
816644 How to Send a Range of Cells in E-Mail by Using VBA
http://support.microsoft.com/?id=816644

185774 How to Programmatically Manipulate a UserForm
http://support.microsoft.com/?id=185774

I'd also suggest the "Progress Indicator" sample from j-walk.com will help
you a lot in this issue which is available from the lnk:
http://www.j-walk.com/ss/excel/files/developer.htm

In addition, since the sending mail operation will spend some time. You can
also show one processbar in Excel status. The processbar sample from David
Wiseman for Excel may help you some in this scenario. This sample is also
located from this link:
Control the LED Display in the StatusBar
http://www.j-walk.com/ss/excel/files/developer.htm

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top