Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Email Access Report to Email Addresses from an Access Query

Posted on 2014-03-20
8
Medium Priority
?
1,879 Views
Last Modified: 2014-05-03
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.
0
Comment
Question by:jaspence
8 Comments
 
LVL 85
ID: 39943960
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
 

Author Comment

by:jaspence
ID: 39945098
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39948517
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 1000 total points
ID: 39974110
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40027033
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 40026415
Not sure why it will be closed since the answers are valid.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question