Email Access Report to Email Addresses from an Access Query

I have a table in MS Access (welder_weldoperator_operator_qualifications) that tracks welder qualifications and re-qualifications. I also have two MS Access queries (due_leader_email, due_leader_email) that filters for overdue welders, based on dates, and grabs the welders email and leader email that are overdue. I want to create a macro that sends the All Due Qualifications report as an attachment in Outlook. The macro will need VBA to grab the email addresses from the due_leader_email, due_leader_email queries. I am hoping that this can be done using Access macros. I am looking for help constructing the VBA code that I could load into the macro VBA. Thanks.
jaspenceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I don't think I suggested a form ... the methods found in the linked article show you how to create an email with Outlook. You can perform that using several methods - a button click, or a VBA routine that can be called by a macro.

What VBA do you have already?

Regarding the queries - we'd have to know more about your database structure to suggest the correct SQL to use. For example, what table is storing the email addresses, and what's the field name? Do you have "criteria" that you need to use to determine the correct email(s) to use?

FWIW, I use vbMAPI from www.everythingaccess.com for my Outlook integration. It's easy to use, deploys directly with the database, and will do everything you need with Outlook.

Also, there's always Total Access Emailer from www.fmsinc.com. TAE is a complete email solution that integrates with your Access database to give you complete control over the process you describe.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't really work with Outlook using macros. You'll have to use VBA for that.

Patrick Matthews has a nice article on working with Outlook from VBA:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

There are a few sample databases included with that article, and you should be able to see exactly how to do this.

So essentially you run the query, output it to PDF, and then use the methods described above to send the email.

Give it a shot, and post back here if you run into troubles.
0
 
jaspenceAuthor Commented:
I am not looking for an Access form that buttons have to be clicked on. To clarify I want to create an email by running an Access macro that I can execute using the Windows Task Scheduler. I am struggling with the VBA for the macro to use the Access queries to generate the email addresses from while also attaching the Access Report.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Luke ChungConnect With a Mentor PresidentCommented:
Please let me know if you'd like more information on our Total Access Emailer product: http://www.fmsinc.com/MicrosoftAccess/Email.asp

It runs as an add-in and includes a VBA programmatic interface/library that creates a procedure you can run from a macro or button event. It lets you easily send emails to everyone in your list and attach a filtered report for each recipient (so they only get their data).

A free trial version is here: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Info on the programmatic interface is here: http://www.fmsinc.com/MicrosoftAccess/Email/vba-programmatic.html

Hope this helps.
0
 
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
Luke ChungPresidentCommented:
Not sure why it will be closed since the answers are valid.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.