Email attachment to users in final query

[clara87]

Hello, I'm trying to send via VBA code from access a file to each user where their emails are in the a "Final" query.

Ex: If the final query has 6 lines and [e-mail] has his email account 4 times, then an email will be sent to user 1 with an attachment containing 4 lines. and also another email for user2 ([e-mail]) that has 2 lines, those will be sent to him via an attached excel file

name of query Final
name offields in the FInal query: Email (contains the email of the user), name (contains the name of the user), Plant (it contains the plant where the product is manufactured) and partID (contains the product umber)

I'm not expert in access, I have been looking other trails but no able to find what I'm looking, is there some code someone can help me.. Big thanks

[zmbd]

There are a few approaches to this:

If the query has the information as you want it to appear in the excel file then the
DoCmd.SendObject (link)
May be the ticket
(You'll need to have some error handling here if you allow the user to edit the message before posting. Also, the output of the query will be very basic in nature)

If you need something more then, IMHO, you should start here with the basics of application automation, NeoPa gives an example using outlook:
home > topics > microsoft access / vba > insights > application automation

Once you have a basic understanding of this insight article we can get into the details.

(Also, there are a dozen+plus threads in the Access/VBA forum covering different aspects of how to do this... one of which could fit the bill, at the top of this page is a search box, type in "email" "query" "access" and if you want "zmbd" :) ) - even if you don't find an exact match, post back to this thread the URL for one that is closest to the goal and we'll look at what needs to be tweaked.)

-Z

[zmbd]

Hello clara87:

Normally our experts do not download unrequested files usually because we work in restricted situations so let's look at what we can do in the text.

My general advice is to avoid attachments when possible.
Looking at your query, I have the impression that you have:

[Final]
[email]          [name] [Plant]  [partID]
[[e-mail]][Mr. A][Plant 1][Part 1]
[[e-mail]][Mr. A][Plant 1][Part 2]
[[e-mail]][Mr. A][Plant 1][Part 3]
[[e-mail]][Ms. B][Plant 2][Part 1]
[[e-mail]][Ms. B][Plant 2][Part 2]
[[e-mail]][Dr. A][Plant 3][Part 1]
(place a table in the [CODE/] format and use spaces to format columns)

> Using the word "Name" as a field name can lead to other issues, it's a reserved word
Problem names and reserved words in Access (Allen Browne)
Personally would rename that field to [CstmrName]
Also, I suspect that your database may not be normalized: (Database Normalization and Table Structures)

I am guessing that you want to send something like

To: [e-mail]
Frm: [e-mail]
Sbj: Your parts order
Message: Dear Mr. A
The following parts are on their way
[From]    [Part]
[Plant 1] [Part 1]
[Plant 1] [Part 2]
[Plant 1] [Part 3]

DoCmd.SendObject can do this without too much fuss without the attachment by opening a record set against your query in vba, looping through the records building your string

If you really need the attachment then you really need to understand the application automation that I linked to in the other post

in either case, you will basically do the following

pseudo-code
open record set
 check for records, if at least one record start the build
read first record email
start loop
 read the remaining field values and add to string
  ((and/or build the excel attachment))
 move to next record
 Read email and if different from initial email then send email
Loop
 (if eof send the final email)
Close the record set

If you will attempt to build the code we can help you walk through it. There are several threads here dealing with opening and looping through record sets - use the search bar at the top of this page and keywords:
Access, Record set, Loop, email
See if any the returned threads are close to what you need and if so try to use and modify. If you run into issues you can post back a link to that thread and your current code (please use the [CODE\] formatting tool) so that we can work towards the goal.

Please check your Bytes.com-Inbox: I'll send you a boilerplate with what I hope are useful resources for beginning Access developers.