SendObject question

[Leslie Isaacs]

Hello All

I want to use the SendObject command to send an email to all the 'people' in
table [locums] for whom there is an email address in field 'email'. I know I
could simply 'hard code' the list of email addresses in the To parameter of
the SendObject command, but obviously this would be laborious and more
inmportantly the list would need to be manually updated each time the email
addresses were updated. Is there a way for the To parameter to be specified
in terms of the 'email' field in my table [locums]?

Hope someone can help.

Many thanks
Leslie Isaacs

 

[Steve Schapel]

Les,

It is possible, but very awkward, to do this in a macro. This is one of
those examples where a VBA procedure is a lot neater, as you can loop
through a recordset based on your Locums table, and build the To string
in code. Something like this...

Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurretnDb.OpenRecordset("SELECT Email FROM Locums WHERE
Email Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ! & ","
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)
DoCmd.SendObject ...

 

[Leslie Isaacs]

Hello Steve

Many thanks for your reply: obviously VBA is the way to go here. I wasn't
sure what the VBA would be for the DoCmd.SendObject line, so I converted my
macro to a module which gave me:

DoCmd.SendObject acQuery, "qry locum sessions not assigned",
"MicrosoftExcelBiff8(*.xls)", "locums", "", "", "Locum session/s required",
"Please see attached schedule.", True, ""

The parameter "locums" in this command is the name of the distribution list
in Outlook. Would I be correct in amending this to "strTo" in the code you
gave me? Also I have had to amend the SELECT statement because the locums
data is in fact in a table called [telephone numbers]. If I'm right (??!!)
my complete function would be:

-------------------------------------------------------------------------------
Function find_locums()
On Error GoTo find_locums_Err

Dim rst As DAO.Recordset
Dim strTo As String

Set rst = CurretnDb.OpenRecordset("SELECT [telephone numbers].Email FROM
[telephone numbers] WHERE ((([telephone numbers].Email) Is Not Null) AND
(([telephone numbers].category)="locums"));

With rst
Do Until .EOF
strTo = strTo & ! & ","
MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)

DoCmd.SendObject acQuery, "qry locum sessions not assigned",
"MicrosoftExcelBiff8(*.xls)", "strTo ", "", "", "Locum session/s required",
"Please see attached schedule.", True, ""

find_locums_Exit:
Exit Function

find_locums_Err:
MsgBox Error$
Resume find_locums_Exit

End Function
-------------------------------------------------------------------------------

I would be very grateful if you could let me know if this looks right.
Many thanks
Leslie Isaacs



[QUOTE="Steve Schapel"]
Les,

It is possible, but very awkward, to do this in a macro. This is one of
those examples where a VBA procedure is a lot neater, as you can loop
through a recordset based on your Locums table, and build the To string in
code. Something like this...

Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurretnDb.OpenRecordset("SELECT Email FROM Locums WHERE Email
Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![Email] & ","
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)
DoCmd.SendObject ...

--
Steve Schapel, Microsoft Access MVP

[QUOTE="Leslie"]
Hello All

I want to use the SendObject command to send an email to all the 'people'
in table [locums] for whom there is an email address in field 'email'. I
know I could simply 'hard code' the list of email addresses in the To
parameter of the SendObject command, but obviously this would be
laborious and more inmportantly the list would need to be manually
updated each time the email addresses were updated. Is there a way for
the To parameter to be specified in terms of the 'email' field in my
table [locums]?

Hope someone can help.

Many thanks
Leslie Isaacs[/QUOTE][/QUOTE]

 

[Steve Schapel]

Leslie,

A few comments... I don't know if I've spotted them all!

I am not familiar with the "Biff" in the Excel reference. But if the
convert to VBA wizard put it there, I guess it's valid.

MoveNext should be .MoveNext (i.e. you forgot the '.')

"strTo" should be strTo, i.e. without the ""s

 

[Ruth Isaacs]

Steve

Brilliant: many thanks for all the help.

Les

Leslie,

A few comments... I don't know if I've spotted them all!

I am not familiar with the "Biff" in the Excel reference. But if the
convert to VBA wizard put it there, I guess it's valid.

MoveNext should be .MoveNext (i.e. you forgot the '.')

"strTo" should be strTo, i.e. without the ""s

--
Steve Schapel, Microsoft Access MVP

Hello Steve

Many thanks for your reply: obviously VBA is the way to go here. I wasn't
sure what the VBA would be for the DoCmd.SendObject line, so I converted my
macro to a module which gave me:

DoCmd.SendObject acQuery, "qry locum sessions not assigned",
"MicrosoftExcelBiff8(*.xls)", "locums", "", "", "Locum session/s required",
"Please see attached schedule.", True, ""

The parameter "locums" in this command is the name of the distribution list
in Outlook. Would I be correct in amending this to "strTo" in the code you
gave me? Also I have had to amend the SELECT statement because the locums
data is in fact in a table called [telephone numbers]. If I'm right (??!!)
my complete function would be:

-------------------------------------------------------------------------- -----
Function find_locums()
On Error GoTo find_locums_Err

Dim rst As DAO.Recordset
Dim strTo As String

Set rst = CurretnDb.OpenRecordset("SELECT [telephone numbers].Email FROM
[telephone numbers] WHERE ((([telephone numbers].Email) Is Not Null) AND
(([telephone numbers].category)="locums"));

With rst
Do Until .EOF
strTo = strTo & ! & ","
MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)

DoCmd.SendObject acQuery, "qry locum sessions not assigned",
"MicrosoftExcelBiff8(*.xls)", "strTo ", "", "", "Locum session/s required",
"Please see attached schedule.", True, ""

find_locums_Exit:
Exit Function

find_locums_Err:
MsgBox Error$
Resume find_locums_Exit

End Function
-------------------------------------------------------------------------- -----

I would be very grateful if you could let me know if this looks right.
Many thanks
Leslie Isaacs
[/QUOTE][/QUOTE]