Results 1 to 5 of 5
  1. #1
    Sunshack is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    10

    EMailDatabaseObject only if object has data

    Hi!

    I wanna automate an ordering process to our suppliers that only accept orders via email (excel og pdf's).
    My wish is that the database will do the following:

    1. Download the orders from ERP-system
    2. The Access user/order manager then check "Yes" or "No" for each order to send in an Access Form, and click a "send email" button.
    3. Automatically sends each order to the correct receiver/supplier.

    Step 1 and 2 is no problem.

    But due to only basic Access knowledge, I run into an issue in step 3

    My original plan was to have a query/object for each order, and email those by using EMailDatabaseObject to the correct supplier. However, I only want to send the object if there is data in the query. In other words, I don't wanna send empty files.

    All input and suggestions is greatly appreciated! I beg you to keep in mind that my VBA-knowledge is lacking, so any code-related solutions might require some "spoon feeding"

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use Dcount() against the query?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Sunshack is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    10
    Thanks for the reply!

    I am not really sure how Dcount() would prevent me from sending out empty emails when I use EMailDatabaseObject as a part of a macro. But then again, my Access knowledge is pretty basic

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Domain aggregate functions can be executed within macro. Do an If Then conditional.

    If DCount("*", "queryname", "yes/no field name") > 0 Then

    What do you mean by "a query object for each order"? There should be only one query.

    I don't think you can really get the behavior you want with macro - need VBA. Bulk emailing with VBA is a common topic. Here is one discussion https://stackoverflow.com/questions/...t-acsendreport
    Last edited by June7; 12-11-2022 at 12:43 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I only use VBA for anything like this. I would a macro, but only for batch jobs, and then simple batch jobs.

    My thinking was that you use DCount() against your query.

    As June7 shows, only if the value returned is > 0 then send the query.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. EMailDatabaseObject
    By messier in forum Macros
    Replies: 6
    Last Post: 05-15-2021, 12:34 PM
  2. EMailDatabaseObject
    By fishhead in forum Macros
    Replies: 3
    Last Post: 04-03-2020, 12:10 PM
  3. EmailDatabaseObject
    By mick3911 in forum Macros
    Replies: 3
    Last Post: 08-07-2018, 04:03 PM
  4. EmailDatabaseObject Help
    By jimaccessnovice in forum Macros
    Replies: 4
    Last Post: 02-27-2018, 04:51 PM

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