Results 1 to 4 of 4

Send E-mail template based on Birthday Query

  1. #1
    jmes7re is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2

    Question Send E-mail template based on Birthday Query

    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!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,592
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jmes7re is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2
    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!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,592
    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

    Code:
      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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populate an Outlook e-mail template
    By jcc285 in forum Programming
    Replies: 1
    Last Post: 10-03-2019, 03:46 PM
  2. Replies: 1
    Last Post: 06-15-2018, 09:48 AM
  3. Calculate age based on birthday
    By MediaCo in forum Access
    Replies: 10
    Last Post: 10-14-2014, 01:00 PM
  4. Replies: 1
    Last Post: 09-14-2012, 10:27 AM
  5. VBA to Send E-mail to Addresses from Query
    By alpinegroove in forum Programming
    Replies: 4
    Last Post: 12-23-2011, 09:45 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums