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

sspreyer

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
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

Code:
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

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
Shane, is this going to be used by multiple users at the same time?
 

sspreyer

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
hi, vbaInet

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

thanks

shane
 

vbaInet

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
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

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
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 :)

Code:
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
 

sspreyer

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
hi

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

Code:
        ' 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

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
Shane take your time and review the link again. Look carefully at how the Filter property is being used.
 

sspreyer

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
right this is my 2nd attempt still not working :banghead:

Code:
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

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 4, 2013
Messages
2,515
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

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
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

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
It's clearly explained in the article:
Code:
    '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

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
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 !!!!:D

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

Code:
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

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
Keep at it :D
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

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
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

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

AWF VIP
Local time
Today, 18:58
Joined
Jan 22, 2010
Messages
26,374
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

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 4, 2013
Messages
2,515
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
Code:
if rstAttach!Send = true then
  olMail.Attachments.Add strpath
endif
 

sspreyer

Registered User.
Local time
Today, 10:58
Joined
Nov 18, 2013
Messages
251
thanks alot!!!!
Cronk and VbaInet for help this going to make my life so much easier!!!!!:)
 

Users who are viewing this thread

Top Bottom