Send email using addresses in a table.

[mdjks]

I have a table with email addresses and want to write code to send automatic reminders using these addresses. The group is required to submit information on a monthly basis and when it is late I need to send a reminder. The table will contain an indicator whether the person has submitted required information or not.

I don't want to hardcode all the email addresses since I need flexibility knowing there will be periodic turnover.

I've seen code like this but don't have a copy and can't find a sample.

 

[namliam]

You need code to fetch the data from the table into a string or something... ????

Is that your question?

 

[mdjks]

In my table tAdministrators the field LastResponse will contain a date. If that date is more than 25 days in the past then they need to have a reminder sent.

I want the code to pull the email address from the email field in the table and send the reminder.


DoCmd.SendObject acTable, "System Dashboard", "MicrosoftExcelBiff8(*.xls)", "mdjks@yahoo.com", "", "", "Reminder", "You are now past the due date", False, ""

 

[namliam]

And you are sure you want to be using the sendobject?

You could do a simple loop

sub test
dim rst as dao.recordset
set rst = currentdb.openrecordset("Your query")
do while not rst.eof

DoCmd.SendObject acTable, "System Dashboard", "MicrosoftExcelBiff8(*.xls)", rst!YourEmailField, "", "", "Reminder", "You are now past the due date", False, ""
rst.movenext
loop

 

[jereece]

I would like to know how to do this too, except I have a table with the individual's name as well as their email address in the database. Specifically, I have one table (tbl_Group) that lists the persons name and email address. Then I have another table (tbl_Task) that lists all the tasks, due dates and name of the person resonsible. I can create a query that will run on startup that will show all tasks that will be due within 3 days. My big question is how to I make this also email the person with a note to say something like "You have a task due or past due. Please check Task Manager and update your tasks".

Any help is appreciated.

Jim

 

[jgc31]

check out the many threads on this subject on the forum. The help file for the sendobject method and action are very comprehensive. Namliam's mail is a short overview of how to do it. If you have already created the query you are 75% there you just need to make a loop to send all the individual emails.

 

[pb21]

multiple email addresses

Of course dont forget the condition of multiple email addresses per person : I created the following code, probably not very elegant solution but it works:

Centre_Code = Me.CBAuditCode.Value
EmailSelectString = "SELECT TblContact.Centre_Code, TblContact.Contact_Method, TblContact.Contact_Data FROM TblContact "
EmailWhereString = "WHERE (((TblContact.Centre_Code) Like """ & Centre_Code & """) AND ((TblContact.Contact_Method) Like ""Email*""));"
EmailSelectString = EmailSelectString + EmailWhereString
Set Rst2 = db.OpenRecordset(EmailSelectString)
Rst2.MoveLast
Select Case Rst2.RecordCount

Case 1:
stDocName = "Audit Sample"
DoCmd.SendObject acReport, stDocName, acFormatHTML, Rst2.Fields(2).Value, , , "Internal Audit", "Please find attached an HTML report detailing the files we require for internal audit, please send asap, regards"

Case Is > 1:
Rst2.MoveFirst
For x = 1 To Rst2.RecordCount
Select Case x
Case 1
TestString = Rst2.Fields(2).Value
EmailString = TestString + "; "
Rst2.MoveNext
Case Is > 1
TestString = Rst2.Fields(2).Value
EmailString = EmailString + TestString
Rst2.MoveNext
End Select
Next x

stDocName = "Audit Sample"

DoCmd.SendObject acReport, stDocName, acFormatHTML, EmailString, , , "Internal Audit", "Please find attached an HTML report detailing the files we require for internal audit, please send asap, regards "


End Select

regards
Peter