Sending emails: selecting recipients from a table? Access 2007

[CazB]

In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?

I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.

If I create a simple table containing all the names, how could I then ask it to use that instead?

This is the function I'm using currently - found on here and adapted to my own purposes

Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"

Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record
DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x:\tenders\group tendering database\TenderUpdate.rtf", False
Dim Subject As String
Dim Attachment As String
Dim Recipient As String
Dim BodyText As String
Dim ExtraText As String
Dim SaveIt As Boolean
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim EmailSend As Object
Dim EmailApp As Object
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Get the sessions username and then calculate the mail file name.
 
UserName = Session.UserName
MailDbName = UserName & ".nsf"
 
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = strSendTo
MailDoc.Subject = strSubject
MailDoc.Body = strBody & "  - " & strExtraText
MailDoc.SaveMessageOnSend = SaveIt
 
'Set up the embedded object and attachment and attach it
Set AttachME = MailDoc.CreateRichTextItem("x:\tenders\group tendering database\TenderUpdate.rtf")
Set EmbedObj = AttachME.EmbedObject(1454, "", "x:\tenders\group tendering database\TenderUpdate.rtf")
 
'Send the document
MailDoc.Send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Kill ("x:\tenders\group tendering database\TenderUpdate.rtf")
 
End Function

So I guess my question is: how do I get my recipients from a table into the strsendto?

 

[pr2-eugin]

How about using Recordset to get the information from the Table, loop through them to get them in one string.. Then pass that string as strSendTo..

 

[CazB]

sounds good... but I'm a 'borrower' and 'fudger' of code and don't really know where to start when I have to do it from scratch!
Could you point me in the right direction, please?

I got this from searching the web but it was only a part of the code and it doesn't work... I don't know what it needs 'around' it to make it work? It says it doesn't know what rs and db are.... I know these are really basic things and I know I need to 'declare' them as variables but I don't know what they should be?

Set rs = db.OpenRecordset("tblRecipients")
rs.MoveFirst
Do While Not rs.EOF
strlist = strlist & rs.Fields("recipient") & ";"
rs.MoveNext
Loop
strlist = Left(strlist, Len(strlist) - 1)

 

[pr2-eugin]

That looks like the right way to go.. Try declaring them as DAO objects.. Like below..

[COLOR=Red][B]Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDB[/B][/COLOR]
Set rs = db.OpenRecordset("tblRecipients")
Do While Not rs.EOF
    strlist = strlist & rs.Fields("recipient") & ";"
    rs.MoveNext
Loop
strlist = Left(strlist, Len(strlist) - 1)

SendNotesMail [COLOR=Red][B]strlist[/B][/COLOR], "Body Message", "Additional Text", "Subject"
[COLOR=Red][B]Set db = Nothing
Set rs = Nothing[/B][/COLOR]

 

[CazB]

Thanks - looks like I'm sorted

 

[pr2-eugin]

Good Luck..