Results 1 to 12 of 12
  1. #1
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12

    Automate Emailing Access Object and Identifying the email recipient

    Hello!


    Here's the end goal I want:

    To set schedule of running a report that is grouped by specific people and automatically send it to those individuals.

    I have a query set up (qryExpiredContracts) that feeds a report (rptExpiredContracts).
    I also have a query (qryUserData) pulling Name, email, Manager, and Manager Email from a table (tblUser).

    I want to match the Name in qryUserData to the Sales Owner in qryExpiredContracts to then email the Sales Owner AND their Manager (preferably filtering the report only on their specific expiring contracts)

    HELP! I am not sure where to begin.

  2. #2
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by kristiszabo View Post
    Hello!
    Here's the end goal I want:

    To set schedule of running a report that is grouped by specific people and automatically send it to those individuals.

    I have a query set up (qryExpiredContracts) that feeds a report (rptExpiredContracts).
    I also have a query (qryUserData) pulling Name, email, Manager, and Manager Email from a table (tblUser).

    I want to match the Name in qryUserData to the Sales Owner in qryExpiredContracts to then email the Sales Owner AND their Manager (preferably filtering the report only on their specific expiring contracts)

    HELP! I am not sure where to begin.

    If you're using Microsoft outlook you can use VBA and include the Reference for MS Outlook. If not, then I don't know.

  3. #3
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Yes I am using Outlook. I am just not that proficient in VBA Code. And wouldn't know how to automate it to send it to specific people and only their part of the report. I am having a mental block.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    This is a very common topic, do some searching and you'll find enough to get you going.

    Basically you need to loop through your user data query and for each Name see if they have any corresponding data in the qryExpiredContracts (using a dCount<>0) and if yes generate the (filtered) report, save it as PDF then attach it to a new email message.

    Here are some pertinent links:
    https://www.access-programmers.co.uk...emails.200225/
    http://www.granite.ab.ca/access/emai...recipients.htm

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Hi thank you for the posted links. I've actually googled and read those specific links and threads before! I just do not make sense of them. In the first link, I started to created the solution the OP did and decided it was too many queries to create and run and didn't want to bog down this database as it will be large enough with the data it will be holding. The second link makes my head spin with the additional links in it. I followed them and the one didn't even seem to pertain to emailing reports as attachments which it is titled. It simply gives info on how to create a report as a file and filter. I don't want to export the reports out to a folder. I just want access to email them from the database without me having to do anything. And also without me facilitating any destination for the reports. I won't be using this database on my machine at all once it is built. So I need it to just do this task without me having any interaction.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I want to match the Name in qryUserData to the Sales Owner in qryExpiredContracts
    This means that the same name exists in both of these queries? **Then you only need the report query. You want to send a version of the report as what, pdf? xls? Something else? You would not want to send the actual Access report object. Perhaps see

    https://docs.microsoft.com/en-us/off...cmd.sendobject

    EDIT - ** Forgot to mention that if the name is in the report query as well, you can DLookup manager, email address, etc. from the user table in your report sending code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would recommend you create a sample db with just the objects needed to illustrate your issue and some "dummy" data to support it. Your requirement usually involves a loop within a recordset of "users" to gather their email address, name, etc. and their associated data for the report (via their unique ID or name or whatever else links them to the report). If they have data (meaning the count of expired contracts is >0) then you need to open a filtered report, save it as PDF (you can do this in the front-end folder = CurrentProject.Path) then attach that to a new Outlook message.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    then you need to open a filtered report, save it as PDF (you can do this in the front-end folder = CurrentProject.Path) then attach that to a new Outlook message.
    Why not just sendobject as pdf instead of creating one?
    Might depend on whether or not the report is already filtered by its query. Then there's OutputTo method as well, once the report is opened with a filter if necessary.
    Last edited by Micron; 10-08-2021 at 05:42 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12

    Sample Database

    Here's a sample database with two tables and the "report".



    Sample Database.zip

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your db example does not contain the user table or the query that you said is the recordsource for the report. I'd say that's important. Even though a work-around could be done with your sample it would not be the correct approach. If an owner is also always the db user, then the numeric user id is what you should be storing in the table, not the owner name. More likely an owner and a user are not always the same person and that would mean it's likely you're missing a table (or more) such as tblContOwner (contract owner). Regardless, you still would not store the name.

    Neither of those tables have primary keys - an important oversight.

    Have you researched normalization when it comes to databases? You might be needing to review table design and primary keys (which is covered to some extent in normalization topics).
    Last edited by Micron; 10-11-2021 at 01:23 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    I turned the query into a make table query to make the tblExpiredContracts for sample database purposes. I didn't want the query to have to run and then I would have to create more db objects for a sample db. The contract owner will never be a database user. I just want automatic emails to them when contracts that are in their name come up to their expire date. Also cc their manager on the email as well. The tblUser is sourced by Salesforce and is all users in our organization so it is the source for Contract Owner, Manager, and Contract Executor. I did this so I wouldn't have to manually type out everyone's names for the database. Why would I need separate tables for Owner, User, etc? I want the query to pull the Sales Owner from the Expired Contracts report, match it with their email and their manager, and the manager's email, which I have that set up. But I cannot figure out how to execute this to automatically run and distribute separate emails to those with their information only.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why would I need separate tables for Owner, User, etc?
    That's how a properly normalized db should be set up. Rather than me explain it, I leave it to you to research the subject. For me, the problem with your reasoning for the sample is that if the tables are properly normalized, then anything I do won't work when you try to implement it. I'd be basing a solution on a table which you're saying doesn't actually exist.

    If they are not properly normalized then I'm not sure I want to be a party to the solution directly.

    I cannot figure out how to execute this to automatically run and distribute separate emails to those with their information only.
    The answer to that is, whatever you choose as a trigger (autoExec macro which runs on db opening?) then you'll probably need to send the report as a pdf. AFAIK, you cannot send an actual report object so you'd send it as pdf using code like that posted here:
    https://codekabinett.com/rdumps.php?...docmd-outputto

    That, then, is the indirect solution suggestion.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 05-27-2021, 08:38 AM
  2. Replies: 1
    Last Post: 09-22-2016, 12:13 PM
  3. Replies: 3
    Last Post: 09-18-2014, 07:13 AM
  4. Emailing a preset email draft from Access
    By jbickl in forum Access
    Replies: 2
    Last Post: 01-13-2012, 06:22 AM
  5. Replies: 2
    Last Post: 02-04-2010, 10:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums