Database

Option Compare Database
Dim db As New DAO.DBEngine
Dim rec As Recordset

Function Email_New()
On Error GoTo Email_New_Err

    DoCmd.OpenQuery "Final Query Test 2", acViewNormal, acReadOnly
    DoCmd.GoToRecord acQuery, "Final Query Test 2", acFirst
    Call SendEmails

Email_New_Exit:
    Exit Function

Email_New_Err:
    MsgBox Error$
    Resume Email_New_Exit

End Function

Function SendEmails()
    On Error GoTo ErrorHandler

    Dim Cnxn As ADODB.Connection
    Dim strConn As String
    Dim rstMRL As ADODB.Recordset
    Dim cmdSQLMRL As ADODB.Command
    Dim strSQLMRL As String
    Dim rstFQT As ADODB.Recordset
    Dim cmdSQLFQT As ADODB.Command
    Dim strSQLFQT As String
    Dim strHoldRecString As String
    Dim strMessage As String
      
    Set Cnxn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\GELCO DATABASE\Gelco_USA.mdb;"
    Cnxn.Open strConn
    
    Set cmdSQLMRL = New ADODB.Command
    Set cmdSQLMRL.ActiveConnection = Cnxn
    strSQLMRL = "Select [Rep Number], [Rep ID], [Email Address] " & _
        "FROM [Master Rep List Test] ORDER BY [Rep Number]"
    cmdSQLMRL.CommandType = adCmdUnknown
    cmdSQLMRL.CommandText = strSQLMRL
    Set rstMRL = cmdSQLMRL.Execute()
    rstMRL.MoveFirst
    strHoldRecString = rstMRL![Rep Number]
    
    Set cmdSQLFQT = New ADODB.Command
    Set cmdSQLFQT.ActiveConnection = Cnxn

    ' Attempted Dynamic SQL here...
    strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
        "[Report #], [Email Address], [Days Aged] " & _
        "FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
        "AND [Rep ID2] = '" & strHoldRecString & "'"
    
    MsgBox strSQLFQT   ' Shows the results

    cmdSQLFQT.CommandText = strSQLFQT
    Set rstFQT = cmdSQLFQT.Execute()
    rstFQT.MoveFirst
    
    
    Do Until rstMRL.EOF
        DoCmd.SendObject acSendQuery, "Final Query Test 2", acFormatXLS, _
            "expense.reports@wrigley.com", , , "Missing Expense Reports", _
            "Hello There, you have some files missing", False
        
        '  Somehow requery the code from the second table here...
        strHoldRecString = rstMRL![Rep Number]
        strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
            "[Report #], [Email Address], [Days Aged] " & _
            "FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
            "AND [Rep ID2] = '" & strHoldRecString & "'"
        
        rstMRL.MoveNext
    Loop

    
' I am able to loop through the records for each table properly with this code which should identify the fields that I need to include.

    'Do Until rstMRL.EOF
        'strMessage = "Rep Number: " & rstMRL![Rep Number] & _
           ' vbCr & " Rep ID: " & rstMRL![Rep ID] & _
            'vbCr & " Email Address: " & rstMRL![Email Address] & ""
        'MsgBox strMessage
        'rstMRL.MoveNext
    'Loop

    'Do Until rstFQT.EOF
        'strMessage = "Confirmation #: " & rstFQT![Confirmation #] & _
           ' vbCr & " Rep Name: " & rstFQT![Rep Name] & _
            'vbCr & " Report #: " & rstFQT![Report #] & _
           ' vbCr & " Email Address #: " & rstFQT![Email Address] & _
           ' vbCr & " Days Aged: " & rstFQT![Days Aged] & ""
        'MsgBox strMessage
       ' rstFQT.MoveNext
    'Loop


ErrorHandler:
    If Not rstMRL Is Nothing Then
        If rstMRL.State = adStateOpen Then rstMRL.Close
    End If
    Set rstMRL = Nothing
    
    If Not rstFQT Is Nothing Then
        If rstFQT.State = adStateOpen Then rstFQT.Close
    End If
    Set rstFQT = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Function