Sending Email to mulitple users from Access

[Murphy]

I have a database and I was requested to setup a process to email various "Process Owners" on a click of a button. I have tried doing it with a macro, I tried some ways using queries and tables but I can not seem to get it right. A form is filled in with process owners, this is done by a drop down choice, the table these Process Owners are in also contains a column with their email address. I want to capture the email address based on the forms selection. I tried to use a query to capture the email address into a "create table procedure" but now I want to take that information and put it into the To: in outlook. I use Access 2007 and outlook 2007.

in a macro, is it possible to set the option in the SendObject, Action Arguments To field to except multiple emails? I can capture one from a form: =[FOMRS]![(form name)]![(field name)], but I can only get one field to work. Any ideas or what is the best way to attempt this? I have 11 fields in all to be captured from the query/table.

 

[G81]

Hi and welcome,

Best way would probably be in vba code. You can feed your whole table/query into a recordset, grab the relevant fields including email address, and then send the emails.

 

[Grundy82]

I think the vbsendmail.dll project should work perfect for this; you can find the .dll here - http://www.freevbcode.com/showcode.asp?id=109 along with the source code. Once you have got it built to your needs you can just use a macro to call the module.

You could have a table containing your list of recipients and just feed them in.

 

[Murphy]

thank you G81, I am not up to VB codeing as I like but I will give that a go over. thanks again.

Thank you Grundy82 for the link. I have downloaded the information and will see what I can do with it.

 

[Grundy82]

No problem, you will need to register the .dll and then reference it in the Visual Basic tools menu.

 

[Murphy]

thanks Grundy82...let you know the results...have a great day

 

[ghudson]

Search the forum for "email outlook" for code to create an email to populate all the parts of the email. You can use a Dlookup() or Dcount() to find the correct email address for the To: section. I suggest you look at the code I posted in this thread Outlook 2007 & E-mails

Bonus, since you are using Access 2007 or 2010 you will not get the nag email warning from outlook about sending an email when you use outlook automation code to create and send the email.

 

[Murphy]

thank you ghudson, that is some code. what sections would I need to change to fit my form? would I be able to remove the bcc sections without effecting the workings of the code?

 

[ghudson]

thank you ghudson, that is some code. what sections would I need to change to fit my form? would I be able to remove the bcc sections without effecting the workings of the code?

My code does not list the Bcc field. You do not have to use the sCC line, just make the value an empty string with two double quotes.

This is the part where you define your specifics. I have adjusted the sTo line to pull the email address from a text box in a form named txtEmailAddress

    sPathFile = "\\Server\Partition\Testing.xls"
    
    'You must key a semicolon between each email name.
    'sTo = "johndoe@widgets.com; marysmith@widgets.com"
    sTo = [txtEmailAddress]
    'sCC = "me@widgets.com"
    sCC = ""
    sReplyRecipient = "joecleck@widgets.com"
    sSubject = "Important Email"
    sBody = sBody & "Please read then destroy this important email!"

 

[Murphy]

Great, thanks for the information, appreciate this a lot. Not being a programmer and having projects dumped on me this forum I can see will be helpful...thanks again ghudson.