Results 1 to 15 of 15
  1. #1
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2014
    Posts
    70

    Filter Recordset by record count

    I have hit a problem due to Google Mail e-mail limits. I have automated Outlook from Access successfully but, as currently written, my code is extracting 600 e-mail addresses from a table and passing them to Outlook. GMail refuses to send to more than 100 BCC recipients at a time from Outlook. I need to create a loop which does: Extract first 100 records, send e-mail, extract next 100 records, send e-mail etc. until we reach EOF or BOF. I know there is a Recordset.Count but is there also an index one could use to control the amount of records used at any one time ?

    Any help gratefully received, thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would just increment a variable. Test its value in the loop, when it hits the desired value send an email, reset variables and continue.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Thank you for the response. I can see how I might use Record.Count to get records 1 to 100. (Move first, move next until rc is 100). What has stumped me is how to get records 101 to 200 which is why I asked if a record set has a built in index.

  4. #4
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by jcc285 View Post
    Thank you for the response. I can see how I might use Record.Count to get records 1 to 100. (Move first, move next until rc is 100). What has stumped me is how to get records 101 to 200 which is why I asked if a record set has a built in index.
    Ok, maybe the Bookmark property is what I need. I’ll do some searches on using that

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could use AbsolutePosition if you want, but I'd just use a variable, like this air-code:

    Code:
      Do While Not rs.EOF 
        intCounter = intCounter + 1
    
        If intCounter = 100 Then
          'Call a function sending the email, using the address string
          intCounter = 0
          strAddresses = ""
        Else
          strAddressees = strAddresses & rs!EmailField & ";"
        End If
    
    
        rs.MoveNext
      Loop
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    OK, many thanks I have implemented your code.

    I have a dummy database with 40 records, 22 have an e-mail address, 2 are discarded as duplicates (Select Distinct used in the table build) leaving 20 valid records. I use .Display rather than .Send in the SendMail Function for testing purposes.

    Using 'If intCounter = 10 Then', I get two Outlook e-mail forms displayed each with 9 e-mail addresses.

    Using 'If intCounter = 11 Then', I get one Outlook e-mail form with 10 e-mail addresses.

    In the first case 2 are left behind, in the second 10 are left behind because intCounter never reaches 11.

    I tried 'If intCounter = 10 Or rs.EOF Then'. But this doesn't produce any remaining addresses either.

    Your suggestion is 99% close to a solution for me but I can't crack the last tiny bit.

    Many thanks for any additional help.


  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here to play with? After the loop, you can test the variable and send an email with any remaining addresses.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by pbaldy View Post
    Can you attach the db here to play with? After the loop, you can test the variable and send an email with any remaining addresses.
    I can’t really post the Db as this issue only relates to one small part of the whole. I’ll work on it some more to clear the remaining items after the loop is complete.
    many thanks for your help.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the entire code now? The code after the loop is simple enough:

    Code:
    If Len(strAddresses) > 0 Then
      'send your email
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Paul

    This doesn't work because jumping out of the counter at 10 produces 2 x 9 addresses, the missing 2 addresses are never added to strAddresses.

    Code:
    If Not rs.BOF And Not rs.EOF Then
      'Get total record count
        rs.MoveLast
        rs.MoveFirst
        intRecordCount = rs.RecordCount
      'Loop through the record set sending an e-mail every 10 addresses
       Do While Not rs.EOF
        intCounter = intCounter + 1
         If intCounter = 10 Then
          If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
           intCounter = 0
           strAddresses = ""
           blnEMailSent = True
          Else
           Err.Raise -101
          End If
         Else
          strAddresses = strAddresses & rs!EMail & ";"
            End If
         rs.MoveNext
       Loop
         End If
     ' Send and remaining messages
       If Len(strAddresses) > 0 Then
        If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
          strAddresses = ""
          blnEMailSent = True
        Else
          Err.Raise -101
        End If
       End If

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I did say it was air-code.

    Add the address after incrementing the counter instead of in the Else clause. Try:

    Code:
       Do While Not rs.EOF
        intCounter = intCounter + 1
        strAddresses = strAddresses & rs!EMail & ";"
         If intCounter = 10 Then
          If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
           intCounter = 0
           strAddresses = ""
           blnEMailSent = True
          Else
           Err.Raise -101
          End If
         End If
         rs.MoveNext
       Loop
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Yes, that has fixed it. Many thanks for all your help. Not sure if it is important but we are, of course, getting a ; after the 10th address.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It can be trimmed, but in my experience the email client doesn't care so I never bothered. The code to trim it would be:

    strAddresses = Left(strAddresses, Len(strAddresses) - 1)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    jcc285 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2014
    Posts
    70
    Paul.
    Once again thanks for all your help. Here is the final code:

    Code:
    'Build the address string
    Dim dbCurrent As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCounter As Integer
    Dim strAddresses As String
    Dim blnEMailSent As Boolean
    Dim strSQL As String
      Set dbCurrent = CurrentDb()
     'Define SQL this is always the same as created above
     'tbl_Bulk_EMail from user defined parameters e.g. All Members, New Members etc.
      strSQL = "SELECT DISTINCT EMail FROM tbl_Bulk_EMail"
      'Open a recordset to obtain recipients
       Set rs = dbCurrent.OpenRecordset(strSQL)
     If Not rs.BOF And Not rs.EOF Then
      'Get total record count
        rs.MoveLast
        rs.MoveFirst
        intRecordCount = rs.RecordCount
      'Loop through the record set sending an e-mail every 10 addresses
       Do While Not rs.EOF
        intCounter = intCounter + 1
        strAddresses = strAddresses & rs!EMail & ";"
         If intCounter = 10 Then
         strAddresses = Left(strAddresses, Len(strAddresses) - 1)
          If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
           intCounter = 0
           strAddresses = ""
           blnEMailSent = True
          Else
           Err.Raise -101
          End If
         End If
         rs.MoveNext
       Loop
     End If
     
    'Send any remaining email
     If Len(strAddresses) > 0 Then
      strAddresses = Left(strAddresses, Len(strAddresses) - 1)
       If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
        strAddresses = ""
        blnEMailSent = True
       Else
        Err.Raise -101
       End If
      End If
    I will mark the thread as solved.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    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. Replies: 5
    Last Post: 12-17-2014, 09:51 PM
  2. Count the number of a group in a recordset
    By Ruegen in forum Programming
    Replies: 2
    Last Post: 05-22-2014, 10:41 PM
  3. Replies: 5
    Last Post: 09-14-2012, 04:56 PM
  4. MessageBox Based on Recordset Count
    By Two Gun in forum Forms
    Replies: 3
    Last Post: 12-25-2011, 07:54 AM
  5. Filter recordset
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 01-26-2011, 10:45 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