Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Loop through a recordset in Access 2010

    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by RNCIENG View Post
    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?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Rui
    -------
    R4

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Rui
    -------
    R4

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great, I am glad you sorted it .
    Rui
    -------
    R4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •