CDO, SMTP Server Name, and Excel's SendMail Method

[Greg Lovern]

I have an Excel VBA macro that sends mail using CDO. Works fine, but
users have to enter their SMTP Server in a designated cell.

In case a user doesn't know his SMTP Server, I allow the user to
choose, in a dropdown, whether to use CDO or Excel's SendMail, since
Excel's SendMail Method does not require specifying an SMTP Server.
Instead, according to the function reference, it uses "the installed
mail system" to send mail.

But CDO is much more versatile than Excel's SendMail, so I'd prefer to
be able to use CDO without expecting the user to know his SMTP Server.

Obviously, Excel's SendMail is somehow figuring out the SMTP Server.
I've found code to do this if the user has a mail account set up in
Outlook Express, but whatever Excel does works with or without an
Outlook Express mail account set up.

If Excel can do it, surely it must be possible to do it in another
program too. How can I determine the SMTP Server without regard to
what mail program is installed and set up with a mail account?


Thanks,


Greg

 

[gimme_this_gimme_that]

Apologies in advance that this comment has little to do with a direct
answer to your question ... but ... did you check to see if you have
an object in your company's Active Directory that has the user's SMTP
(or Exchange Server) ?

If it does you can fetch it there.

That's probably where Excel's SendMail is getting it.

For simple examples of fetching data from Active Directory using VBA
see microsoft.public.scripting.vbscript. Simple queries in VBA and
VBScript are nearly identical. I've posted a few VBA examples there
myself.

 

[Greg Lovern]

Thanks, but wouldn't that require that the user be on an NT network?

Greg

 

[gimme_this_gimme_that]

I don't know.

Are you running another implementation of LDAP?

I haven't tried to query other LDAP systems because I haven't been on
one to experiment.

How do you think Excel's SendMail knows?

 

[Ron de Bruin]

If the user have a account in OE or Windows mail there is no need to fill in the SMTP server
http://www.rondebruin.nl/cdo.htm

There is code on this site if you want to know the SMTP server from Outlook
http://vbnet.mvps.org/