Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Microsoft Access 365: Access VBA & Automating Outlook Email

Name: Microsoft Access 365: Access VBA & Automating Outlook Email
Rating: 4.7 (263 reviews)

Learn more about Microsoft Access VBA and using VBA to send Outlook emails from within Microsoft Access!

Created byBruce Myron

Last updated 11/2023

English

English [Auto],

What you'll learn

Create an Access Visual Basic Program that will use a one line command to Email a Report as an Attachment
Understand how to user VBA to open and control the Microsoft Outlook email program and send emails to multiple recipients.
MANY time saving VBA techniques, not just those used with Email, are explained in detail.

Course content

5 sections • 24 lectures • 3h 11m total length

Introduction4:58
Explore automating Outlook emails from Access using VBA to generate and send report PDFs instantly, with test emails and attachments, and compare macro-free approaches and self-contained email programs.
Important NOTE on DOWNLOADABLE RESOURCES0:19

Create the New Do Invoice Subroutine11:00
Replace embedded macros with a VBA do invoice subroutine that runs SQL, fills the order print table, opens the order print report, and enables sending emails via Outlook.
Create an Email Button and Subroutine9:28
Create an email button with a VBA subroutine that prompts for an email, copies the order print to a PDF, emails it as an attachment, and deletes the temporary object.
Test the One Line Email Command5:53
Learn to test the one line email command in microsoft access 365 using vba by debugging, compiling, saving, and sending an outlook email, while handling security prompts.

Inspect the Macro and PrepXL Program from the Previous Course9:00
Review the previous macro, replace it with VBA, validate the start and end dates, and set up a prep data and prep excel flow to refresh and remove connections.
Create the PrepData Function and Test Entry of Dates9:59
Create the prep data function to collect beginning and end dates, validate with is date and read val in a loop, and guard input with message boxes for date order.
Convert the First Two Queries to VBA6:54
Convert the first two queries to VBA by defining query strings, switching to design view, and replacing parameter prompts with literal date criteria to validate 'orders by date' results.
Convert the Rest of the Queries and Test PrepData15:49
Convert the remaining six queries and optimize prep data in VBA, updating the macro and test paths. Debug, adjust report dates, and finalize dynamic SQL for automated data prep.
Fix an Error on the Last Query and Retest PrepData8:15
Fix a last-query error in prep data by debugging the immediate window, adjusting the from clause, and retesting prep data and prep Excel workflows.
Add a Progress Bar and Turn On the Hourglass8:05
Turn on the hourglass during VBA processes to indicate activity, and turn it off after completion, and initialize a bottom progress meter updating through Excel prep and data refresh.
Two Ways to Get the Email from the Customer Table9:10
Learn two VBA methods to pull a customer's email into the order print in Access: binding the email field and using a query or recordset to fetch it.

Create the DoEMail VBA Function, Setup Variables and add an error trap8:38
Create the do email function in VBA, link the Outlook reference library, and automate sending from an Excel data table while implementing an error trap.
Get Email Subject and Body and finish the DoEmail function8:45
Automate sending Outlook emails from Access VBA by iterating an email list, building each message with subject and body, attaching files, sending, and updating last email date.
Create the Call to the DoEmail program and Test9:04
Learn how to modify the sales report workflow to automate Outlook emails by calling the doEmail routine, handling Excel file paths, and validating prep data for a seamless send process.
Correct a Few Errors3:30
Fix a bug in the Access VBA workflow by updating the prep Excel file to the new Excel file and triggering Outlook emails. Explore building an emailer program with VBA.
Figure Out Why the Dates are Wrong on the Excel File9:44
Troubleshoot date discrepancies in the Excel file by fixing the recordset action, refreshing links, and updating start and end dates with an update query, then save and refresh before emailing.

Initial Inspection of the Finished Database8:15
Explore a finished emailer database in Access, examining three tables, forms, and a module; learn selection by code, attachments, and sending emails via Outlook.
Look at the VBA Code Behind Some of the Buttons11:43
Learn how to call subform routines to manage records. Add attachments with a file dialogue and remove attachments using VBA in an Access 365 mail form.
Look at Code Behind Select Emails by Code and Send Email Buttons10:47
Learn how to use Access VBA to select emails by code and send emails in Outlook, including clear all, close, update queries, and handling attachments.
Download of Database Used in Next 3 Videos0:05
Using Word to Add Rich Text to the Email Body7:20
Explore how to add rich text to an Outlook email body from Access using a Word document, including an EMC Doc field and automatic salutation.
Introduction to Early and Late Binding12:30
Explore late binding versus early binding in Access VBA, manage references to Office libraries, and work with Outlook and Word objects to ensure cross-computer compatibility.
A Second Example of Late Binding2:42
Explore how to implement late binding in Access VBA to attach Word documents via a file dialog, compare with early binding, manage Office 16 Object Library references, and test deployment.

