Recordset and DoCmd.SendObject

S

Stephanie

Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
..
 
D

Dan Artuso

Hi,
You have to use SendObject *after* you've built your bcc list and use the
list as an argument to SendObject. You don't need commas for trailing arguments that you leave out.

Private Sub EmailHydrant_Click()

DoCmd.SendObject acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName] FROM Contacts WHERE [EmailName] Is Not Null" And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub
 
S

Stephanie

Thanks, Dan.
Makes sense. However, I still don't really get it. Seems
like I need to take it in 2 steps. First, I attached the
DAO.Recordset code to a command button:

Private Sub EmailHydrant_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null And
[EmailHydrant] = -1")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub

However, when I click nothing happens. Although I'm not
sure what should happen...how do I know whether or not the
code worked correctly? how can I see the results?

Second, once the code produces the list , I would need to
use
DoCmd.SendObject SendNoObject,,acFormatRTF,,,Bcc,"Hydrant
Email Link"
Would I still SendNoObject? How do I get the email list I
want populated into an email? Thanks- I find this
confusing, Stephanie

-----Original Message-----
Hi,
You have to use SendObject *after* you've built your bcc list and use the
list as an argument to SendObject. You don't need commas
Click to expand...
for trailing arguments that you leave out.
Private Sub EmailHydrant_Click()

DoCmd.SendObject
Click to expand...
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT
Click to expand...
[EmailName] FROM Contacts WHERE [EmailName] Is Not Null"
And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub


--
HTH
-------
Dan Artuso, MVP


"Stephanie" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
.
Click to expand...


.
Click to expand...
 
D

Dirk Goldgar

Stephanie said:
Thanks, Dan.
Makes sense. However, I still don't really get it. Seems
like I need to take it in 2 steps. First, I attached the
DAO.Recordset code to a command button:

Private Sub EmailHydrant_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null And
[EmailHydrant] = -1")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub

However, when I click nothing happens. Although I'm not
sure what should happen...how do I know whether or not the
code worked correctly? how can I see the results?

Second, once the code produces the list , I would need to
use
DoCmd.SendObject SendNoObject,,acFormatRTF,,,Bcc,"Hydrant
Email Link"
Would I still SendNoObject? How do I get the email list I
want populated into an email? Thanks- I find this
confusing, Stephanie

-----Original Message-----
Hi,
You have to use SendObject *after* you've built your bcc list and
use the list as an argument to SendObject. You don't need commas
Click to expand...
for trailing arguments that you leave out.
Private Sub EmailHydrant_Click()

DoCmd.SendObject acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email
Link",,,, Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT
Click to expand...
[EmailName] FROM Contacts WHERE [EmailName] Is Not Null"
And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub


--
HTH
-------
Dan Artuso, MVP


"Stephanie" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...

Stephanie, why did you take out the line that Dan had just before the
End Sub line in the code he posted:

?
That's the line that would open Outlook to a new message with the bcc:
box populated with the list you built in strTo. By taking that line
out, you made it so the code builds the list but doesn't do anything
with it.
 
S

Stephanie

Thanks! I didn't realize that Dan had moved the
DoCmd.SendObject code to the end of the show.
Very cool! Thanks, Stephanie
-----Original Message-----
Thanks, Dan.
Makes sense. However, I still don't really get it. Seems
like I need to take it in 2 steps. First, I attached the
DAO.Recordset code to a command button:

Private Sub EmailHydrant_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null And
[EmailHydrant] = -1")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub

However, when I click nothing happens. Although I'm not
sure what should happen...how do I know whether or not the
code worked correctly? how can I see the results?

Second, once the code produces the list , I would need to
use
DoCmd.SendObject SendNoObject,,acFormatRTF,,,Bcc,"Hydrant
Email Link"
Would I still SendNoObject? How do I get the email list I
want populated into an email? Thanks- I find this
confusing, Stephanie

-----Original Message-----
Hi,
You have to use SendObject *after* you've built your bcc list and
use the list as an argument to SendObject. You don't
Click to expand...
need commas
for trailing arguments that you leave out.
Private Sub EmailHydrant_Click()

DoCmd.SendObject acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email
Link",,,, Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT
Click to expand...
[EmailName] FROM Contacts WHERE [EmailName] Is Not Null"
And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub


--
HTH
-------
Dan Artuso, MVP


"Stephanie" <[email protected]> wrote
Click to expand...
in message news:52ef01c4c8dc$1bf025e0 [email protected]...
Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
Click to expand...
Click to expand...

Stephanie, why did you take out the line that Dan had just before the
End Sub line in the code he posted:
Email Link"

?
That's the line that would open Outlook to a new message with the bcc:
box populated with the list you built in strTo. By taking that line
out, you made it so the code builds the list but doesn't do anything
with it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
S

Stephanie

Dan,
I didn't realize that you had moved the DoCmd.SendObject
code to the end of the Sub. That did the trick, very
slick! Thanks, Stephanie
-----Original Message-----
Hi,
You have to use SendObject *after* you've built your bcc list and use the
list as an argument to SendObject. You don't need commas
Click to expand...
for trailing arguments that you leave out.
Private Sub EmailHydrant_Click()

DoCmd.SendObject
Click to expand...
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT
Click to expand...
[EmailName] FROM Contacts WHERE [EmailName] Is Not Null"
And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub


--
HTH
-------
Dan Artuso, MVP


"Stephanie" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
.
Click to expand...


.
Click to expand...
 
D

Dan Artuso

Hi,
You're welcome, and thanks to Dirk for jumping in. I haven't had much time lately
to follow up on posts.

--
HTH
-------
Dan Artuso, MVP


Stephanie said:
Dan,
I didn't realize that you had moved the DoCmd.SendObject
code to the end of the Sub. That did the trick, very
slick! Thanks, Stephanie
-----Original Message-----
Hi,
You have to use SendObject *after* you've built your bcc list and use the
list as an argument to SendObject. You don't need commas
Click to expand...
for trailing arguments that you leave out.
Private Sub EmailHydrant_Click()

DoCmd.SendObject
Click to expand...
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT
Click to expand...
[EmailName] FROM Contacts WHERE [EmailName] Is Not Null"
And [EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject,,,,,strTo,"Hydrant Email Link"
End Sub


--
HTH
-------
Dan Artuso, MVP


"Stephanie" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
Hi.
I'm trying to get Access to create an email containing the
email address in the Bcc field of those that want to
receive the email (EmailHydrant = Yes). I don't want to
send any Access objects.
One of the MVP helped with some DAO.Recordset code to loop
through the records, but I'm not sure what to do with it.
I decided to use DoCmd.SendObject but am having problems
with the syntax. Grr.
When I click the command button, I want Access to open up
Outlook and populate the Bcc field with the correct email
list, so I can fill in the message text manually and send
the email.
I appreciate your time and hope you have some detailed
suggestions. Thanks, Stephanie

Private Sub EmailHydrant_Click()

DoCmd.SendObject
acSendNoObject,,acFormatRTF,,,Bcc,"Hydrant Email Link",,,,
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null" And
[EmailHydrant] = -1)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub
.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

too few parameters 1
Email Report 2
Email Issue 2
variables and parameters 14
invalid procedure call or argument 2
Getrows array 3
Error sending emails from Access through Outlook... 1
Delete a table using VBA 1

Top