Results 1 to 10 of 10
  1. #1
    rrowsam is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    10

    Sending reminder emails using EmailDatabaseObject to multiple people?

    I am trying to figure out a way to send an email to multiple recipients when a task is due in 30 days. I have a query that filters all active issues that are due within 30 days and has all of the relevant info.

    I am trying to use the EmailDatabaseObject to send the reminder emails, but can't figure out how to get it to send to all of the contacts in the list. Does anyone know if the EmailDatabaseObject Macro can be used to do what I'm trying to do? If not, it seems like it should be a relatively simple VBA code, but I'm not entirely sure how to accomplish it.



    Thanks!
    Robert

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    1. one email with multiple addressees

    2. multiple emails to single addressee

    In either case, loop through a recordset that contains email addresses. This is a fairly common topic. https://www.accessforums.net/showthread.php?t=70886
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rrowsam is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    10
    Thanks for the reply! That link put me on the right track. I have working code now (below), but now I'm wondering what I can do to skip duplicate email addresses? Some people have multiple issues my "Coming Due/Overdue Issues" report, and I want to just send them 1 email.

    Thanks!

    Code:
    Function cmdEmail_Click()
    Dim rs As Recordset
            Set rs = CurrentDb.OpenRecordset("select [E-mail Address] from Coming_Due_and_Overdue_Issues")
            If rs.RecordCount > 0 Then
            rs.MoveFirst
            Do Until rs.EOF
                If IsNull(rs![E-mail Address]) Then
                rs.MoveNext
                Else
                    strAddresses = rs![E-mail Address]
                    rs.MoveNext
            End If
            DoCmd.SendObject acReport, "Coming Due/Overdue Issues1", "PDFFormat(*.pdf)", strAddresses, "", "", "PATS Task Reminder", "You have a task in PATS that requires action. Please see attached document. ***This email is auto generated from Issues Database. Please do not reply!***", False, ""
            Loop
            End If
    
    
    
    
    cmdEmail_Click_Exit:
        Exit Function
    
    
    cmdEmail_Click_Err:
        MsgBox Error$
        Resume cmdEmail_Click_Exit
    End Function

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    group them in the query before running this code.

    every row in the query that's duplicate will be "grouped". If all your query results are unique you may have to use another query for the grouping.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can try:

    Set rs = CurrentDb.OpenRecordset("select DISTINCT [E-mail Address] from Coming_Due_and_Overdue_Issues") in your code above.

    Cheers,
    Vlad

  6. #6
    rrowsam is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    10
    Quote Originally Posted by Gicu View Post
    Can try:

    Set rs = CurrentDb.OpenRecordset("select DISTINCT [E-mail Address] from Coming_Due_and_Overdue_Issues") in your code above.

    Cheers,
    Vlad
    This worked, thanks!

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear!
    Vlad

  8. #8
    rrowsam is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    10
    Any reason this would spontaneously stop working? I've used it several times now and it had been working perfectly, but not it no longer populates recipients and automatically sends the email.

    Any thoughts?

    Thanks!

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Check your data to see if you have any "outliers". When something stops working it is usually data related (unless software updates break something while trying to patch something else).

    Cheers,
    Vlad

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Step debug.

    Change the SQL so only records with email are retrieved and the IsNull test won't be needed.

    SELECT DISTINCT [E-mail Address] FROM Coming_Due_and_Overdue_Issue WHERE NOT [E-mail Address] IS NULL;

    This assumes the field is actually null and does not have empty string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. sending multiple emails wth vba
    By baronqueefington in forum Programming
    Replies: 2
    Last Post: 02-09-2015, 02:49 PM
  2. Replies: 1
    Last Post: 08-06-2014, 02:22 PM
  3. Sending multiple emails
    By Sephaerius in forum Database Design
    Replies: 4
    Last Post: 08-08-2013, 11:55 AM
  4. Sending data from form to multiple emails
    By GeorgeB in forum Access
    Replies: 11
    Last Post: 01-09-2012, 04:23 PM
  5. Sending multiple emails
    By Petefured in forum Programming
    Replies: 0
    Last Post: 05-24-2011, 03:40 AM

Posting Permissions

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