Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    21

    Unanswered: Sending an email to multiple users

    Hello everyone,

    I am trying to get email information off of a subform on the current form. It is basically boils down to converting the subform data into an array in vba so I can than convert into a string then use in DoCmd.SendObject. So for I have

    Code:
    MailAddress = Forms![RCA Log].[frmSubActionItems].Form!PPR
    which...gives me the first row entry on the subform. Any way to fix this?

    Consequently, it seems that this may not be the best way to send out emails (because access freezes until you send out the email). I have just heard about RecordSet, but I am not sure how that would allow me to pull data off the subform. Any help is appriciated.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I would loop through the subform's recordset using a DAO recordset, but that could just be one approach. You're right about the freezing during an email, but this is the only easy approach to sending an email.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    21
    I feel like I am missing something here. I tried referencing the form, but it won't take. It says "Compile error: Method or data member not found" for fld.PPR , the name of the subform data set that I want. I am just printing it atm, but it will be easy to send it off to outlook after this is figured out. I appriciate the help.
    Code:
    Private Sub MailAllinForm_Click()
    
        Dim strTable As String
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        strTable = "Me!frmSubActionItems.Form.RecordSource" 'Name of the subform that has the emails
        Set db = CurrentDb() 'Current database file I am in, easy since this is the only database    
        Set tdf = db.TableDefs(strTable)
        For Each fld In tdf.Fields
            Debug.Print fld.PPR 'Name of the form field in the subform that I want
        Next
        
        Set fld = Nothing
        Set tdf = Nothing
        Set db = Nothing
    End Function
    
    
    Exit_MailAllinForm_Click:
        Exit Function
    
    Err_MailAllinForm_Click:
        'MsgBox Err.Description
        Resume Exit_MailAllinForm_Click
    
    End Function

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I was actually referring to a DAO recordset of the subform, something like:
    Code:
    'instantiate variables
    dim rs as dao.recordset
    dim strEmails as string
    
    'initiate variables
    strEmails = ""
    set rs = Forms!Mainform!Subform1.Form.recordsetclone
    rs.movelast  'populates recordset
    
    'start at the top, cycle to the end of file
    rs.movefirst
    while not rs.eof
        'add a colon between addresses
        if len(strEmails) > 0 then strEmails = strEmails & ";"
        
        'add address to string
        strEmails = strEmails & rs.fields("PPR")
    wend
    
    'release recordset from memory
    rs.close
    set rs = nothing
    
    debug.print strEmails
    Untested, off the top of my head. Give it a shot and see how that works for you.
    Me.Geek = True

  5. #5
    Join Date
    May 2009
    Posts
    21
    Well, it seems to be stuck in the while loop. CPU usage goes to 100%. The email list shouldn't be that long. Maybe 2 or 3. The whole database is only 600. It wouldn't be pulling all of those right?

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    It should only be going through the records in the subform, but even if it would go through all 600, it should take less than a second easily.

    Like I said, untested, forgot the rs.movenext statement so it was just looping on the same record over and over

    Try:
    Code:
    'instantiate variables
    dim rs as dao.recordset
    dim strEmails as string
    
    'initiate variables
    strEmails = ""
    set rs = Forms!Mainform!Subform1.Form.recordsetclone
    rs.movelast  'populates recordset
    
    'start at the top, cycle to the end of file
    rs.movefirst
    while not rs.eof
        'add a colon between addresses
        if len(strEmails) > 0 then strEmails = strEmails & ";"
        
        'add address to string
        strEmails = strEmails & rs.fields("PPR")
        
        rs.movenext
    wend
    
    'release recordset from memory
    rs.close
    set rs = nothing
    
    debug.print strEmails
    Me.Geek = True

  7. #7
    Join Date
    May 2009
    Posts
    21
    You are a lifesaver! It works perfectly! Now I just need to clean up the duplicates and be done with it.

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    By the way, not sure what kind of data validation you have when you enter in the email addresses, but in case you ever have a null string as an email address you may want to change it to:
    Code:
    strEmails = strEmails & Nz(rs.fields("PPR"),"")
    Me.Geek = True

  9. #9
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    By the way, not sure what kind of data validation you have when you enter in the email addresses, but in case you ever have a null string as an email address you may want to change it to:
    Code:
    strEmails = strEmails & Nz(rs.fields("PPR"),"")
    Ah, good point. The table is currently protected with only myself being able to open it.

    I took the code you gave me (thankyou once again), then tried to apply a filter to repeat emails. And now I am getting a "Subscript out of Range" error, though it doesn't specify where. I would guess I would have to define the array length first (though for a theoretical infinite array, I am not sure how to do this). Here is what I have.

    Code:
    Private Sub MailAllinForm_Click()
    On Error GoTo Err_MailAllinForm_Click
    
    Dim MailAddress As String
    Dim rs As DAO.Recordset
    Dim strEmails() As String
    Dim LowBound As Long, UpBound As Long
    Dim TempArray() As String, Cur As Long
    Dim A As Long, B As Long
    
    
    Set rs = Forms![RCA Log]![frmSubActionItems].Form.RecordsetClone
    rs.MoveLast  'populates recordset
    rs.MoveFirst  'start at the top
    
    A = 0
    While Not rs.EOF
        'add a colon between addresses
        
        A = A + 1
        'add address to string
        MailAddress = rs.Fields("ActionPPR")
        MailAddress = Nz(DLookup("[txtEmail]", "[qryFullUserName]", "[qryFullUserName].[txtUserID]='" & MailAddress & "'"))
        If Len(MailAddress) > 0 Then MailAddress = MailAddress & ";"
        strEmails(A) = MailAddress
        rs.MoveNext
    Wend
    
    LowBound = LBound(strEmails)
    UpBound = UBound(strEmails)
    
    ReDim TempArray(LowBound To UpBound)
    
    Cur = LowBound
    TempArray(Cur) = strEmails(LowBound)
    
    For A = LowBound + 1 To UpBound
        For B = LowBound To Cur
            If LenB(TempArray(B)) = LenB(strEmails(A)) Then
                If InStrB(1, strEmails(A), TempArray(B), vbBinaryCompare) = 1 Then Exit For
            End If
        Next B
        If B > Cur Then Cur = B: TempArray(Cur) = strEmails(A)
    Next A
    
    ReDim Preserve TempArray(LowBound To Cur)
    strEmails = TempArray
    
    MailAddress = "" 'Reusing variable
    For A = 0 To UBound(strEmails)
        A = A + 1
        MailAddress = MailAddress & strEmails(A)
    Next A
    DoCmd.SendObject , , , MailAddress
    
    Exit_MailAllinForm_Click:
        Exit Sub
    
    Err_MailAllinForm_Click:
        MsgBox Err.Description
        Resume Exit_MailAllinForm_Click
    Ah yes, it is probaly very ugly, but I am not sure of any other methods.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    If you need to figure out what line it doesn't like, then just temporarily suspend your error handling by inserting an apostrophe before it, like
    'On Error GoTo Err_MailAllinForm_Click
    and see what line it doesn't like. You may have to go into debugging mode to see what the problem is.

    What are you ultimately trying to get out of it?
    Me.Geek = True

  11. #11
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    If you need to figure out what line it doesn't like, then just temporarily suspend your error handling by inserting an apostrophe before it, like
    'On Error GoTo Err_MailAllinForm_Click
    and see what line it doesn't like. You may have to go into debugging mode to see what the problem is.

    What are you ultimately trying to get out of it?
    Take all emails from current form and subform and export to Outlook while avoiding duplicates. There are additional things that I will do once this is done (automatic warnings of due dates). I'll check in the morning and see what it is. I am sure that I didn't define a variable properly.

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Have you tried stepping through the code yet to see what the problem is?
    Me.Geek = True

  13. #13
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    Have you tried stepping through the code yet to see what the problem is?
    Yes, I went through like you said and found a variable was undefined properly. Fixed it and it works like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •