UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Loop Recordset With CDO Sendmail, Access 2010    
 
   
LilAnnCC1
post Dec 27 2017, 09:12 AM
Post#1



Posts: 754
Joined: 31-May 04
From: Wisconsin, USA


I am having an issue with this loop. I want each record to be sent as an email to the creator of the record. In testing I am using 3 records (2 records to one recipient, and 1 record to another).
What am I doing wrong with this loop?

CODE
Public Function ProblemPO()
'Send emails to PO creators when there are issues with PO's

Dim rst As DAO.Recordset
Dim strMess As String
Dim strSub As String

Set rst = CurrentDb.OpenRecordset("qs_POProblems", dbOpenDynaset)

strMess = "The accounting department has determined that " & rst!fPONo & " has an issue." & vbCrLf & vbCrLf & "Remember to uncheck PO Issue on the PO form after fixing Purchase Order!"
strSub = " Problem PO " & rst!fPONo

With rst
        If rst.EOF And rst.BOF Then
            'do nothing as there are no records
        Else
            
            Do Until rst.EOF
                rst.Edit
                rst![POEmailDate] = Date
                rst.Update
                rst.MoveNext
                Call SendEmail(rst![Email], "", strSub, strMess)
            Loop
End If
End With

    rst.Close
    Set rst = Nothing
    

End Function

This post has been edited by LilAnnCC1: Dec 27 2017, 09:20 AM
Go to the top of the page
 
cheekybuddha
post Dec 27 2017, 09:22 AM
Post#2


UtterAccess VIP
Posts: 10,469
Joined: 6-December 03
From: Telegraph Hill


You haven't explained how it's not working!

From a glance, I think you want to call SendMail() before .MoveNext

Also, you don't need to qualify with rst within the With/End block. (Or you can omit the With/End block instead).

When sending CDO mail it can also help to add DoEvents or a Sleep function call between each call to SendEmail()
Go to the top of the page
 
LilAnnCC1
post Dec 27 2017, 10:18 AM
Post#3



Posts: 754
Joined: 31-May 04
From: Wisconsin, USA


I'm sorry! Shame on me!

It is sending out 3 emails to 1 person (who is supposed to get 2) and 1 email to the other recipient, but they all have the same PO number on them! I need an email to go for PO 2101, 2103 and 2105. They are getting the same email for PO 2101.

I tried moving the sendmail to before movenext, but it still didin't pull the correct PO Number.

I am rather lost when it comes to recordsets and loop (actually, all things VBA, SQL) and do my best to try and understand what I'm doing, so I'm not sure how to omit With/End. Also have never used DoEvents or used a sleep function.
Go to the top of the page
 
LilAnnCC1
post Dec 27 2017, 11:20 AM
Post#4



Posts: 754
Joined: 31-May 04
From: Wisconsin, USA


This is how I changed it and it seems to work now!

CODE
If rst.EOF And rst.BOF Then
    'do nothing as there are no records
Else
        strMess = "The accounting department has determined that " & rst!fPONo & " has an issue." & vbCrLf & vbCrLf & "Remember to uncheck PO Issue on the PO form after fixing Purchase Order!"
        
rst.MoveFirst
            Do Until rst.EOF
                        
                rst.Edit
                rst![POEmailDate] = Date
                rst.Update
                strSub = " Problem PO " & rst!fPONo
                Call SendEmail(rst![Email], "", strSub, strMess)
                rst.MoveNext
            Loop
            
End If

    rst.Close
    Set rst = Nothing


Thank you for pointing me in the right direction!
Go to the top of the page
 
cheekybuddha
post Dec 27 2017, 12:08 PM
Post#5


UtterAccess VIP
Posts: 10,469
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

It's great when you work it out yourself!

d
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 06:47 AM