Loop through records in table and output to string,then email 1

[Phil4tektips]

Hi all,

I have this code which will eventually email all the users of my database each month, I am in the process of writing it. I would like to put a loop around the section of code below to collect all the entries from a table that I have called "Reported by List".

This table contains all the users names, clock numbers, and email addresses. Can I loop through this table to output all the clock numbers to the string "strClockNo"?

Code:

strClockNo = "s02506"

strEmail = DLookup("", "[Reported by _
List]", "[Clock Number] = '" & strClockNo & "'")

'MsgBox "'" & strEmail & "'", vbCritical, "Tester!"

stDocName = "Generic Report"
'DoCmd.OpenReport stDocName, acPreview

DoCmd.SendObject acSendReport, stDocName, acFormatSNP, _
strEmail, , , "Summary of your Records for _
month)", "This is an automated monthly email direct _
from QRRT - Thankyou."

Thanks for any help in advance.....how do you post in a code window in this forum? It looks much neater than above!


~Phil4tektips~
Grant us peace in our days work!

 

[PHV]

Instead of using the DLookUp function consider a Recordset (either DAO or ADODB)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

Ok I've changed to DAO.....not too familiar with this...but

Code:

Private Sub ManReport1_Click()
On Error GoTo Err_ManReport1_Click

Dim dbs As DAO.database, rst As DAO.Recordset
Dim stDocName As String
Dim strEmail As String
Dim strClockNo As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordSet("Reported by List")

strClockNo = "s02506"

strEmail = DLookup("", "[Reported by List]", "[Clock Number] = '" & strClockNo & "'")

stDocName = "Generic Report"
'DoCmd.OpenReport stDocName, acPreview
DoCmd.SendObject acSendReport, stDocName, acFormatSNP, strEmail, , , "Summary of your Records for (month)", "This is an automated monthly email direct from QRRT - Thankyou."

rst.Close
dbs.Close

Exit_ManReport1_Click:
Exit Sub

Err_ManReport1_Click:
MsgBox Err.Description
Resume Exit_ManReport1_Click

End Sub

How do I use the RecordSet, I looked at some examples but didnt know how to apply it here. I've always used DLOOKUP!

Thanks.

~Phil4tektips~
Grant us peace in our days work!

 

[PHV]

A starting point

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordSet("Reported by List")
stDocName = "Generic Report"
With .rst
  While Not .EOF
    strClockNo = ![Clock Number]
    strEmail = ![Email]
    DoCmd.SendObject acSendReport, stDocName, acFormatSNP, strEmail, , , "Summary of your Records for (month)", "This is an automated monthly email direct from QRRT - Thankyou."
  WEnd
  .Close
End With
Set rst = Nothing: Set dbs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordSet("Reported by List")
stDocName = "Generic Report"
With .rst
  While Not .EOF
    strClockNo = ![Clock Number]
    strEmail = ![Email]
    DoCmd.SendObject acSendReport, stDocName, acFormatSNP, strEmail, , , "Summary of your Records for (month)", "This is an automated monthly email direct from QRRT - Thankyou."
    [highlight].MoveNext[/highlight]
  WEnd
  .Close
End With
Set rst = Nothing: Set dbs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

Error Message on the "With .rst" line:

Invalid or unqualified reference.

This is a split DB....I guess I need to change CurrentDB() to the Back end DB??

How do I source this when I Set dbs = (Back end)

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

I tried this:

Set dbs = "J:\POOL\QRRT\Gen\DO NOT DELETE_be.mdb"

~Phil4tektips~
Grant us peace in our days work!

 

[PHV]

Replace this:
With .rst
By this:
With rst

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

I set dbs back to CurrentDB()

and deleted deleted the offending dot!

Its now going through the table nicely.....

IS there a way to get it to send the email as well....so that I dont have to click "send" for every email?

Thanks PHV this is so much help.

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

Its fine....I just set the EditMessage property to FALSE...

since the default is TRUE.

Thanks.

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

Thanks PHV its working great....


~Phil4tektips~
Grant us peace in our days work!