Requirements

The course files were created with the latest (as of June 2020) version of Microsoft 365 Office so it is recommended that students use this version.
This is an INTERMEDIATE Course and Basic Knowledge of Access VBA is required.
Students who have not done any VBA programming may get frustrated because I assume they know the basics and I move faster than in the first VBA course.
This course is based on the database built in the previous course in the series "Microsoft 365 Access: Link Data to Excel with Macros and VBA" and students will benefit greatly from taking that course before this one.

Description

This review says it all:

"This is the best Udemy class I've taken so far. The only thing better than learning by doing is to learn to do something USEFUL. This class teaches you a useful skill (email Excel reports) but it also shows you how easy and powerful VBA can be. It only took about 3 hours to gain useful knowledge of Excel, Access and VBA. Good audio/video quality plus example files and detailed PDF instructions. We need more like this!!" - Russ Cannizzo

This is an INTERMEDIATE Microsoft Access VBA Course and you should take the "VBA for Non-Programmers" course, or the equivalent, before taking this one. This course builds on the Macro and VBA code created in the course "Microsoft Access 365: Link Data to Excel with Macros and VBA" so it is recommended that you also take that course before this one. In the course I assume you already know how to create Access VBA functions and subroutines and I move more quickly when creating VBA Code.

This shows you several ways to use Access VBA to automate the distribution of Excel files by sending them as email attachments. We learn how to send Access objects as attachments with a one line command, then we replace the multiple query update macro created in the previous course with VBA Code. We then learn how to control Outlook and send emails to multiple recipients. We conclude by examining the VBA techniques used in a simple, yet powerful, Emailing database.

From a Student -

"This course is amazing, I thought that was more difficult to create a program to send emails from Access, but not really! Bruce's explanation makes the things easier to understand, he is an extremely good teacher, an expert in this topic. I have taken several of his courses and when I finished I have the feeling of wanting to learn more, I've definitely will continue my learning of Access with his courses, I recommend his courses a lot, very useful and interesting." - Roberto Carlos Huerta Gomez

In short, to the point videos, you will learn MANY new Access VBA techniques that you will use for years to come! This course is a great followup to the first VBA course in that it shows you the best way to use VBA to automate many common tasks, not just emailing.

* Videos show you every step of each process!

* There is no fluff and you can go through the entire course in a few hours!

From another Student -

"I'm little rusty on VBA skills yet the instructor has explained the needed commands in a way I would listen to him for hours. And files are downloadable too so I know where to return when my business clients need this great automation." - Matt S Rinc

Anyone with a solid understanding of the basics of Access VBA can easily accomplish all the tasks in the course, since every click and keystroke is shown and a finished copy of the database is provided. This is a great way to expand your VBA skills and discover more of the incredible power available with Access programming.

Think of how much time this will save you! Emailing from Access will be SIMPLE for you and you can save many hours each month! You will be able to control data in subforms in ways you did not know possible!

Another Student -

"Excellent teaching style with easy to understand explanations. Bruce is excellent when it comes to responding to queries, and is happy to hear any ideas you may have to improve (and expand) the course. Buy it!!!" - Ray McConaghy

This course could greatly increase your productivity and help you develop skills that are in very high demand in the workplace.

Click the Take This Course button and start learning right now!

NOTE: The course files were created using the latest version of Microsoft 365 Office so versions prior to 2013 may not function properly. If you have one of these earlier version, I strongly urge you to setup a subscription to Microsoft 365. It is low cost and you can update multiple computers with the entire Office suite with a single subscription.

Who this course is for:

Anyone who wants to save a great deal of time by automating the distribution of Excel reports.
Students with a basic understanding of Access VBA that would like to see examples of how to use it to increase productivity.

Microsoft Access 365: Access VBA & Automating Outlook Email

What you'll learn

Explore related topics

Course content

Overview and Email Setup2 lectures • 5min

A One Line VBA Command to Email a Report3 lectures • 26min

Replace the Orders by Date Macro from the Previous Course7 lectures • 1hr 7min

Setup of Outlook Email Automation5 lectures • 40min

Using a Simple Emailer Database7 lectures • 53min

Requirements

Description

Who this course is for: