Loop through a recordset in Access 2010

Topic

New Reply

I am trying to loop through a recordset to send multiple emails. Most of my code is from Microsoft and I have adapted it to my needs as far as attachments.

I need to send emails to multiple people each having a different attachment.

The code runs without error BUT it will only send the first record.

I have looked at this code trying to find the error of my ways for 3 days and trying different things, yet I can’t change the outcome. Can someone please offer some insight as to what I’m doing wrong? Please?

Thanks in advance.

Code:

Public Sub Send_Second_Attempt_Fax()

Dim MyDB As Database
Dim MyRS As Recordset
  
Dim str_Report_Name As String
Dim str_MyFilename As String
Dim str_myAttach As String
Dim str_MyPath As String
Dim str_ToFaxName As String
Dim str_FaxNum As String

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(“qry_MyQRYName”)
MyRS.MoveFirst

‘ Create the Outlook session.
Set objOutlook = CreateObject(“Outlook.Application”)

Do Until MyRS.EOF

‘Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = “[FAX: ” & str_ToFaxName & “@” & Me.MYFaxToNum & “]”

    With objOutlookMsg
    ‘Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo
    
        str_MyPath = “myPath”
        str_ToFaxName = Me.txt_To
        str_Report_Name = “rpt_2ndAttempt_MRR_FCS”
        str_MyFilename = str_ToFaxName & “_MRR.pdf”
    
    ‘Set the Subject, the Body, and the Importance of the e-mail message.
        .Body = “Please See Medical Record Request attachment”
        .subject = “HEDIS Medical Record Review–2nd Attempt”

        DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, str_MyPath & “” & str_MyFilename, False
        str_myAttach = str_MyPath & “” & str_MyFilename
        ‘AttachmentPath = str_myAttach

    ‘Add the attachment to the e-mail message.
    If Not IsMissing(str_myAttach) Then
        Set objOutlookAttach = .Attachments.Add(str_myAttach) ‘(AttachmentPath)
    End If
MsgBox str_myAttach
    ‘Resolve the name of each Recipient.
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
        .Send
        Me.txt_HEDIS_Fax_Date_Attempt2 = Date
        End With
        MyRS.MoveNext
    Loop
            
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

Reply | Quote

Replies

I am trying to loop through a recordset to send multiple emails. Most of my code is from Microsoft and I have adapted it to my needs as far as attachments.

I need to send emails to multiple people each having a different attachment.

The code runs without error BUT it will only send the first record.

I have looked at this code trying to find the error of my ways for 3 days and trying different things, yet I can’t change the outcome. Can someone please offer some insight as to what I’m doing wrong? Please?

Thanks in advance.

Code:

Public Sub Send_Second_Attempt_Fax()

Dim MyDB As Database
Dim MyRS As Recordset
  
Dim str_Report_Name As String
Dim str_MyFilename As String
Dim str_myAttach As String
Dim str_MyPath As String
Dim str_ToFaxName As String
Dim str_FaxNum As String

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(“qry_MyQRYName”)
MyRS.MoveFirst

‘ Create the Outlook session.
Set objOutlook = CreateObject(“Outlook.Application”)

Do Until MyRS.EOF

‘Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = “[FAX: ” & str_ToFaxName & “@” & Me.MYFaxToNum & “]”

    With objOutlookMsg
    ‘Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo
    
        str_MyPath = “myPath”
        str_ToFaxName = Me.txt_To
        str_Report_Name = “rpt_2ndAttempt_MRR_FCS”
        str_MyFilename = str_ToFaxName & “_MRR.pdf”
    
    ‘Set the Subject, the Body, and the Importance of the e-mail message.
        .Body = “Please See Medical Record Request attachment”
        .subject = “HEDIS Medical Record Review–2nd Attempt”

        DoCmd.OutputTo acOutputReport, str_Report_Name, acFormatPDF, str_MyPath & “” & str_MyFilename, False
        str_myAttach = str_MyPath & “” & str_MyFilename
        ‘AttachmentPath = str_myAttach

    ‘Add the attachment to the e-mail message.
    If Not IsMissing(str_myAttach) Then
        Set objOutlookAttach = .Attachments.Add(str_myAttach) ‘(AttachmentPath)
    End If
MsgBox str_myAttach
    ‘Resolve the name of each Recipient.
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
        .Send
        Me.txt_HEDIS_Fax_Date_Attempt2 = Date
        End With
        MyRS.MoveNext
    Loop
            
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

I’ve only had a quick scan, but you seem to be opening a recordset called MyRS, moving through each record, but not actually using any data from the recordset. As far as I can see, the info that you are using to build the Outlook message comes from properties of the Me object which would suggest that they are coming from the current state of a form rather than the current record in the recordset.

Have I missed something?

Reply | Quote

Jeremy,

Let me be more clarify.
I have a bound form, recordset is qy_MYQRYName (generic for posting purposes). I would rather not use a form at all only adopted the form usage based on Microsoft Office’s code. I would prefer to simply use the qry as the recordset and code to loop through that. I simply don’t know how to make that happen and have tried several things. Again, I don’t get any errors and the code runs but only captures the first record. I would love some assistance with this if you could offer.

Thanks so much for your time.

Reply | Quote

As Jeremy stated, you are not using the information from the recordset when sending the emails. The emails keep using this:

str_ToFaxName & Me.MYFaxToNum
“myPath”
Me.txt_To

I suppose you’d need to change each of them by the proper field from the My.Rs recordset. Beware that now, the path is being set literally to the value “mypath”. Not sure if that is intended, either.

Reply | Quote

Thanks R4. I’ve made the change as he suggested. The str_ToFaxName is the only variable I need from the recordset. The path is where the report resides. The report is named using the str_ToFaxName and it’s recordset has a parameter based on the form’s text field str_ToFaxName and is a pdf file. So, the path is static but the report name is not. Should I do something different there?

Since I made the change as suggested, the code does loop correctly now but now I get the same report. I’m so confused

Reply | Quote

Question is, the Access report being used to generate the report is not dependent on the current record of the recordset, is it? That’s probably why you get the same report attached. To get it right, you probably need to change the way the report “determines” the record to use. Probably the quickest way to do it would be to have the report use a table record to determine which user should have its record printed and your code would set that value, using the current record from the recordset, before creating the report.

P.S.: R4 is my blog and that’s what is linked in my signature. You can simply use my username (ruirib) if you need to address me.

Reply | Quote

First, I apologize for the name error.

Secondly, I finally have the problem resolved. I accomplished this by the following:

I created a Public Function
Public strPVDID As String
Public Function GetPVDId() As String
GetPVDId = strPVDID
End Function

In the query for the report(s) I pass strPVDID as a parameter.

strPVDID changes as the code goes through the loop

AND–
Changed:str_ToFaxName = Me.txt_To
To:strPVDID = MyRS.Fields(“txt_HEDIS_Fax_To”).Value

Changed:str_ToFaxName & “_MRR.pdf”
To: str_MyFilename = strPVDID & “_HEDIS_MRR.pdf”

Thanks for all the help everyone.

Reply | Quote

Great, I am glad you sorted it :).

Reply | Quote