Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2015
    Posts
    6
    Provided Answers: 1

    Answered: vb Access Nested Loop

    Hi.

    I have a query that a list of staff with their managers name assigned.

    I want to produce a list for each manager.

    eg

    First Manager
    Bill Mary
    Graeme Mary
    tim mary
    susan jill
    harold jill
    bob peter
    jill peter
    mary peter


    How can I in VB get this to make a list of names for each manager.

  2. Best Answer
    Posted by rHausler

    "
    Code:
    Set MyDB = CurrentDb
            Set rstEMail = MyDB.OpenRecordset("Missing LLN", dbOpenDynaset)
            With rstEMail
            last = ![EmailAddressOFRegionalField]
                Do Until .EOF
                    LMSMesBody = LMSMesBody & ![FirstNameStaffField] & " " & ![LastNameStaffField] & "<Br>"
                    last = ![EmailAddressOFRegionalField]
                    lnam = ![ContactNameOfRegionalField]
                    .MoveNext
                    If .EOF Then
                        Set appOutlook = CreateObject("outlook.application")        'Create a new Microsoft Outlook session
                        Set Message = appOutlook.CreateItem(olMailItem)        'create a new message
                        With Message
                            .To = last
                            .Subject = "Subject of Email "
                            .Htmlbody = "Html Coded Email Body<br>"
                            .attachments.Add TempFilePath, OLBYVALUE, 0
                            .attachments.Add "Pathandfile.pdf"
                            .Display
                            '.Send
                        End With
                        LMSMesBody = ""
                    ElseIf last <> ![CEmail] Then
                        Set appOutlook = CreateObject("outlook.application")        'Create a new Microsoft Outlook session
                        Set Message = appOutlook.CreateItem(olMailItem)        'create a new message
                        With Message
                            .To = Last
                            .Subject = "Subject of Email "
                            .Htmlbody = "Html Coded Email Body<br>"
                            .attachments.Add TempFilePath, OLBYVALUE, 0
                            .attachments.Add "Pathandfile.pdf"
                            .Display
                            '.Send
                        End With
                        LMSMesBody = ""
                    End If
                        
                Loop
            End With
        rstEMail.Close
        Set rstEMail = Nothing
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without knowing your table design, who knows
    it should be 'doable' using a query, assuming your tables are designed to support this requirement
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Your post is lacking in enough details for us to really help. Make a list where??? In a Report...in a Combobox/Listbox...where?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #4
    Join Date
    Jul 2015
    Posts
    6
    Provided Answers: 1

    nested Loops

    Quote Originally Posted by Missinglinq View Post
    Your post is lacking in enough details for us to really help. Make a list where??? In a Report...in a Combobox/Listbox...where?

    Linq ;0)>

    Ok i have a list of staff in
    table 1 typical FirstN,LastN I also have a field with the area that they work in. eg North, South, East & West.
    there could be 56 staff in an area.

    In table 2 I have the area's with a coordinator's name and email address. Area, CName, CEmail
    There could be more than the 4 areas.

    What I need to do in VB is send a list of staff names to the Email address for each area CoOrdinator.

    I have a query that is already joined by Area But to now get the email to loop to collect each staff member and then send the email then move onto the next area group and repeat.

    I hope that this helps describe what i need assistance with.
    Thanks
    Rob.

  6. #5
    Join Date
    Jul 2015
    Posts
    6
    Provided Answers: 1
    Code:
    Set MyDB = CurrentDb
            Set rstEMail = MyDB.OpenRecordset("Missing LLN", dbOpenDynaset)
            With rstEMail
            last = ![EmailAddressOFRegionalField]
                Do Until .EOF
                    LMSMesBody = LMSMesBody & ![FirstNameStaffField] & " " & ![LastNameStaffField] & "<Br>"
                    last = ![EmailAddressOFRegionalField]
                    lnam = ![ContactNameOfRegionalField]
                    .MoveNext
                    If .EOF Then
                        Set appOutlook = CreateObject("outlook.application")        'Create a new Microsoft Outlook session
                        Set Message = appOutlook.CreateItem(olMailItem)        'create a new message
                        With Message
                            .To = last
                            .Subject = "Subject of Email "
                            .Htmlbody = "Html Coded Email Body<br>"
                            .attachments.Add TempFilePath, OLBYVALUE, 0
                            .attachments.Add "Pathandfile.pdf"
                            .Display
                            '.Send
                        End With
                        LMSMesBody = ""
                    ElseIf last <> ![CEmail] Then
                        Set appOutlook = CreateObject("outlook.application")        'Create a new Microsoft Outlook session
                        Set Message = appOutlook.CreateItem(olMailItem)        'create a new message
                        With Message
                            .To = Last
                            .Subject = "Subject of Email "
                            .Htmlbody = "Html Coded Email Body<br>"
                            .attachments.Add TempFilePath, OLBYVALUE, 0
                            .attachments.Add "Pathandfile.pdf"
                            .Display
                            '.Send
                        End With
                        LMSMesBody = ""
                    End If
                        
                Loop
            End With
        rstEMail.Close
        Set rstEMail = Nothing
    Last edited by Missinglinq; 07-29-15 at 23:46. Reason: Added Code Tags

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry does this mean this is now cleared/solved
    is it still a problem

    what problkem are you experiencing
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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