Send E-mail from Query Results

[pbaldy]

Did you try the same syntax I posted above?

sToName = ![First Name]

If you want to use the Fields collection, you need the field name in quotes (and no bang inside the parentheses).

 

[CharlesWhiteman]

Thanks P, Yes did try and that line worked. Now the code works to that point but debugs on the following lines with error "Unknown message recipient"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False

 

[pbaldy]

Can you put the actual email address in there instead of the first name?

 

[CharlesWhiteman]

Good one! Yeah, didnt realise that it was really looking for an email address rather than To "John" etc. Changed the code as follows which now works. Hopefully this will be useful to others! Thanks P!!

f IsNull(![Email Address]) = False Then
sToName = ![Email Address]
sSubject = "Test"
sMessageBody = "Email Body Text"
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False

 

[CharlesWhiteman]

Could a outlook email template be used with this code?

 

[pbaldy]

I've never tried, but one of the arguments for SendObject looks like it's for a template. Look at that in VBA Help.

 

[CharlesWhiteman]

I been successfully tesing the code with a simple qry but when I try to use it with a Qry which includes more than one table (in qbe) I get an error "too few parameters. Expected 4" any ideas why this might be going on?

 

[CharlesWhiteman]

its debugging to this line

Set rsEmail = MyDb.OpenRecordset("qryEmailList", dbOpenSnapshot)

 

[pbaldy]

My guess is that it's not because of the multiple tables, but that the query has parameters:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[CharlesWhiteman]

I think youre right. I got around it by creating an OnOpen event on the form to a make table query (based on user filter selections) and then based the code on that table and then when the emails are sent and the form closes it deletes the table.

 

[pbaldy]

If the fields remain the same, I would just empty/append to a table, rather than deleting and recreating the table. It would be more efficient.

I think I may have been near you last week. Visited my daughter in Bury St Edmunds, plus Cambridge, Ely and of course London.

 

[CharlesWhiteman]

Hi P, Actually I tested it under V2007 runtime and I dont think runtime version likes table making! Following your advice have changed to append with delete string to empty it afterwards.

I'm actually down in Hampshire these days - must update my profile!

 

[CharlesWhiteman]

I'd be interested to see what you think about this thread?

http://www.access-programmers.co.uk/forums/showthread.php?t=180507

 

[retskcud]

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

Set MyDb = Nothing
Set rsEmail = Nothing


End Sub

 

[pbaldy]

Try just deleting this line:

.MoveFirst

The recordset will start on the first record anyway, and that line will error if there are no records.

 

[retskcud]

Thanks! That did it.

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[wmears]

Thank you all for your help with this issue. I have built a database which pulls records automatically from our server which are entered by persons needing information. I need to send a response email to each person who submits something.

Your code works great, however, I need to only send this to the new records which came in. How do I only select the new records and not send emails to each and every person everytime! I thought I could figure this out, turns out no.....

Thanks!

 

[pbaldy]

Depends on your structure (define "new records"). If you have a join date or something along those lines, the query that gets the people could be restricted on that field.

 

[Steve123]

I've been using this excellent piece of code thank you.
My query returns a number of rows for the same recipient I would like to know how I can use this to consolidate the data and send all relevant records for one person on one email?


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)
sSubject = "Unreceipted PO's "
sMessageBody = "Po No: " & .Fields(0) & "PO Date: " & .Fields(1) & vbCrLf & "Supplier: " & .Fields(2) & vbCrLf & "Cost Centre: " & .Fields(3)
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub