Call e-mails stored in a table for a docmd.sendobject

RCMetrics

Board Regular
Joined
Oct 28, 2005
Messages
95
Hi,

wondering how I can do this,

basically have all e-mails in a table (just e-mails)

the plan is to have one table per center and in each tables the e-mails of employees working in those center.

How can I call the value in one table to be the "bcc" of the sendobject line?

I have one table for testing purposes called Emails

in a form: (command button)

Private Sub Command0_Click()

dim email as ???

email = ???? (calling all e-mails in the Emails table)

DoCmd.SendObject acSendNoObject, , , , ,email, "TEST", "TEST", False


Let me know how I should work around this.

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows
For a start you shouldn't have multiple tables for each centre.

You should have one table with all the emails and a field that indicates the centre.

As to your actual question I don't think I actually understand what you want to do.

Do you want to loop through and create a message for all the emails?
 

RCMetrics

Board Regular
Joined
Oct 28, 2005
Messages
95
Hi,

I undertand your point, so I guess I'll do that...
But basically, I want all e-mails listed to be in the bcc line.

here's what I manage to do:

Private Sub Command0_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String

Set rsEmail = CurrentDb.OpenRecordset("Emails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("E-mail").Value

DoCmd.SendObject acSendNoObject, , , , , strEmail, "TEST", "TEST", False

rsEmail.MoveNext 'goes to next record

Loop
Set rsEmail = Nothing

End Sub

Only thing I don't like is that the message keeps promting even though I have set the argument to FALSE (not sure why it's happening)

And I wish this wouldn't be a loop thing (couldn't figure out to do it at once)

So this code does send to each person listed, but one at a time and for some reason, keeps promting the e-mail for edit... but shouldn't do it.

Any idea?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows
I don't see how you'll be able to do it without some sort of loop.

But I would suggest that rather than looping and creating an email for each address you should loop and construct the bcc string which is described thus.
A string expression that lists the recipients whose names you want to put on the Bcc line in the mail message. If you leave this argument blank, the Bcc line in the mail message is blank.
 

RCMetrics

Board Regular
Joined
Oct 28, 2005
Messages
95

ADVERTISEMENT

Thanks for your help ...I figured it out like this...

instead of looping the send command, I'm looping the e-mails into the string...

Private Sub Command0_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim maillist As String

Set rsEmail = CurrentDb.OpenRecordset("Emails")

Do While Not rsEmail.EOF
maillist = maillist & rsEmail.Fields("E-mail").Value & ";"
rsEmail.MoveNext 'goes to next record
Loop

DoCmd.SendObject acSendNoObject, , , maillist, , , "TEST", "TEST", False

End Sub


And this works fine, it sends to everyone in the table and runs the sendobject only once.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I have sent e-mails this way too. That is putting all the addesses into one field, then sending the e-mail once to all addressees. But I found that Outlook seems to have a limit as to how many addressees you can have doing it this way. I never researched it, just cut off the addressees at an arbitrary number and sent two or three e-mails. This is only meant to be a suggestion to look into this, at least be aware it might happen down the line.
 

westjelly

Board Regular
Joined
Jul 5, 2005
Messages
50
Could you create a short-term distribution list from the emails, send it out, then delete the distribution list?

Or maybe create the distribution list via vba, appending/deleting any addresses that no longer exist?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,983
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top