Move the SendObject below the loop, and within the loop have this line:
sToName = sToName & .Fields(5) & "; "
I'd guess you also want to create the subject outside the loop. If you want info from all the records in the body of the email, build it up in a similar manner to the address. You can add carriage returns with vbCrLf.
When I try the above, it errors out:
Run-time '2205'
Unknown message recipient(s); the message was not sent.
Do you have any ideas?
-----------------------------------------
Here's what I moved around:
-----------------------------------------
Private Sub Command12_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qry8 March 2010 Ages with CA", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(5)) = False Then
sToName = sToName = .Fields(5) & ";"
sSubject = "Reminder: Under ... "
sMessageBody = "Please see the summary details below...: " & vbCrLf & _
"Contract Admin: " & .Fields(0) & vbCrLf & _
"ECS:" & .Fields(1) & vbCrLf & _
"D.O #: " & .Fields(2) & vbCrLf & _
"Sum of Total Aging: " & .Fields(4) & vbCrLf & _
"Total Aging: " & .Fields(3)
End If
.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End With
Hi pbaldy,
I've played around with the original code but with the sToName = sToName = .Fields(5) & ";" included I get an error too.
I've taken it out and the error goes but i still get an individual email per query line.
The code now looks like this:
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Unreceipted PO Summary", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(5)) = False Then
sToName = .Fields(5)
sMessageBody = "Po No: " & .Fields(0) & " PO Date: " & .Fields(1) & " Supplier: " & .Fields(2) & " Cost Centre: " & .Fields(3)
End If
.MoveNext
Loop
sSubject = "Unreceipted PO's "
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub
It took a while to look at as I've been away for a few days.
I appreciate you looking at this though, thanks.
Well, the way you have it now, I would expect it to send one single email to the last person in the recordset. The values within the loop will keep overwriting each other. I'm not a fan of this format:
.Fields(5)
as it's not obvious what field that is. I use
rs!FieldName
which makes it obvious. Are you sure that's the email address? Have you set a breakpoint and examined the values, or added
Debug.Print sToName
so you can examine the final value in the Immediate window? With the code I posted earlier, it should look like this at the end:
I feel slightly embarassed posting this..and it took me a whole night thinking about it and another few minutes this morning...but the reason it's erroring out is because we both need to change the '=' to an '&'
From: sToName = sToName = .Fields(5) & ";"
To: sToName = sToName & .Fields(5) & ";"
Then it doesn't error and sends the email on the last recordset. So now I just need to change my looping structure (perhaps and in a nested loop while conditions match the one particular aggregated group) to get the records I want.
Ah, you mean you and Steve (I was worried I'd done it). Yes, that would certainly be a problem. Thanks for posting it, and don't be embarrassed; we've all made a few of those brain-cramp mistakes. I was really only looking at Steve's code, so didn't notice that up in the text (and never saw your original post).
You're right about my code only sending the last record pbaldy, I had stripped down my data for testing so hadn't realised. I've made changes to get this bit right now.
I'm still at a loss with sending one email per person with all of the query lines that contain their email address. I can confirm that .Field (5) is the email address on the query, that is as long as i begin with the first column of the query as 0. Is this correct?
I've never tried to produce code before so you comment about rs!FieldName and Debug.Print sToName went straight over my head, sorry.
Currently it's looking like this :
Private Sub Command14_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Unreceipted PO Summary", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(5)) = False Then
sToName = .Fields(5)
sMessageBody = "Po No: " & .Fields(0) & " PO Date: " & .Fields(1) & " Supplier: " & .Fields(2) & " Cost Centre: " & .Fields(3)
sSubject = "Unreceipted Purchase Orders"
End If
.MoveNext
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub
Wherever I've tried to place the sToName = sToName & .Fields(5) & "; " I get an error.
I suggest you stop using the .Fields(0) .Fields(1) .Fields(2) names and use the names of the fields. Rename the fields to something meaningful if needed.
I also prefer If Not IsNull([FieldName]) Then instead of If IsNull([FieldName]) = False Then
When in doubt of what is going on, print the values in the debugger to see if there really is something there.
Hey Everyone!!! thought I would join this discussion....I love this code. But I only want to send a record base on the form they are filling out. When I put a parameter in the query...I get the error messege Parameter expected. When I take that out it works fine but will not e-mail the "current" form.
Think something like this will work for you. You can set this up on a button or a timer, or what-have-you.
Code:
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qryQueryName", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(x)) = False Then
sToName = .Fields(x)
sSubject = "Invoice #: " & .Fields(y)
sMessageBody = "Email Body Text " & vbCrLf & _
"Field A: " & .Fields(a) & vbCrLf & _
"Field B: " & .Fields(b) & vbCrLf & _
"Field C: " & .Fields(c)
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
You can test this on a button, but here is what goes down ...
First, look at your query and see how your columns are defined. Note, the order of your fields, for instance if the e-mail address is in the first column, that column index is 0 (the query columns go from 0 to n).
Note a recordset uses the term 'fields' for columns so assign the correct field/column numbers in the above code:
With , I put the invoice number in the subject line - you can move it whereever, just wanted to give you a good enough example to work off of.
Also, I had it check field(x) (the email field) to see if there was an e-mail there, if not, it ignores that record.
I've used your code to generate the emails and it works great! Thank you for that. Could I ask for some help with the following issue wit this code?
This works great when the query returns values, but most of the time, the query that is being called returns no values. This causes the script to error out. Can you provide the needed code to allow for no results or null values?
Thank you in advance.
Here's the code I'm using.
-----------------------------------
Sub sendEmail()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("ItemOverdue", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(0)) = False Then
sToName = .Fields(0)
sSubject = "blah blah: " & .Fields(3)
sMessageBody = "blah blah." & vbCrLf & _
"" & vbCrLf & _
"Item: " & .Fields(3) & vbCrLf & _
"Account Number: " & .Fields(13)
DoCmd.SendObject acSendNoObject, , , sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
when I pull the query without the criteria "Between [Start Date] And [End Date]" on the field "Date" works. However it doesn't when I put that criteria. It says "run-time error '3061' too few parameters. expected 2
I'd use a form to gather the dates. You can then use the Eval() function in the query or move the parameters out to this process (new or modified lines shown):
Dim strSQL As String
strSQL = "SELECT * FROM QueryName WHERE DateField Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"
Set rsEmail = MyDb.OpenRecordset(strSQL, dbOpenSnapshot)
