Hi,

I am not sure how difficult this is, but I need to send an e-mail for each record of a query (typically 5-6 records). Is there any way I can loop through each record and send an individual e-mail to each. Each record will have unique information such as invoice number so will need to be individual e-mails.

Cheers
Paul

Think something like this will work for you. You can set this up on a button or a timer, or what-have-you.



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:

qryQueryName = the name of the query you want to send e-mails from
x = # of the field with e-mail address
y = # of field with invoice number
a,b,c = # of fields if you want the e-mail body to have more information from the query (if not/more, you can delete/add as appropriate)


With (y), 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.

Hope that helps,
-dK

This is perfect! Many thanks for your help and time. :)

Regards
Paul

No problem!

Glad it worked first shot. :eek:

-dK

Hi dkinley...your thread worked well for me also and also first shot...I am trying to add as an attachment to the email a report specific to each client, your thread does the looping thru brilliantly you wouldnt have any pointers on how to attach a client specific report to each email as it loops thru ?? any help would be much appreciated..been at this for week now and going a bit crazy :) cheers Fi

The following link ...

http://www.access-programmers.co.uk/forums/showthread.php?t=155673&highlight=send+report

there is some code that shows how to do an attachment like a report.

You should only have to modify the following line in the code you have working ...


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


So check that link and look through there so you can make alterations to the above to tailor to your needs.

Hope that helps,

-dK

Hi,
I'm able to send emails using code, thanks.
Unfortunately I get a windows message warning that a virus may be trying to send an email and I have to wait several seconds before the YES button appears, I've got about 120 emails that will be sent, can I get around this ???

Cheers

Some thoughts here:

http://www.mdbmakers.com/forums/showthread.php?t=3147

I've used ClickYes successfully.

I had a similar problem, but there is no real easy way around it. The Link that pbaldy has posted was one way around it, but had issues with security (I work for a very large blue chip company). In the end I ran the script through a old networked Windows 2000 PC (where this security measure isn't installed) and it worked a treat!

Some thoughts here:

http://www.mdbmakers.com/forums/showthread.php?t=3147

I've used ClickYes successfully.

Thanks for the info, I will only need to run this code every 6 months so I don't mind overriding the Outlook security for 5 minutes every 6 months.

Cheers.

I need to set up a way to email from Access. I have email addresses in a table, I have created a query that pulls from this table - FirstName, LastName, Email Address, Yes to send email by newsletter field.

I want to send an email to everyone in this query. I want to fill in the BCC field, Subject and Message box, then I went Outlook to wait for me to Attach a file and then I click the send button. I would like it to be one email that is created with all the email addresses in it versus mutliple emails

How do I do this?

Welcome to the site. There's a lot of info here that should get you started:

http://www.granite.ab.ca/access/email.htm

I have been to this web site. I can't seem to grasp how to do this. I was really hoping for someone who could say Step 1 do this, then Step 2 do this, etc. I get all this information about put this in a module and command buttons but no real step by step instruction on exactly what I want to do. I am not sending a report or form or anything from Access. I simply want to use the email addresses stored in a table to send out an email to everyone in that table that has a yes to a specific field.

Most of us like to guide you towards solving the problem yourself, not do all the work for you. In that link is a section on loops. Their loop will send an email per address. Instead, you'll want to build a string inside the loop:

strTo = strTo & RS!cEmailAddress & ";"

which will make a string of address you can use to send one email after the loop is done.

So where can I get better instruction or training on how to accomplish this. Because I just do not fully understand the information that is given to me. There is no clear instruction anywhere in Access help or Microsoft on emailing from Access.

Hello DK,
I tried using the code but i just wanted to make sure if i need to put this code on a command button as Event procedure or somewhere else.
Thanks

You could do either; it would depend on how you wanted the process to work. It would probably be most commonly used behind a button though.

I've changed the code slightly to reflect my Db but just get an error message "Cant find the field |

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("qryEmailList", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields([Email Address])) = False Then
sToName = .Fields([First Name])
sSubject = "Test"
sMessageBody = "Email Body Text"
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

Try

If IsNull(![Email Address]) = False Then

Its producing error "Item not found in this collection" and debugs to this line:

sToName = .Fields(![First Name])

If I remove the bang then I get an error "Cant fine the field "|" referred to... Although the field is there.

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).

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

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

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

Could a outlook email template be used with this code?

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

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?

its debugging to this line

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

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

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.

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.

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!

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

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

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

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.

Thanks! That did it.

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

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!

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.

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

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

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

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:

bill@abc.com; frank@cdf.com;

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.

Regards
Steve

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.

debug.print .Fields(0)
debug.print .Fields(1)
etc...

It should be able to be dropped in right where you currently set the variable. Can you post the db?

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.

Is there a way to "filter" for that current form?

The parameter error is probably this one:

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

which can also be gotten around using the Eval() function around the form reference in the query.

Thanks Pbaldy!!

I got around it by typing in the record source instead of referencing a query. it seems to be working perfectly. :)

Larry

No problemo Larry, glad it worked for you.

How you can solve the problem with security warning, how do you avoid that?
I am using OL 2010. Thanks.


Think something like this will work for you. You can set this up on a button or a timer, or what-have-you.



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 (y), 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.

Hope that helps,
-dK