Results 1 to 5 of 5

Automatically send email with attachement based on due date

  1. #1
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9

    Automatically send email with attachement based on due date

    Good day ladies and gents



    I just started building a database with 2 simple tables (Customer and Vehicle_Details), with the following fields:

    1. The Customer table have fields as follow: CustId_number(autonumber), FirstName, LastName, ID_Number, Postal_Address, Street_Address, Cellphone, E-mail, and Attachment.
    2. The Vehicle_Details table have as follow: CustId_number(number), Make, Series, Licence, Register, Chassis/Vin, Engin and Expiry_Date.

    What I need the database to do is based on the expiry date in the Vehicle_Details table, an email must be sent with the attachment in Customer table 30 days before the expiry date and "on" the expiry date and 15 days after the expiry date and then on the 23rd day after the expiry date, the expiry date changes to the next year.

    Is there a way to make this fully automated even if the file is open or not?

    Just to mention, I have next to nothing coding experience.

    I thank you all in advance.

    Darryl

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    Yes, there is, but it does get complicated.

    A VBScript file can be set to run on a schedule defined in Windows TaskScheduler. Code in the script file calls Access code. Example:
    Code:
    Set accessApp = CreateObject("Access.Application")
    accessApp.OpenCurrentDataBase "C:\Users\June\recur2.mdb"
    accessApp.UserControl = False
    accessApp.Visible = False
    accessApp.Run "TestVBA"
    'accessApp.DoCmd.RunMacro "TestMacro"
    accessApp.Quit
    
    VBScript is created with a simple text editor (Notepad will serve) then rename with .vbs file extension.

    The called VBA procedure would run code sending email with filtered report.

    So you really have two parts to solution: 1) the VBA procedure 2) the VBScript.

    Tackle one at a time.
    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.

  3. #3
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    @June7, Thanks for the quick response.

    Like I said "next to nothing coding experience" but let me try disect your code above.
    The "TestMacro" I asume is the vba procedure for the email. That shouldn't be hard to fiqure out (research).
    I'm not too sure what the "TestVBA" is, would that be the form from which the macro is suppose to run?
    The rest is clear as daylight.

    I really appreaciate your help, I havent even thought about this method and a viable option, one that I will surely use, but is there maybe a way to do this purely in vba as an alternative?

    Thanks again @june7

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    That code shows example of calling a VBA procedure and a macro. I don't use macros, only VBA. Note the line has an apostrophe at beginning so it is commented and would not execute.

    Review https://support.office.com/en-us/art...7-70649e33be4f

    You said you want to automate even when the database is not open. Code cannot run unless it is called. So unless you want to manually open database every time you want emails sent, setting up a Task in the Scheduler is best I know.
    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
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    357
    Have a look at...
    https://www.access-diva.com/vba18.html

    Has step by step instructions. Hopefully, will get you started.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

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

Similar Threads

  1. Replies: 2
    Last Post: 03-12-2018, 08:16 AM
  2. Replies: 1
    Last Post: 02-11-2016, 12:35 PM
  3. Replies: 1
    Last Post: 08-14-2014, 03:37 AM
  4. Replies: 4
    Last Post: 02-05-2014, 04:05 PM
  5. Send report based on Email selections
    By wnicole in forum Access
    Replies: 9
    Last Post: 10-02-2013, 07: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
  •  
Tech Forums: Microsoft Office Forums