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

[RCMetrics]

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

 

[Norie]

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]

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]

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]

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]

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]

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?