how to loop through recordset and only attach records that are true (1 Viewer)

[sspreyer]

hi ,


I have some code that loops the clone recordset of my subform and generates a email with attachments. I have mainform and continuous subform within the subform I have field called address this holds paths to files and another field called send and this is a yes/no field

now what I'm trying to do is loop through the subform if send field is true then attach file from the address path but if send field is false then do not attach file

here the code I have

Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="test@test.com"]test@test.com[/EMAIL]"
        .CC = ""
        .Subject = "tests!"
        .Body = "this is a testcode"
        
        ' Add Attachments
         With rstAttach
            If .RecordCount > 0 Then
                .MoveFirst
                Do While Not .EOF
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

thanks in advance

shane

 

[vbaInet]

Shane, is this going to be used by multiple users at the same time?

 

[sspreyer]

hi, vbaInet

no this will not be used by multiple user at the same time

thanks

shane

 

[vbaInet]

This code looks suspiciously like the one we worked on at some point

After getting the clone, use the Filter property of the recordset to filter it down to those checked records.

 

[sspreyer]

This code looks suspiciously like the one we worked on at some point
.

vbaInet Excellent memory

here my attempt I know it not right but am I going in the right direction before I go tying myself up

With rstAttach
         [COLOR=red][B].Filter = !Send
          Me.FilterOn = True
[/B][/COLOR]          
         
            If .RecordCount > 0 Then
                .MoveFirst
                Do While Not .EOF
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

thanks again

shane

 

[vbaInet]

I know when I see my code and my comments

Here's a link that explains the Filter property

https://msdn.microsoft.com/en-us/library/office/ff837300.aspx?f=255&MSPPError=-2147217396

And remove the FilterOn code.

 

[sspreyer]

hi

after looking at the link you provided me this my attempt but i think it trying to add new or edit not filter

        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
           [COLOR="Red"]  rstAttach!Send = True[/COLOR]
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

thanks in advance

 

[vbaInet]

Shane take your time and review the link again. Look carefully at how the Filter property is being used.

 

[sspreyer]

right this is my 2nd attempt still not working :banghead:

Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
[COLOR=red][B]Dim rstFiltered As DAO.Recordset
[/B][/COLOR]     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="test@test.com"]test@test.com[/EMAIL]"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
             
 [COLOR=red][B] .Filter = !Send = True
             Set rstFiltered = .OpenRecordset[/B]
[/COLOR]          
             
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

 

[Cronk]

The original issue was

if send field is true then attach file from the address path but if send field is false then do not attach file

Does this mean if the send field is false, then the email is not to be sent
OR the email is to be sent with no attachment.

If the latter, a filtered recordset is not required, just an If/Endif around the attachment add line.

 

[sspreyer]

Hi
I a main form with continous subform I just want to loop through the sub form records to find the attachment for the email if the send field is true then attach the file but if the send field is false then don't attach file

Thanks

Shane

 

[vbaInet]

It's clearly explained in the article:

    'Now filter the Recordset to return only the customers from that city
    rst.Filter = "City = '" & strCity & "'"

Now compare what you have with this, it's not even close to what you've written. And after two attempts you give up, instead of looking back at the article.

However, Cronk raised a good point. If the Send field is False (or not checked) would you still send an email without an attachment? This will determine what approach to take.

 

[sspreyer]

And after two attempts you give up, instead of looking back at the article.

To be honest I'm no way shape or form a VBA programmer I muddle by to make my life easier.

BUT I NEVER GIVE UP !!!!

However, Cronk raised a good point. If the Send field is False (or not checked) would you still send an email without an attachment? This will determine what approach to take.

if say one record the send field was false then I would send the email but with out that attachment but the next record maybe true so I would want to attach the attachment

after you point out were I needed to look

'Now filter the Recordset to return only the customers from that city
rst.Filter = "City = '" & strCity & "'" .

here my revised code it still not working but sure I made progress hopefully

Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
[COLOR=red][B]Dim rstFiltered As DAO.Recordset
    Dim strsend As String[/B][/COLOR]
    
    
    
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
       [B][COLOR=red]  strsend = rst!Send[/COLOR][/B]
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="test@test.com"]test@test.com[/EMAIL]"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
           
             
          [COLOR=red][B]     rst.Filter = "Send = '" & strsend & "'"
             Set rstFiltered = rst.OpenRecordset
[/B][/COLOR]                       
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

changes are in red

error is now object can't be found this line ( strsend = rst!Send)


thanks in advance

shane

 

[vbaInet]

Keep at it

if say one record the send field was false then I would send the email but with out that attachment but the next record maybe true so I would want to attach the attachment

In that case Cronk was right on point. You will need to use an IF statement, no filter required. If you weren't sending mails to those records with a Send value of No then you will need the Filter property.

See what happens!

 

[sspreyer]

surely you need to use a filter as its continuous subform with muiltiple records attached to the main form record ID

1) filter through records in sub form that are true

2) Attach only the records that are true to the email

I had another go at the code

  Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
Dim rstFiltered As DAO.Recordset
    Dim strsend As String
  
    
    
    
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
      
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="test@test.com"]test@test.com[/EMAIL]"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                        [B][COLOR=red] strsend = !Send = True[/COLOR][/B]
                Do While Not .EOF
           
             
[COLOR=red][B]               rstAttach.Filter = "Send = '" & strsend & "'"
             Set rstFiltered = rstAttach.OpenRecordset[/B][/COLOR]
          
             
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing

error now is data miss-match in criteria expression line: ( Set rstFiltered = rstAttach.OpenRecordset)


thanks in advance

shane

 

[vbaInet]

You have two types of records, one where Send is True and other where Send is False. You said that if Send is True or False an email should be sent regardless. However, if Send is True it should include attachment. If this is the case then you don't need the Filter property.

 

[Cronk]

Now you have answered my original question in a round about way, that you want to send an email for every record but only attach a file when
rstAttach!Send= true

There is no point in applying a filter to the recordset because you will generate only emails having an attachment ie no emails without an attachment.

Loop through the full recordset to generate an email for each record and have, as I wrote in my first response to you, use "an If/Endif around the attachment add line".

And to spell that out

if rstAttach!Send = true then
  olMail.Attachments.Add strpath
endif

 

[sspreyer]

thanks alot!!!!
Cronk and VbaInet for help this going to make my life so much easier!!!!!

 

[GinaWhipp]

Hmm, I guess I arrived too late! Next time...

 

[vbaInet]

Hmm, I guess I arrived too late! Next time...

Way too late