Sending Email with multiple recipients (1 Viewer)

BWG3

Registered User.
Local time
Today, 16:06
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.
 

boblarson

Smeghead
Local time
Today, 13:06
Joined
Jan 12, 2001
Messages
32,059
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
 

BWG3

Registered User.
Local time
Today, 16:06
Joined
Sep 22, 2009
Messages
44
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
 

boblarson

Smeghead
Local time
Today, 13:06
Joined
Jan 12, 2001
Messages
32,059
The code has to be IN an event. So, which event did you put it in?
 

BWG3

Registered User.
Local time
Today, 16:06
Joined
Sep 22, 2009
Messages
44
It works!! I placed it in the "on click" event. Thank you very much for your help!
 

wnicole

New member
Local time
Today, 13:06
Joined
Sep 28, 2013
Messages
9
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?
 

pr2-eugin

Super Moderator
Local time
Today, 20:06
Joined
Nov 30, 2011
Messages
8,494
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
 

wnicole

New member
Local time
Today, 13:06
Joined
Sep 28, 2013
Messages
9
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?
 

pr2-eugin

Super Moderator
Local time
Today, 20:06
Joined
Nov 30, 2011
Messages
8,494
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..
 

wnicole

New member
Local time
Today, 13:06
Joined
Sep 28, 2013
Messages
9
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.
 

mmartin831

New member
Local time
Today, 15:06
Joined
Apr 16, 2013
Messages
1
this was awesome, thank you!! what would you add to copy two people that would never change.

Thanks,
 

bastanu

AWF VIP
Local time
Today, 13:06
Joined
Apr 13, 2010
Messages
1,401
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,
 

Users who are viewing this thread

Top Bottom