By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,221 Members | 1,121 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,221 IT Pros & Developers. It's quick & easy.

Using SQL to source email address list

P: n/a
Hi All,
Is there a way to (whilst using the sendobject command) use a query as
the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?

I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find
the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _
"mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
You can save your SQL string as a query, and use the name of the query in
your SendObject.

HTH
- Turtle

"Mark" <ma*********@ntlworld.com> wrote in message
news:fM************@newsfe2-win.ntli.net...
Hi All,
Is there a way to (whilst using the sendobject command) use a query as
the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?

I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _
"mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark

Nov 13 '05 #2

P: n/a
"Mark" <ma*********@ntlworld.com> wrote in message news:<fM************@newsfe2-win.ntli.net>...
Hi All,
Is there a way to (whilst using the sendobject command) use a query as
the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?

I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find
the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _
"mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark

You have to loop through the records returned by the query and then
put the SendObject command inside that...
Nov 13 '05 #3

P: n/a
Mark,

In the sendonject command you use "mail_list" as the fourth argument,
but the method wants to see a string of amailadresses there, seperated
bij semicolons. Here's a way how to convert the query to the right
format:

Dim rstML As ADODB.Recordset
Dim strList As String

rstML.Open "mail_list", CurrentProject.Connection
Do Until rstML.EOF
strList = strList & rstML!Emailadress & "; " 'replace emailadress
with the field you use!
rstML.MoveNext
Loop
rstML.Close
Set rstML = Nothing
strList = Left(strList, Len(strList) - 2) 'cut off the last ; and
space

and now you can use strList (without "") as the fourth argument, now
it should work:
DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _
strList, "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

Hope this helps,

Regards,

Gert-Jan

"Mark" <ma*********@ntlworld.com> wrote in message news:<fM************@newsfe2-win.ntli.net>... Hi All,
Is there a way to (whilst using the sendobject command) use a query as
the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?

I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find
the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _
"mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark

Nov 13 '05 #4

P: n/a
Hi there,
As I said, I am new to programming to bear with me if I am making a
really simple mistake/s. As I understand, I need to;
have a query saved (as "mail_list") which lists all the addresses the report
is to be sent to.
I replace the text "Emailadress" with the field name returned by the query.

If I run the code below, I get the following error message: Object variable
or With block variable not set.

Could you tell me what I am doing wrong?

Private Sub Command0_Click()

Dim rstML As ADODB.Recordset
Dim strList As String
On Error GoTo mail_err

rstML.Open "mail_list", CurrentProject.Connection, adOpenStatic,
adLockReadOnly
Do Until rstML.EOF
strList = strList & rstML!Address & "; " 'replace email address with
the field you use!
rstML.MoveNext
Loop
rstML.Close
Set rstML = Nothing
strList = Left(strList, Len(strList) - 2) 'cut off the last ; and Space
DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)",
strList, "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
Debug.Print Err.Description
MsgBox Err.Description & Err.Number
Resume mail_exit

End Sub

Thanks again,

Mark

"G.J. v.d. Kamp" <gj******@hotmail.com> wrote in message
news:d4*************************@posting.google.co m...
Mark,

In the sendonject command you use "mail_list" as the fourth argument,
but the method wants to see a string of amailadresses there, seperated
bij semicolons. Here's a way how to convert the query to the right
format:

Dim rstML As ADODB.Recordset
Dim strList As String

rstML.Open "mail_list", CurrentProject.Connection
Do Until rstML.EOF
strList = strList & rstML!Emailadress & "; " 'replace emailadress
with the field you use!
rstML.MoveNext
Loop
rstML.Close
Set rstML = Nothing
strList = Left(strList, Len(strList) - 2) 'cut off the last ; and
space

and now you can use strList (without "") as the fourth argument, now
it should work:
DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _ strList, "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

Hope this helps,

Regards,

Gert-Jan

"Mark" <ma*********@ntlworld.com> wrote in message

news:<fM************@newsfe2-win.ntli.net>...
Hi All,
Is there a way to (whilst using the sendobject command) use a query as the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?
I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _ "mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark

Nov 13 '05 #5

P: n/a
Oops, my bad, the first line should be:
Dim rstML As New ADODB.Recordset
^^^
Also make sure that lines of code stay on one line, i got a couple of
linebreaks when i copied the code.

Good luck,
GJ

"Mark" <ma*********@ntlworld.com> wrote in message news:<14**************@newsfe4-gui.ntli.net>...
Hi there,
As I said, I am new to programming to bear with me if I am making a
really simple mistake/s. As I understand, I need to;
have a query saved (as "mail_list") which lists all the addresses the report
is to be sent to.
I replace the text "Emailadress" with the field name returned by the query.

If I run the code below, I get the following error message: Object variable
or With block variable not set.

Could you tell me what I am doing wrong?

Private Sub Command0_Click()

Dim rstML As ADODB.Recordset
Dim strList As String
On Error GoTo mail_err

rstML.Open "mail_list", CurrentProject.Connection, adOpenStatic,
adLockReadOnly
Do Until rstML.EOF
strList = strList & rstML!Address & "; " 'replace email address with
the field you use!
rstML.MoveNext
Loop
rstML.Close
Set rstML = Nothing
strList = Left(strList, Len(strList) - 2) 'cut off the last ; and Space
DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)",
strList, "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
Debug.Print Err.Description
MsgBox Err.Description & Err.Number
Resume mail_exit

End Sub

Thanks again,

Mark

"G.J. v.d. Kamp" <gj******@hotmail.com> wrote in message
news:d4*************************@posting.google.co m...
Mark,

In the sendonject command you use "mail_list" as the fourth argument,
but the method wants to see a string of amailadresses there, seperated
bij semicolons. Here's a way how to convert the query to the right
format:

Dim rstML As ADODB.Recordset
Dim strList As String

rstML.Open "mail_list", CurrentProject.Connection
Do Until rstML.EOF
strList = strList & rstML!Emailadress & "; " 'replace emailadress
with the field you use!
rstML.MoveNext
Loop
rstML.Close
Set rstML = Nothing
strList = Left(strList, Len(strList) - 2) 'cut off the last ; and
space

and now you can use strList (without "") as the fourth argument, now
it should work:
DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _ strList, "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

Hope this helps,

Regards,

Gert-Jan

"Mark" <ma*********@ntlworld.com> wrote in message

news:<fM************@newsfe2-win.ntli.net>...
Hi All,
Is there a way to (whilst using the sendobject command) use a query as the source of email addresses. I ask this because the list is constanly
changing and I don't want to keep going into the code to change the list?
I am very new to programming and though that I could declare the SQL
statement but that has not worked. Could anyone offer any direction to find the best way to do this if indeed it can be?

The code I am trying to use is:

Private Sub Command0_Click()
On Error GoTo mail_err
Dim mail_list As String

mail_list = "SELECT mailing_list.address" & _
"FROM mailing_list" & _
"WHERE (((mailing_list.end_of_shift)=-1));"

DoCmd.SendObject acQuery, "qry_weekly_report", "RichTextFormat(*.rtf)", _ "mail_list", "", "", "Weekly Report", _
"Please find attached the weekly report", False, ""

mail_exit:
Exit Sub

mail_err:
MsgBox Err.Description
Resume mail_exit

TIA

Mark

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.