Nested Loop...list invoices due

[lcook1974]

Hey Everyone!

I have an email code that works but will not list the invoices that are due. I have a loop that looks at email address' and adds them to sToName line. I have a static message that will need to list the outstanding invoices at the end of the statement. That's where i'm having trouble.

Private Sub cmdEmail_Click()
Dim MyDb As Database
Dim rsEmail As DAO.Recordset
Dim rsInvoice As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("SELECT tblContact.OrganizationID, tblContact.ContactName, tblEmail.PrimaryEmail, tblEmail.SecondaryEmail, tblEmail.ThirdEmail " & _
                                 "FROM tblContact INNER JOIN tblEmail ON tblContact.ContactID = tblEmail.ContactID " & _
                                 "Where (tblContact.OrganizationID) =  " & Me.OrganizationID)
 
Set rsInvoice = MyDb.OpenRecordset("Select * From qInvoiceData WHERE [Company ID] = '" _
                                   & Forms![frmMainEntry]![frmInvoiceData].Form![Company ID] & "'")
[COLOR=palegreen]'Every from here to the next comment works great[/COLOR]
With rsEmail
    Do Until rsEmail.EOF
        If IsNull(rsEmail!PrimaryEmail) = False Then
             sToName = sToName & rsEmail!PrimaryEmail & ";"
        End If
    .MoveNext
    Loop
sSubject = "Finances are due for " & Me.OrganizationName
sMessageBody = "Please address the listed invoice number/numbers. " & vbCrLf & _
                "If you have received this communication in error, please provide the correct contact information for this request by reply email to sender." & vbCrLf & _
                vbCrLf & _
                "Please see list of invoices. " & vbCrLf
End With
 
 
'This part doesn't work...I'm trying to list off the invoices that are over due
With rsInvoice
    Do Until rsInvoice.EOF
        If IsNull(rsInvoice!Invoice) = True Then
 
sMessageBody = sMessageBody & rsInvoice!Invoice
End If
.MoveNext
Loop
End With
DoCmd.SendObject acSendNoObject, , , sToName, , , sSubject, sMessageBody, True, False
 
 
End Sub

So after the text " Please address the listed invoice number/numbers" I want a list of the invoices.

Any help, kick or nudge in the right direction would be great!

Thanks!
larry

 

[CBrighton]

Shouldn't "If IsNull(rsInvoice!Invoice) = True Then" be "If IsNull(rsInvoice!Invoice) = false Then"?

Othewise you are only trying to populate the email with records which contain null?

 

[lcook1974]

Dang it!!! I had tried it that way but it didn't work, of course it works now!!!

Thank you! I'll worry about the formatting next

This is what came up...

Please see list of invoices.
T1-10-002T1-10-005T2-10-011BT1-10-026T2-10-018T2-10-019T1-10-073T1-10-078T1-10-079T2-10-031T2-10-032T2-10-033T2-10-034T2-10-035T1-10-088T1-10-89T1-10-087T1-10-092T1-10-094T1-10-097T1-10-102T1-10-096T1-10-104T2-10-037T1-10-108T1-10-110T1-10-113T1-10-117T1-10-119T1-10-125T2-10-040T2-10-043T1-10-114T1-10-115T1-10-118T1-10-120T1-10-121T1-10-122T1-10-126T1-10-127T1-10-129T1-10-131T2-10-036T2-10-041T2-10-042T1-10-130T1-10-116T1-10-132T1-10-133T1-10-135T1-10-128T1-10-141T1-10-142T2-10-049T2-10-050T2-10-051T1-10-137T1-10-139T1-10-143T1-10-144T1-10-145T1-10-146T1-10-148T2-10-046T2-10-047T2-10-048T2-10-052T2-10-053T1-10-136T1-10-140LSH-0000006409T1-10-147T1-10-152T2-10-055T2-10-056T2-10-057T1-10-155T1-10-156T1-10-151T1-10-153T1-10-154T1-10-159T1-10-162T1-10-163T1-10-167T1-10-157T1-10-168T1-10-169T2-10-061T2-10-062T2-10-063T1-10-170T1-10-160T1-10-161T2-10-059T2-10-058T1-10-171T1-10-173T1-10-174T1-10-175T1-10-176T1-10-177T1-10-178T2-10-064T2-10-065T1-10-172T2-10-060T1-10-101T1-10-179T1-10-186T1-10-190T1-10-193LSH-0000006424


Which is the list of invoices...

 

[CBrighton]

Should only be a couple of extra lines within that last If statement for the formatting though. Not too bad.

 

[lcook1974]

Done...

Thank you CBrighton!!!