DavidSt
|
| Posted: 06/16/2005, 10:34 AM |
|
I want to set up an after insert event that will allow me to send emails to multiple people within a database.
Example:
If a task is updated then I want to send an email to everyone that has been assigned that task. I tried to use the CCDLookup and it only emails the first person it is assigned to within the table. (table consists of many to one relationship where task id could be found multiple times within the "taskid" column) I am trying to find the solution to be able to send emails to every row in the table were the task id appears in the task id column).
Does this make any sense or am I just confusing the situation.
|
|
|
 |
smalloy
Posts: 107
|
| Posted: 06/16/2005, 11:55 AM |
|
The best way to handle this is not with a CCDLookUp but with a recordset, you can then loop through the records to get all of teh email address (if you want one email with many recipients or loop through email addresses and call a email function.
Your code could look like this:
Dim DBAMSData
Dim rs
Dim sSQL
Dim strEmailAddr
instantiate the data source
Set DBAMSData = New clsDBAMSData
DBAMSData.Open
'Here's your query
sSQL = "SELECT eamailAddreses FROM table WHERE something = '" & grid.something.Value
'instantiate the recordset
Set rs = DBAMSData.Execute(sSQL)
'Loop - perhaps a do while is better?
If NOT rs.EOF Then
'get email addresses
strEmailAddr = strEmailAddr & rs("eamailAddreses")
'Call a emailing subroutine or function here if one email per person
'change the line above to be: strEmailAddr = rs("eamailAddreses") too!
End If
'Clean Up
rs.close
Set rs = Nothing
If DBAMSData.State = adStateOpen Then _
DBAMSData.Close
Set DBAMSData = Nothing
End If
_________________
Anything can be done, just give me time and money. |
 |
|
DavidST
|
| Posted: 06/17/2005, 7:03 AM |
|
Thanks for quick response smalloy. I implelmented your code but for somereason, I am only getting the first record in the table. Any ideas?
|
|
|
|
smalloy
Posts: 107
|
| Posted: 06/17/2005, 7:53 AM |
|
Yes, I do know why, its because I forgot to add the looping part!!! Sorry about that.
Instead of
If NOT rs.EOF Then
Use
Do While NOT rs.EOF Then
Then add "Loop" instead of End If.
You should be up and running!
_________________
Anything can be done, just give me time and money. |
|
|
|
smalloy
Posts: 107
|
| Posted: 06/17/2005, 7:54 AM |
|
ooops, ALSO - get rid of the "Then" at the end of "Do While"
Steve
_________________
Anything can be done, just give me time and money. |
|
|
|
Alexandre
|
| Posted: 08/19/2005, 5:55 PM |
|
I try to insert your code in my page and i have time out, what is wrong in this code:
Dim DBAMSData
Dim rs
Dim sSQL
Dim strEmailAddr
Dim strPrenom
Dim strNom
'Variables des champs tables employees
Dim intVinId
Dim txtToEmail
Dim txtFromMail
'Variables des champs tables bugs
Dim txtVinDescription
Dim txtSubject
' -------------------------
'Envoyer un mail :
'Préparation de la connection et des variables
Dim iMsg
Set iMsg = CreateObject("CDO.Message")
Dim iConf
Set iConf = CreateObject("CDO.Configuration")
Dim Flds
Set Flds = iConf.Fields
Flds("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
Flds("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = "c:\inetpub\mailroot\pickup"
Flds.Update
'-----------------------------------------
'on récupère les valeurs saisies par le client de la récette à la recette no
'intVinId = CCDLookUp("max(Commentaire_id)", "commentaires", "", DBConnection1)
txtFromMail = CCDLookUp("email", "members", "member_id=" & DBConnection1.ToSQL(CCGetUserID,ccsInteger), DBConnection1)
'txtToEmail = "info@finger.bz"
txtVinDescription = Commentaires.commentaire.value
'-----------------------------------------
'instantiate the data source
Set DBAMSData = New clsDBConnection1
DBAMSData.Open
'Here's your query
sSQL = "SELECT email, first_name, last_name FROM members "
'instantiate the recordset
Set rs = DBAMSData.Execute(sSQL)
Do While NOT rs.EOF
'get email addresses
strEmailAddr = strEmailAddr & rs("email")
strPrenom = strPrenom & rs("first_name")
strNom = strNom & rs("last_name")
'Call a emailing subroutine or function here if one email per person
Set iMsg.Configuration = iConf
iMsg.To = strEmailAddr
iMsg.From = txtFromMail
iMsg.Subject = "Bonjour " & strPrenom & " " & strNom & " un message de l'administrateur de la confrérie "
iMsg.Textbody = txtVinDescription
'Message fini de préparer on l'envoie
iMsg.Send
'change the line above to be: strEmailAddr = rs("eamailAddreses") too!
loop
'Clean Up
rs.close
Set rs = Nothing
If DBAMSData.State = adStateOpen Then DBAMSData.Close
Set DBAMSData = Nothing
'End If
|
|
|
|
alfi3000
Posts: 2
|
| Posted: 08/24/2005, 5:23 AM |
|
No answer, never mind I find in http://www.gotodon.com information and here is the solution for emailing to multiple people:
_________________________________________
' Database MS access and table name is "members"
Dim rs
Dim sSQL
Dim strEmailAddr
Dim strLastName
Dim strFisrtName
'-----------------------------------------
'instantiate the data source
If DBconnection1.state <> 1 Then DBconnection1.Open
Set rs = DBconnection1.Execute("SELECT email, first_name, last_name FROM members")
While NOT rs.EOF
'get email addresses
strEmailAddr = rs("email")
strLastName = rs("first_name")
strFisrtName = rs("last_name")
'Call a emailing subroutine or function here if one email per person here an example:
Set iMsg.Configuration = iConf
iMsg.To = strEmailAddr
iMsg.From = "youraddress@yourcompany.com"
iMsg.Subject = "Hello " & strPrenom & " " & strNom & ", this is an email from webmaster"
iMsg.Textbody = " Text for the body of the email"
'send message
iMsg.Send
rs.MoveNext
Wend
'Clean Up
rs.close
Set rs = Nothing
|
|
|
|
|
