Sending Email with multiple recipients

B

BWG3

Registered User.
Local time
Today, 15:16
Joined
Sep 22, 2009
Messages
44
I click a button on a form which runs an append query to a table (Table1) and all this append query does is store the email addresses of the people I have selected on that form. So, if I selected Tom and John on the form, Table1 stores Tom@blank.com and John@blank.com in the first two rows of the first column. Is there anyway to take these names (note: I may also have more than 2 people selected) and set them equal to vba code people so that an email will be sent to all the persons in Table1?


strEmailAddress = Is there any code I can place here that will read all the email addresses in column 1 of Table1 so that the sendobject command will send an email to multiple people?

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False

Thank you for your help. Happy Holidays.
 
This should work, I believe:

Code: 
Dim rst As DAO.Recordset
Dim strEmailAddress

Set rst = CurrentDb.OpenRecordset("Table1")

Do Until rst.EOF
  strEmailAddress = strEmailAddress & rst("EmailAddressFieldNameHere") & ","
  rst.MoveNext
Loop

strEmailAddress = Left(strEmailAddress, Len(strEmailAddress)-1)

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False

rst.Close
Set rst = Nothing
 
Bob, thanks for your reply. I inserted this code in a new vba module and I was going to have the module run in a macro after the append query. I'm getting an error with the "Set" in the above code. The error states "compile error, invalid outside procedure". I'm pretty new to access and vba, so I'm not exactly sure what this means. Any ideas to fix the problem. Thanks.

Brandon
 
The code has to be IN an event. So, which event did you put it in?
 
It works!! I placed it in the "on click" event. Thank you very much for your help!
 
I have a question. This seem to be working great for me...however right before it goes to Outlook it says "run time 2295 unknown message recipients" and does not send.
When I hover over the email section of code, the email addresses are correct.

Any suggestions?
 
Did you try Debugging (http://www.baldyweb.com/ImmediateWindow.htm) the generated strEmailAddress? Normally the error associated with 2295 unknown message recipients normally involves some missing email addresses..
Code: 
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress)-1)
[COLOR=Blue][B]Debug.Print strEmailAddress[/B][/COLOR]
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
 
I dont understand. All my email addresses are correct. Not sure why it will send that code.
I put the Debug.Print there but nothing appeared at bottom. Is there something to hit to run it?
 
You need to execute the code as you normally would to send email.. But this time since you have the Debug.Print line, it will show what the String holds..
 
Ok I did it at the bottom it says
False
False
False
then the email addresses.

Questions
1. It appears the employees without email addresses may be trigerring it. Is there a Is Null statement you can send me. I dont know how to create one in VBA.
2. I notice the email addresses was everybody in the table but not the specific ones based on the query. Was that normal? Is it telling me the code is wrong and will send to everybody?

Your help appreciated.
 
this was awesome, thank you!! what would you add to copy two people that would never change.

Thanks,
 
You shouldn't "reuse" a 6-year old post, start your own instead and copy and paste the code (and the link to the original if you wish). As you mention you cannot do that with SendObject, you will have to use Outlook automation (or CDO if email client is not Outlook) to attach external files. There are many examples in this forum, start with the Similar threads at the bottom of the page.

Here is a Google search for that:

Cheers,
 
You must log in or register to reply here.

Similar threads

N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
331
sonic8
S
H
Code for sending email 'transport' error
Replies
11
Views
621
isladogs
isladogs
P
sending confirmation email
Replies
3
Views
455
The_Doc_Man
The_Doc_Man
N
Solved How to send multiple attachment with CDO Gmail in MS Access VBA
Replies
11
Views
515
nector
N
N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
396
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom