Urgent Help - Send email to entire contacts as bcc

[Guest]

Hi there,

I am using an Send Object function to generate an email mail merge.
I am trying to put all of my contacts emails in the BCC field so that
privacy is maintained, however my function below inputs the SQL statement
into the box rather than the entire contacts address. If I use a dlookup
function instead, what with statement do I use to loop through the results.


Private Sub mailer_Click()

DoCmd.SendObject acSendNoObject, , , , , emailenq, "Subject", "Message",
True, False

End Sub

Function emailenq() As String

emailenq = "SELECT Customer_Emails_Qry.Customer_Email FROM
Customer_Emails_Qry;"

End Function


Or I try with dlookup but I get an error in the loop saying object required.
What would work here? Without the With and Loop statements it works fine, but
only returns one address (The first recordset)

With Customer_Emails_Qry
Do While Not .EOF

emailenq = DLookup("[Customer_Email]", "[Customer_Emails_Qry]",
[Customer_Email]<> ''") & ";"
Loop

Can anyone help. Either way will be fine.

Thanks

Andi

 

[Smartin]

Hi there,

I am using an Send Object function to generate an email mail merge.
I am trying to put all of my contacts emails in the BCC field so that
privacy is maintained, however my function below inputs the SQL statement
into the box rather than the entire contacts address. If I use a dlookup
function instead, what with statement do I use to loop through the results.


Private Sub mailer_Click()

DoCmd.SendObject acSendNoObject, , , , , emailenq, "Subject", "Message",
True, False

End Sub

Function emailenq() As String

emailenq = "SELECT Customer_Emails_Qry.Customer_Email FROM
Customer_Emails_Qry;"

End Function

Right, you're not executing any query here, you're merely returning the
SQL statement (a string) to SendObject.


Try this replacement for the Function.

Function emailenq() As String
Dim RS As DAO.Recordset
Dim tmpEM As String
Dim sSQL As String
sSQL = "SELECT Customer_Emails_Qry.Customer_Email " & _
"FROM Customer_Emails_Qry;"
Set RS = DBEngine(0)(0).OpenRecordset(sSQL)
RS.MoveFirst
Do While Not RS.EOF
tmpEM = tmpEM & Nz(RS.Fields(0).Value, "") & ";"
RS.MoveNext
Loop
emailenq = tmpEM
Set RS = Nothing
End Function

Or I try with dlookup but I get an error in the loop saying object required.
What would work here? Without the With and Loop statements it works fine, but
only returns one address (The first recordset)

With Customer_Emails_Qry
Do While Not .EOF

emailenq = DLookup("[Customer_Email]", "[Customer_Emails_Qry]",
[Customer_Email]<> ''") & ";"
Loop

Can anyone help. Either way will be fine.

Thanks

Andi

HTH

 

[Guest]

Hi There Smartin,

Thanks for that,

yes that solved my issue.

By the way anyone reading this may want to take note because this solves the
issue of sending out a group mail instead of having to do a mail merge in
word and then outputting to outlook.

You can obviously set up a formatted html template for your mailshot, using
the send object.

Thanks

Andi

Hi there,

I am using an Send Object function to generate an email mail merge.
I am trying to put all of my contacts emails in the BCC field so that
privacy is maintained, however my function below inputs the SQL statement
into the box rather than the entire contacts address. If I use a dlookup
function instead, what with statement do I use to loop through the results.


Private Sub mailer_Click()

DoCmd.SendObject acSendNoObject, , , , , emailenq, "Subject", "Message",
True, False

End Sub

Function emailenq() As String

emailenq = "SELECT Customer_Emails_Qry.Customer_Email FROM
Customer_Emails_Qry;"

End Function

Right, you're not executing any query here, you're merely returning the
SQL statement (a string) to SendObject.


Try this replacement for the Function.

Function emailenq() As String
Dim RS As DAO.Recordset
Dim tmpEM As String
Dim sSQL As String
sSQL = "SELECT Customer_Emails_Qry.Customer_Email " & _
"FROM Customer_Emails_Qry;"
Set RS = DBEngine(0)(0).OpenRecordset(sSQL)
RS.MoveFirst
Do While Not RS.EOF
tmpEM = tmpEM & Nz(RS.Fields(0).Value, "") & ";"
RS.MoveNext
Loop
emailenq = tmpEM
Set RS = Nothing
End Function

Or I try with dlookup but I get an error in the loop saying object required.
What would work here? Without the With and Loop statements it works fine, but
only returns one address (The first recordset)

With Customer_Emails_Qry
Do While Not .EOF

emailenq = DLookup("[Customer_Email]", "[Customer_Emails_Qry]",
[Customer_Email]<> ''") & ";"
Loop

Can anyone help. Either way will be fine.

Thanks

Andi

HTH