469,125 Members | 1,780 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Email attachment to users in final query

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 user1@yahoo.com 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 (user2@yahoo.com) 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
May 10 '17 #1
2 855
zmbd
5,400 Expert Mod 4TB
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
May 10 '17 #2
zmbd
5,400 Expert Mod 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. [Final]
  2. [email]          [name] [Plant]  [partID]
  3. [a@trashmail.com][Mr. A][Plant 1][Part 1]
  4. [a@trashmail.com][Mr. A][Plant 1][Part 2]
  5. [a@trashmail.com][Mr. A][Plant 1][Part 3]
  6. [b@trashmail.com][Ms. B][Plant 2][Part 1]
  7. [b@trashmail.com][Ms. B][Plant 2][Part 2]
  8. [c@trashmail.com][Dr. A][Plant 3][Part 1]
  9. (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
Expand|Select|Wrap|Line Numbers
  1. To: a@trashmail.com
  2. Frm: clara87comany@Widgets.com
  3. Sbj: Your parts order
  4. Message: Dear Mr. A
  5. The following parts are on their way
  6. [From]    [Part]
  7. [Plant 1] [Part 1]
  8. [Plant 1] [Part 2]
  9. [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

Expand|Select|Wrap|Line Numbers
  1. pseudo-code
  2. open record set
  3.  check for records, if at least one record start the build
  4. read first record email
  5. start loop
  6.  read the remaining field values and add to string
  7.   ((and/or build the excel attachment))
  8.  move to next record
  9.  Read email and if different from initial email then send email
  10. Loop
  11.  (if eof send the final email)
  12. 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.
May 11 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Wald | last post: by
3 posts views Thread by Paul Lamonby | last post: by
1 post views Thread by abracad | last post: by
7 posts views Thread by erikcw | last post: by
reply views Thread by perryche | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.