vbAccess Mail problem

[iowa20625]

Hello all,
Over the last 2 months i have been working with the vbAccess, and Access, building a lil database for a work section. I have learned a lot...

So here is the cituation:
I have a working database, sends out rosters(or queries in a form of excel) everytime i press the button every email has a txtMessage in the body, what i am trying to do is make it so that if its a certain report i want to have a different message:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Its currently not working when i try to put in the If statement determining which report...below is the attached a part of the code(i current have ' before my if thing...)

Any help is MUCH MUCH appreciated, i have tried looking by qry it sends out table field, the report and i still get nothing....

Empty_Fields is a txt field in a tblComments table.
User_Comments is another txt field in a tblComments table
tblSubReports and tblReports has the name of each qry that is attached for each report..if that makes sence.. confused yet? lol


Set db = CurrentDb()
Set rst = db.OpenRecordset("tblReports")
Set rstComments = db.OpenRecordset("tblComments")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pretty much if the report is qryEmptyFields(thats the qry name and its under 'the name of: Empty_Fields in the tblsubReports and tblReports, then i want 'Empty_Comments(which is different mesage) if else then User_Comments'
(which is a different set of txt)

Below is my closest attempt..which dont work ofcourse..i have it look in my subreports--report_name field and if that field equals empty fiels then i want certain comments..thats my logic..and its faulty..


'If (tblSubReports.Report_Name) = "Empty_Fields" Then
' TheMessageTxt = rstComments![Empty_Comments]
'Else
'TheMessageTxt = rstComments![User_Comments]
'End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' I use this table to get the Log Number to use in the loop which is the name of the folder the code creates in N:\LCM
Set rst = CurrentDb().OpenRecordset("tblReports")
Dim appOutlook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
'outputs the excel products
Do While Not rst.EOF
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rst![Report_Query], DestPath & rst![Report_Query], True
rst.MoveNext
Loop
rst.MoveFirst
Do While Not rst.EOF
Set MailOutLook = appOutlook.CreateItem(olMailItem)
With MailOutLook
If TheSelection = 2 Then 'test email
.To = TestTo 'to me
Else
.To = rst![Report_POCs] 'for regular email
End If
.CC = ""
.BCC = ""
.Subject = "(FOUO) Alpha Rosters for " & Format(Date, ("dd mmm yy"))
.Body = TheMessageTxt
Set rst2 = db.OpenRecordset("SELECT tblSubReports.Report_Name, tblSubReports.SubReport_Query FROM tblReports RIGHT JOIN tblSubReports ON tblReports.Report_Name = tblSubReports.Report_Name WHERE (((tblSubReports.Report_Name)='" & rst![Report_Name] & "'))")


(Sry for bad spelling)