Send E-mail from Query Results

[wildsyp]

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

 

[dkinley]

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 , 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

 

[wildsyp]

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

Regards
Paul

 

[dkinley]

No problem!

Glad it worked first shot.

-dK

 

[fibayne]

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

 

[dkinley]

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

 

[gadjet]

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

 

[pbaldy]

Some thoughts here:

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

I've used ClickYes successfully.

 

[wildsyp]

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!

 

[gadjet]

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.

 

[srctr]

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?

 

[pbaldy]

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

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

 

[srctr]

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.

 

[pbaldy]

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.

 

[srctr]

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.

 

[rtulshan]

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

 

[pbaldy]

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.

 

[CharlesWhiteman]

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

 

[pbaldy]

Try

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

 

[CharlesWhiteman]

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.