Tim,
This is how I have done it.
Create a stored procedure named "GetEMailAddresses"
Create a function to loop through the stored procedure.
Code is below. I hope this helps.
lq
The stored procedure:
/* Create list of e-mail addresses from tblEmployees */
Alter Procedure GetEMailAddresses
As
SELECT
EmailAddress
FROM
tblEmployees
WHERE
(InactiveEmployee = 0)
AND
(EmailAddress Is Not Null)
ORDER BY
tblEmployees.WorkCity
The Function:
Function EmailList()
On Error GoTo myErr
'PURPOSE: Returns a string list of all employees with an e-mail
addresses that are still active employees
Dim RS As ADODB.Recordset, myAddress As String, mySP As
String, _
myField As String, mySubject as string, myMsg as string
mySP = "GetEMailAddresses"
myField = "EmailAddress"
Set RS = CurrentProject.Connection.Execute("EXEC " & mySP)
myAddress = ""
If Not RS.BOF And Not RS.EOF Then '>has found at least one
record:
Do While Not RS.EOF
myAddress = myAddress & ";" & RS(myField)
RS.MoveNext
Loop
End If
myAddress = Mid(myAddress, 2)
'call your email program using sendobject or another function:
mySubject = "Notice to all employees"
myMsg = "If you are receiving this email it means your job has
been eliminated and you should consider yourself immediately
terminated." & vbcrlf & "Your job has been outsourced to a cheaper
country."
DoCmd.SendObject acSendNoObject, , , myAddress, , ,
mySubject, myMsg, True
myExit:
On Error Resume Next
RS.Close
Set RS = Nothing
Exit Function
myErr:
If Err.Number = 2296 Or Err.Number = 2501 Then
'user cancelled the email after the email template opened
ElseIf Err.Number = 3021 Then
MsgBox "There are no records that match your search.",
vbInformation, "Search Results"
ElseIf Err.Number = 94 Then 'there was a record but it contains a
null value:
'>user cancelled
Else
MsgBox Err.Number & " " & Err.Description
End If
Resume myExit
End Function
Tim Smallwood <dotnettim@netscape.net> wrote in message news:<40aac5e3$0$207$75868355@news.frii.net>...[color=blue]
> Hi,
>
> I have an Access project that I use to allow a client to hit an SQL
> server at my host. This project has several forms that are used to
> udpate tables, etc, but I'd also like to be able to include the ability
> to allow my client to send an email to everyone that has subscribed to
> our web site by going to the table that contains the names and email
> addy's and then loop thru the same and send an email to them all by
> appending their address to the email TO: field.. or BCC< etc.. I've done
> this in ASP, and VB, but not in access.. I of course will be using the
> SMTP server at my hosting provider for this.. Can someone point me to
> some sample code to do this? I'm using Access 2002 / Office pro 2002..
> Thanks
>
>
>
> Tim
>
> *** Sent via Developersdex
http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]
