Send E-mail from Query Results (1 Viewer)

wildsyp · Nov 13, 2008

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 · Nov 13, 2008

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:

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 · Nov 13, 2008

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

Regards
Paul

dkinley · Nov 13, 2008

No problem!

Glad it worked first shot.

-dK

fibayne · Nov 26, 2008

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 · Dec 3, 2008

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

Code:

                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 · Jan 12, 2009

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 · Jan 12, 2009

Some thoughts here:

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

I've used ClickYes successfully.

wildsyp · Jan 12, 2009

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 · Jan 13, 2009

pbaldy said:
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 · Feb 18, 2009

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 · Feb 18, 2009

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 · Feb 18, 2009

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 · Feb 18, 2009

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 · Feb 18, 2009

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 · Sep 28, 2009

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 · Sep 30, 2009

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 · Sep 30, 2009

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 · Sep 30, 2009

Try

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

CharlesWhiteman · Sep 30, 2009

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.

Send E-mail from Query Results (1 Viewer)

Registered User.

Access Hack by Choice

Registered User.

Access Hack by Choice

Registered User.

Access Hack by Choice

Registered User.

Wino Moderator

Registered User.

Registered User.

SrCtr

Wino Moderator

SrCtr

Wino Moderator

SrCtr

New member

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Similar threads

Users who are viewing this thread