Email with Bcc only. No attachments

[shuddle]

I know this is a common question but all the threads I read ask about attaching reports and such. I'm sure I am making this more complicated than it needs to be.

I only want to open an email with the recipients in the Bcc. The data is pulled from the Query Q_All_Emails_no_dups The field name is Email.



I know it has something to do with the DoCmd.SendObject but I'm sure I am butchering it. That is the only line of code I have. Is there more to it?



DoCmd.SendObject acSendNoObject, , , [Q_All_Emails_no_dups.Email], , , , , , False

[CJ_London]

BCC is one of the parameters of sendobject

perhaps explain what you are actually trying to do, it may be that sendobject is not the right thing to be using

[Gicu]

You can't just reference the query and its email field, you need to use a string variable and build a semi-colon or comma delimited list of your email addresses:

Dim sEmails as string
Dim qdf as DAO.QueryDef, rst as DAO.Recordset

Set qdf=Currentdb.QueryDefs("Q_All_Emails_no_dups")
Set rst=qdf.openrecordset
sEmails=""
Do Until rst.EOF
     sEmails=sEmails & rst("Email") & ","
rst.movenext
Loop

sEmails=Left(sEmails,Len(sEmails)-1) 'trim the last comma

DoCmd.SendObject acSendNoObject, , , sEmails, , , , , , False

Cheers,
Vlad

[shuddle]

Thanks so much. There's no way I would have figured that out.

It is giving me an error where qdf As DAO.QueryDef, is highlighted.

---------------------------Microsoft Visual Basic for Applications
---------------------------
Compile error:


User-defined type not defined
---------------------------
OK Help
---------------------------

[Gicu]

Sorry just use (without the DAO.):

Dim qdf as QueryDef

[shuddle]

Still getting the same area in the same location.

[shuddle]

I mean "Still getting the same error in the same location

[Gicu]

Can you please post your code?

[shuddle]

Option Compare Database


Private Sub Command0_Click()
Dim sEmails As String
Dim qdf As QueryDef, rst As Recordset


Set qdf = CurrentDb.QueryDefs("Q_All_Emails_no_dups")
Set rst = qdf.openrecordset
sEmails = ""
Do Until rst.EOF
sEmails = sEmails & rst("Email") & ","
rst.movenext
Loop


sEmails = Left(sEmails, Len(sEmails) - 1) 'trim the last comma


DoCmd.SendObject acSendNoObject, , , sEmails, , , , , , False
End Sub

[Gicu]

Can you try:

Option Compare Database
Option Explicit


Private Sub Command0_Click()
Dim sEmails as string
Dim qdf as QueryDef, rst as DAO.Recordset


Set qdf=Currentdb.QueryDefs("Q_All_Emails_no_dups")
Set rst=qdf.openrecordset
sEmails=""
Do Until rst.EOF
     sEmails=sEmails & rst("Email") & ","
rst.movenext
Loop


sEmails=Left(sEmails,Len(sEmails)-1) 'trim the last comma


DoCmd.SendObject acSendNoObject, , , sEmails, , , , , , False
End Sub

Cheers,

[shuddle]

Nope it's just not liking that qdf as QueryDef

[Gicu]

In the VBE window go to Tools\References and make sure you have checked the Microsoft Office xx.00 Access database engine Object Library where xx is your Office version.

[Welshgasman]

Why the need to use a qdf?

Could you just not use ?

Set rst = CurrentDb.OpenRecordset("Q_All_Emails_no_dups")

[shuddle]

I went in and checked it. Now it is giving me a Run-time error '2295' Unknow message recipients(s); the message was not sent.

[Gicu]

Of course, I usually use a qdf in case the query has parameters to avoid the "too few parameters...." error.

Dim qdf as QueryDef, prm as Parameter,rst as Dao.Recordset
Set qdf=Currentdb.QueryDefs("qryMyQuery")
For each prm in qdf.Parameters
       prm.Value=Eval(prm.Name)
Next prm

Set rst=qdf.OpenRecordset
....

Didn't include the parameter portion in this post as the query name posted (Q_All_Emails_no_dups) suggested no parameters were used.

Cheers,