Send E-mail template based on Birthday Query

[jmes7re]

Hello,
My idea: run a access file that would check if someone makes birthday on today's day. And if yes, then a Happy Birthday e-mail would be sent automatically to that person e-mail.

Reality:

Now, I have the following Database:

1 Table - t_User (Name, Birthday, E-mail address)
1 Query - q_Aniv


1 Form - f_Aniv

For the Query, i have:

SELECT t_User.Name
FROM t_User
WHERE (((Month([Birthday]))=Month(Date())) AND ((Day([Birthday]))=Day(Date())));


For the Form, i have:

Private Sub Form_Open(Cancel As Integer)
Dim email As Long
On Error Resume Next
Err.Clear
If Me.RecordsetClone.RecordCount > 0 Then
MsgBox "Today's birthday"
Cancel = True
email = Shell("""C:\Program Files (x86)\Microsoft Office\Office16\Outlook.exe"" /f c:\temp\Aniv.msg")
Else
MsgBox "Nobody does birthday"
Cancel = True
End If
End Sub


So, with this i run the Form and my E-mail message template is open if i got someone who makes birthday today, but i'd like to fill the To field with the e-mail address from the Table t_User before send it, is it possible?

Or can i create a replica of the e-mail message in the Form and have a "Send" button to send the e-mail directly, after checking if someone does birthday "today"?

The body of the e-mail is a image, and i'd like to keep it, if possible.

Thank you!

[pbaldy]

Look at SendObject, which has an argument for using a template file. If you add the email address to the query/form you can get it from there. Your code doesn't account for the possibility of multiple people with the same birthday.

[jmes7re]

Hello, thanks for the tip.
I've changed the query to show Name and E-mail address:
SELECT t_User.Nome, t_User.[E-mail]
FROM t_User
WHERE (((Month([DataNascimento]))=Month(Date())) AND ((Day([DataNascimento]))=Day(Date())));

But i still can't put it to get the emaila ddress from the query.
Yes, my code does not account for the possibility of multiple people, i remember that yesterday afternoon, but i was trying to figure it out, and i don't know which code i have to do.
I'm not an expert on coding, and i'm googling and trying piece of codes, if i get what i want.
Now i'll look at the SendObject function, and see if it's usefull to me.
Thanks!

[pbaldy]

I don't know how you'd add the address using the method you've got. SendObject should give you more control. Here's my template code for looping a recordset. Base it on your query, and within the loop send your email. You refer to a field from the recordset like

rs!FieldName

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'your code to send emails here
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing