How to email differnet people in a query

[MacrossHunter77]

Hi again I almost finished with a project but I need some help on how to
email diferent people with a table data here is my example

Log (This is the name of the Table)
the fields are
Tracking Date Status Name e-mail
256482 02/08/08 Complete John Mano (e-mail address removed)
268662 02/08/08 Complete Nisa Clarbo (e-mail address removed)
285425 03/16/08 Complete Mario Colla (e-mail address removed)
255482 03/17/08 Complete Mario Colla (e-mail address removed)
255225 03/20/08 InProgress Ed Martin (e-mail address removed)
256852 03/22/08 InProgress Malo Cokn (e-mail address removed)

Ok then I create a query Named "Comple Projects Status" frhis is only for
complete projects

256482 02/08/08 Complete John Mano (e-mail address removed)
268662 02/08/08 Complete Nisa Clarbo (e-mail address removed)
285425 03/16/08 Complete Mario Colla (e-mail address removed)
255482 03/17/08 Complete Mario Colla (e-mail address removed)

then I want to use a form that is link to this query and use a command button
name e-mail so I can email only this people that have the complete project
status this is what I get confused I need a code that when I Click the email
button automatically put the name of each person to the To: which the
respective information I really don't care if many windows will open I just
want that each person can recieved their complete status data for example
like this

To:[email protected]

Message:
256482 John Mano 02/08/08 Complete

OR LIKE THIS

To:[email protected]

Message:
285425 Mario Colla 03/16/08 Complete
255482 Mario Colla 03/17/08 Complete

if anyone have a clue about this please let me know I would really appreciate
if you can help me on this thanks

 

[MacrossHunter77 via AccessMonster.com]

Thank you for your response but really I just made up this emails and names
just to give you guys a idea on how I want this to work, still I exagerated a
little but Like I said these are not real name from real people or emails
still I am going to try you example that might work still I'll let you know
how that goes

Hi again I almost finished with a project but I need some help on how
to email diferent people with a table data here is my example

Hopefully you didn't actually just post REAL Email addresses of REAL people
into a public newsgroup. If so you should apologize to them immediately
about all of the SPAM they will be receiving.

You actually do not need your form bound to this query (unless for other
reasons) in order to assemble and send the Email.

What you do is create a Recordset using a query or SQL statement that
retrieves the Email addresses you want. Then you loop through that
Recordset appending each address into a string variable separated by
semi-colons. After completing the loop you use the variable as the TO
argument for SendObject or whatever other code you are using to create the
Email.

UNTESTED EXAMPLE:

Dim db as Database
Dim rs as Recordset
Dim ToVar as String
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs![e-mail] &";"
rs.MoveNext
Loop

(code to send Email)
(code to clean up)

 

[MacrossHunter77 via AccessMonster.com]

Hi Rick well I still don't know what code to use for the email code and the
code clean up still I don't have any clue on how to this can you give me a
more info about this thanks

Hi again I almost finished with a project but I need some help on how
to email diferent people with a table data here is my example

Hopefully you didn't actually just post REAL Email addresses of REAL people
into a public newsgroup. If so you should apologize to them immediately
about all of the SPAM they will be receiving.

You actually do not need your form bound to this query (unless for other
reasons) in order to assemble and send the Email.

What you do is create a Recordset using a query or SQL statement that
retrieves the Email addresses you want. Then you loop through that
Recordset appending each address into a string variable separated by
semi-colons. After completing the loop you use the variable as the TO
argument for SendObject or whatever other code you are using to create the
Email.

UNTESTED EXAMPLE:

Dim db as Database
Dim rs as Recordset
Dim ToVar as String
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs![e-mail] &";"
rs.MoveNext
Loop

(code to send Email)
(code to clean up)

 

[MacrossHunter77 via AccessMonster.com]

Nope it doesn't work rick can you give more clues on how to do this

Hi again I almost finished with a project but I need some help on how
to email diferent people with a table data here is my example

Hopefully you didn't actually just post REAL Email addresses of REAL people
into a public newsgroup. If so you should apologize to them immediately
about all of the SPAM they will be receiving.

You actually do not need your form bound to this query (unless for other
reasons) in order to assemble and send the Email.

What you do is create a Recordset using a query or SQL statement that
retrieves the Email addresses you want. Then you loop through that
Recordset appending each address into a string variable separated by
semi-colons. After completing the loop you use the variable as the TO
argument for SendObject or whatever other code you are using to create the
Email.

UNTESTED EXAMPLE:

Dim db as Database
Dim rs as Recordset
Dim ToVar as String
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs![e-mail] &";"
rs.MoveNext
Loop

(code to send Email)
(code to clean up)

 

[Rick Brandt]

Hi Rick well I still don't know what code to use for the email code
and the code clean up still I don't have any clue on how to this can
you give me a more info about this thanks

Access has a built in function calles SendObject() that will send an Email
using your default MAPI client (Outlook or Outlook Express for example).

Dim db as Database
Dim rs as Recordset
Dim ToVar as String
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs![e-mail] &";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject,,,ToVar,,,"Subject", "Message",True
rs.Close
Set rs = Nothing
Set db = Nothing

 

[MacrossHunter77 via AccessMonster.com]

Hello Rick well I tried your way and it works but there is a problem i just
noticed that it will take all the email addresses in the field but the
duplicates email will send it again and again for for example if my name is
derick and I have 5 records the code will email me 5 times for each record
also I noticed that eah individual received the same report, and I really
don't need that I need a code that can email each individual their own
records at the same time I don't want to repeat the emails again and again do
you another code to filter this name please let me know thanks

Hi Rick well I still don't know what code to use for the email code
and the code clean up still I don't have any clue on how to this can
you give me a more info about this thanks

Access has a built in function calles SendObject() that will send an Email
using your default MAPI client (Outlook or Outlook Express for example).

Dim db as Database
Dim rs as Recordset
Dim ToVar as String
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs![e-mail] &";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject,,,ToVar,,,"Subject", "Message",True
rs.Close
Set rs = Nothing
Set db = Nothing

 

[Rick Brandt]

Hello Rick well I tried your way and it works but there is a problem
i just noticed that it will take all the email addresses in the field
but the duplicates email will send it again and again for for example
if my name is derick and I have 5 records the code will email me 5
times for each record also I noticed that eah individual received the
same report, and I really don't need that I need a code that can
email each individual their own records at the same time I don't want
to repeat the emails again and again do you another code to filter
this name please let me know thanks

Modify your query to return only the email addresses and then use a DISTINCT
clause. That will eliminate any duplicates. For individual Emails you just
move SendObject inside of the loop and drop the variable...

Dim db as Database
Dim rs as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[Comple Projects Status]", dbOpenSnapshot)

Do Until rs.EOF
DoCmd.SendObject acSendNoObject,,,rs![e-mail],,,"Subject", "Message",True
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

The final True is what makes the message appear in an Outlook window so you
can send it manually. If don't need that then just change it to False. As
it is shown you will need to manually press send on each message.