Solved

Email Access Report to Email Addresses from an Access Query

Posted on 2014-03-20
8
1,775 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 250 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 250 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 48

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

631 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