how to loop through recordset and only attach records that are true

S

sspreyer

Registered User.
Local time
Yesterday, 21:12
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
 
Shane, is this going to be used by multiple users at the same time?
 
hi, vbaInet

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

thanks

shane
 
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.
 
vbaInet said:
This code looks suspiciously like the one we worked on at some point ;)
.
Click to expand...

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
 
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
 
Shane take your time and review the link again. Look carefully at how the Filter property is being used.
 
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
 
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
Click to expand...

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.
 
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
 
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.
 
vbaInet said:
And after two attempts you give up, instead of looking back at the article.
Click to expand...

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

vbaInet said:
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.
Click to expand...

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

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

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
 
Keep at it :D
sspreyer said:
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 :)
Click to expand...
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!
 
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
 
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.
 
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
 
thanks alot!!!!
Cronk and VbaInet for help this going to make my life so much easier!!!!!:)
 
You must log in or register to reply here.

Similar threads

A
Automation Error
Replies
10
Views
247
The_Doc_Man
The_Doc_Man
LarryE
Solved Using ACCESS, WORD and OUTLOOK to send bulk E-Mails
2
Replies
38
Views
946
MajP
MajP
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
696
Minty
Minty
M
Solved Update records through a recordset
Replies
11
Views
1,118
Pat Hartman
P
N
Solved MS Access Outlook VBA fixing
Replies
5
Views
607
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom