Using VBA to send email based on form response

[muziq2]

Hi,

I have a user form that basically allows a user to select a class the
want to attend. This form then updates another sheet with the user'
name, the class they have selected and records the time they signed u
for the class.

Currently I just have a msg box popping up to say that they have bee
registered for the class. However, they (the powers that be) woul
also like an email confirmation to be sent out.

Users will be accessing the file over a general file server -

Basically the user would be sending a message to themself verifyin
that they've signed up for the class since they'll open the file o
their box and will not have permission to access another emai
account-

Here are the questions -

Can excel trigger an email through outlook that states the user nam
and the date of the class they selected?

Excel would have to use the user name they select from the drop dow
box in the form as the recipient in the to: line. Outlook would the
have to get the email address based on that name. Normally outlook ca
reconcile the email address if only the name is entered.

Any help is greatly appreciated.

If you need any other information please let me know.

Thanks,

Jef

 

[muziq2]

--

 

[ross]

Hi Jeff,

Excel can send e-mail (an account needs to be active on the box). I'm
not going to post the code here, look on the net/newsgroups/macro
recored.

I would make the follow suggestions.
I would ask the user to enter an e-mail, and then use this. It would
make the code a whole lot easier, and i think it is a better way to go
anyway, as the user can control which e-mail account is to be used.
If you use the out look address book, you'll have to code to get the
address based on the name, and say there are 2 folks with the same
name, the user may need to look it up etc, he may need to do this
anyway.

If i was you i'd ask them for there e-mail address and be done with
it, sometimes it's just better to keep things simple!

anyway
good luck
Ross

 

[muziq2]

Thanks for your help Ross. You're right, I should have the user ente
an email address.

If they do not enter the email address then an email will not be sent?

Is there any way that you can post the code here. I'm accessing thi
through a portal (http://www.excelforum.com) and not through
newsgroup,

Thanks,

Jef

 

[ross]

Jeff, if you can get here your laughing,

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

(Thanks to Ron de Bruin)

If they do not enter the email address then an email will not be sent?

You could use application.username, or windows.username or somthing to
get a user name, and then try and get the users e-mail adress from the
outlook address book, but this will need alot(well more) of codeing,
and you'll have to access outlook from excel (i'm not mega sure but i
think maybe outlook uses vb sripit?) anyway. On top of all this there
is no guarantee that the system and excel user name will be a match
for the e-mail address (unless you know this is not the case for your
network). For example, my username on the pc is 7537462 (or whatever),
and my excel "user name" is BT, so that wouldn't be any good.

You could ask them there name, (say in a text box, or your drop down
box) and use this to get the e-mail addres from outlook, but agian, if
john smith works at you place you could be introuble!

I think you have to get it from another box - i dont think it will be
an inconvenience for your users.

Good Luck
Ross