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]
