Solved: email from within excel using outlook

[PeterNZ]

Hi Guys

I want to send email from excel (using outlook) I have some code that does exactly that very well . But now I need to be able to set up a way to pick certain groups of email addresses to sent to .I was thinking of perhaps a userform popup activated by keyboard shortcut to launch it. Having a radio button list and a go button User can select a radio button and click go. which should check a named range on another sheet (or text file ) to get the list of email addresses to populate the outlook dialogbox. I would need to have 11 areas for user to pick one from.

will be useing this on excel 2003 and outlook 2003.
here is the code that works well for a predefined email address.


Public Sub SendIt()

Application.Dialogs(xlDialogSendMail).Show arg1:="toWho@somewhere.com", _
arg2:="Subject goes here"



End Sub




Thanks Guys.:help

[Aussiebear]

What are the rules by which the email recipients are chosen by? Can it be defined within Excel? If so then I'm assuming that you could have a dropdown list to select the group required.

[PeterNZ]

Yes certainly, preferred within excel . Main problem is outlook has people listed as individuals (thousands of them) and also as a group (entire site) I wish to be able to create (once) Area lists of email addresses that can be auto-inserted at the click of a button . But having those lists of emails in another sheet or text file for easy additions /changes later. Perhaps groups can be created in outlook itself but why go there if we can avoid it?. Wanna keep it to Excel as much as possible.

[mdmackillop]

Can you post a workbook showing the code you have and address etc. layouts

[PeterNZ]

Hi, well I believe I have cacked it myself Got some code for sendmail from Mr Ron Debruin I found on MSDN Thanks Mr Debruin! Did the rest Myself(not well tested yet) but seems to work well so far. I will atatch workbook for you to check, any advice on improvements would be appriciated. :beerchug:

[PeterNZ]

attatching excel 2003 version Works you just need to insert email addresses seperated by ; 's in sheet three in the 5 area named ranges .Uses control + m shortcut to run it.

[GTO]

Greetings Peter,

Well, it looks like you did well in solving this on your own. Well done!

As you mentioned 'any suggestions', here's what took me a bit too long to cobble together.

It appears that our circumstances are different, in that, if I was to use this at work, I have to allow for being asked for a password, and I'm certainly not 'all that' at Outlook. Additionally, I ran into a problem in testing at home, related to either a sick laptop or an xl2000 issue possibly. Anyways, I got a chance to try at work (2003) and this seems to work.

If nothing else, take a look at the function RetList(), as this could give you a way to enter the addresses in each cell (down a column) rather than needing to write the whole distribution list in one cell.

Well, I hope that's a little help at least,

Mark

[GTO]

PS. Thanks for posting in .xls format, as there's plenty of members who still are limited to this :-)

[mdmackillop]

You could also try using the format (ignore the underline)
John <js@test.com>
which should put the Recipient's name in the To field

[PeterNZ]

Thanks for the tips guys, good stuff as always :thumb