Can't connect Macro and Query

[RobInAZ]

This is my first post and I have limited experience with Access.
I hope that my terminology is correct.
I have a table titled 3MesaMstr that has four fields including EstComplDate (date/time), ActivityID (text), Email (text), and 30DayRmndr (date/time). I have a Select query that selects records from 3MesaMstr where:
3MesaMstr is null
AND
EstComplDate is less than 31 days away
AND
Email no criteria, show only

I have a function that creates an email.

My goal is upon opening the Master form (or even clicking a button on the form) Access will:
1. Run the query.
2. Take the query results and email EstComplDate and ActivityID to the specific Email field. Not the entire results to all emails. Just email each result to the single email address in the record.
3. Write the current date to the 30DayRmndr upon emailing.

I tried to complete this action using a macro without success.

I have attached a simple version of the database.

Thank you for any help

-Rob

 

[Trevor G]

This can be achieved via VBA, sample code that you will need to adjust is shown below:

Dim rs As Recordset
Dim rs Emailee As Recordset
Dim strBody As String
Dim strRecipient As String
Set rsEmailee = Currentdb.OpenRecordSet("YourEmailList",dbReadOnly)
Set rs = Currentdb.OpenRecordSet("YourQuery",dbReadOnly)
'read only will allow you to open your recordset faster

rs.MoveLast
rs.MoveFirst
'Access can sometimes error on the record count without moving to the last record and back to first. This prevents that error.

'cycle through all of the records. This could be done using rs.EOF (end of file) but takes longer to execute as it always rechecks where it is.
For i = 1 To rs.RecordCount
'set the body of the message
strBody = rs!Field_Of_Message_Body
'set the recipient of the message
strRecipient = rsEmailee.FindFirst("Your_Emailee_Name_Field = ' " & rs!Field_of_Emailee & " ' ")
'structure of the SendObject DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
DoCmd.SendObject ,,,strRecipient,,"Your Subject Line Here",strBody,False

'move to the next record in the line and then cycle the for loop to the next i
rs.MoveNext
Next i
'clean up
set rs = Nothing
set rsEmailee = Nothing

 

[RobInAZ]

Trevor,
Thank you for your response. I really appreciate it. I am working with the code you supplied right now.

-Rob

 

[RobInAZ]

Trevor,
I have been working with the code you provided for hours today. I made some progress but I am getting an error on:
strRecipient = rsEmailee.FindFirst("Email = ' " & rs!Email & " ' ")

CODE:



Public Sub ProvidedCode()

Dim rs As Recordset
Dim rsEmailee As Recordset
Dim strBody As String
Dim strRecipient As String
Set rsEmailee = CurrentDb.OpenRecordSet("Email", dbReadOnly)
Set rs = CurrentDb.OpenRecordSet("Query1", dbReadOnly)
'read only will allow you to open your recordset faster

rs.MoveLast
rs.MoveFirst
'Access can sometimes error on the record count without moving to the last record and back to first. This prevents that error.

'cycle through all of the records. This could be done using rs.EOF (end of file) but takes longer to execute as it always rechecks where it is.
For i = 1 To rs.RecordCount
'set the body of the message
strBody = rs!EstComplDate
'set the recipient of the message
strRecipient = rsEmailee.FindFirst("Email = ' " & rs!Email & " ' ")
'structure of the SendObject DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
DoCmd.SendObject , , , strRecipient, , , "Your Subject Line Here", strBody, False

'move to the next record in the line and then cycle the for loop to the next i
rs.MoveNext
Next i
'clean up
Set rs = Nothing
Set rsEmailee = Nothing

End Sub



My table is named: 3MesaMstr
Query is named: Query1
The field that stores the email address is named: Email

Any idea what I'm doing wrong?

Thank you,
Rob

 

[CJ_London]

What you should have is

'set the recipient of the message
rsEmailee.FindFirst("Email = ' " & rs!Email & " ' ")
strRecipient = rsEmailee("Recipient")

 

[RobInAZ]

CJ,
Thank you for your help.
The code you provided fixed my issue.

-Rob