Goals
There are four major goals for today's
class.
-
You will learn how to send email
with and without using Microsoft Outlook.
-
You will learn how to send bulk
email batches to groups of customers on set mailing lists using
plain text, rich text, or HTML formatted emails.
-
You will build a separate email
server database whose job is to handle the processing of email for
all of the users on your network.
-
You will learn how to extract data
(including attachments) from incoming emails using Outlook, parse the
data into your Access tables, and save attachments to your hard drive.

Sample
Database
You can download the database that is
built in this class so you can test it and make sure it's right for your
needs.
Click here to download (Access 2013 format).
Important note: in the preview
database, only the forms that send email via Outlook will
work. The SMTP (non-Outlook) code will NOT work because you need
to edit the source code and specify your mail server settings, username,
and password. These features are only available in the FULL
database (which you get a copy of when you buy the seminar).

Here's What
You'll Learn
You will start by learning how to send
email from Access using Outlook by sending reports from
your database directly to a single email.
You will learn about the SendObject
and EmailDatabaseObject macro and VBA commands. We'll talk about
the Outlook Security Warning and what it means.
You will learn how to create nice,
formatted emails using Microsoft Word. We will use our Access
data in a Word Mail Merge to send multiple emails using Outlook.
You will learn about inserting pictures, hyperlinks, and more.

Next we'll build our own email form and
send text email direct to Outlook without having to use a database
report. You'll learn about the Outlook Application Library and
how to add a reference to it in your VBA editor.

You'll learn how to send formatted,
rich text and HTML messages direct to Outlook. We'll see how
to upload images to a public folder on the Web so that your
recipients don't have to download them in their email.

Next we'll create a separate form for
sending HTML formatted emails. The concept is to create an HTML
email in a web editor (or even Word, saved as HTML). Then we just copy
and paste the HTML code directly into our email database and click send.

You will next learn how to send
attachments as part of your emails via Outlook. We'll create ZIP
files, check if a file exists, use the Common Dialog DLL to click on a
"select file" button and browse for the files to attach.

Next we will see how to automatically
export an Access report as a PDF file using VB code. We'll
save these PDF files to a folder on your hard drive, and then use them
as attachments in outgoing emails. Great for sending customer statements
or invoices.

Now we'll start learning how to send
bulk email using Outlook. We'll create a customer list form, select
specific customers to mail to, generate a loop to cycle through
each of the customers, and send email to the selected customers. I will
also show you how to disable the Outlook Security Warning so
you're not prompted each time you send an email. We'll send balance
statements to all customers with a balance on their account over zero
dollars.

Next we'll begin a series of lessons on
sending email without using Outlook. We will learn how to connect
directly to an SMTP mail server. You will still need an email
account with an Internet provider, but this will allow you to send
directly to their mail server using Access directly, without needing an
intermediate program like Outlook between you. We'll learn about the
Collaborative Data Object (CDO) programming library.

We will build a more robust form
for sending email. You'll be able to select the format (plain, rich
text, HTML), specify CC and BCC recipients, and more.

Next we will learn how to connect to
Gmail to send mail using SSL (Secure Sockets Layer). We'll also
learn how to send "friendly" names on our emails. You will learn how to
send email attachments using this method. You will also lean how
to handle errors during the transmission of the email to the server.

You will learn how to create an email
based on a web page or HTML document. If you know a little bit
about web editing, you'll be able to set up a web page, format it as an
email (newsletter, for example) and then with a few lines of code send
that out as an email, automatically.

Next we'll learn how to use Recordsets
to loop through the records of our database in VBA code. This will allow
us to more easily send large batches of bulk email. We'll create our own
Merge Codes in our emails so you can customize them with phrases like "Dear
*FIRSTNAME*" turning into "Dear Joe" when the email goes out.

We'll monitor our email statistics,
so when you're sending a large batch you can see how many emails there
are total, how many are left, and what the percentage of completion is.

If you're sending a large volume
of emails, you may want to space them out so you don't bog your mail
server down. Some ISPs also will complain if you send a ton of emails at
once. I'll show you how to program a countdown timer with a delay
so you can space your emails out. This will also prevent a flood of
traffic on your web server if everyone responds to your email at
once.

You'll probably have certain emails that
you'll send on a regular basis, such as monthly newsletters, weekly
balance reports, and so on. Instead of having to re-invent the wheel
every time, we'll make a table/form to save our Email Templates.
That way you just pick the template, select the customers, customize it
a bit if you want, and then click SEND.

You'll also probably want to put
customers into different mailing lists, such as your company
newsletter, monthly specials, or in my case a group for my Excel
students, another for my Access students, and so on. We'll set up a
category system where each customer can be in one or more
categories. Now all you have to do is pick the Template, pick the
Category, and all of those customers get their emails,
automatically.

Sending a large batch of email can
take a long time. If you have 20,000 emails to send out, that can busy
your system up for a few hours. It would be nice to queue up the emails
and have another database send them out, right? This can even be a
database sitting on another computer somewhere on your network... one
that everyone on the network can use. In this lesson I'll show
you how to set that up. To be clear, this "database server" will
collect the emails from users and send them up to your Email account.
You will still need an ISP and an email address. This database just
handles all of the email transmission processing so you don't bog down
your workstation PC.

Next we will learn how to read
incoming emails and extract the data from them. We'll see how to
loop through the emails in our Outlook inbox, collect the data from
them, store that data in our Access tables, and put the data in the
right fields. We'll see how to send out a User Survey via email
and process the data when the users return the email with information
filled in.

If you have your own web site with
Microsoft (Windows) hosting, this lesson will allow you to set up a form
on your web site using HTML and ASP (Active Server Pages) to
collect user data. When they submit the data it will come to you via
email and you can use the techniques from the previous lesson to read
and parse the data.

Many people use the Attachment
field type in Access databases to store files. I will show you how to
extract those files to your hard drive using VBA code so that you can
then use them as email attachments.

We will also discuss tracking email
deliverability with delivery status notifications and read receipts.
We'll talk about sending email as text messages using an SMS
gateway, and we'll see how to extract attachments from incoming
emails and save them to a file folder.
As you can see, if you want to be able to
send and receive emails using Access, this is the perfect seminar
for you. You will learn everything mentioned above. You will have
access to the full database that we build in class with all of these
features. If you have any question whether this seminar is right for
you, please feel free to
contact me.

Access Email Seminar Outline
00. Intro (19:51)
01. Sending Email Manually (16:35)
Create Customer Database Customer Table Customer Balance Report Email
Button Send Object As Dialog Brief Discussion of Output Formats Export
as PDF Send and Receive in Outlook Command Button Wizard Build Event
Embedded Macros OpenReport Macro Command Where Condition
CustomerID=Forms!CustomerF!CustomerID VBA Code Editor Docmd.OpenReport
acViewPreview
02. EmailDatabaseObject SendObject (17:49) Mail
Report in Command Button Wizard EmailDatabaseObject Macro Command Specify
Output Format PDF Send to Static Email Address Send to Current Customer
To, Cc, Bcc Fields Subject Line Message Text Edit Message Unsafe
Actions Access Security Warnings Access VBA SendObject Command Adding
FirstName to Custom Message Text Limitations of SendObject Method
03. Email Merge to Word (16:14) Pros and Cons External Data, Word
Merge Send Company Newsletter Microsoft Word Mail Merge Wizard Use
Current Document Use an Existing List Edit Recipient List Edit Document
- Compose Email Add Formatted Text Insert Hyperlinks Insert Pictures
Layout Email with Tables Complete the Merge Merge to E-mail Embedded
Images
04. Automating Outlook Email 1 (22:54) Create Send Email
Form VBA Primer with MsgBox Adding a Reference to Outlook Microsoft
Outlook 15.0 Object Library Outlook.Application Outlook.MailItem
CreateObject, CreateItem To, Subject, Body Properties Displaying the
Message Sending the Message Immediately Outlook Security Warning Send
to Multiple TO Recipients Add a CC and BCC Stop Code Execution Invalid
Use of Null Checking for Required Values If Not IsNull
05.
Automating Outlook Email 2 (27:09) Sending Formatted Emails Plain Text
vs Rich Text HTML Codes in Text Using Word for Exporting HTML Web Page
HTM HTML HTML Filtered Putting Images Files to the Web Upload to Google
Drive Change URL in HTML for Image Switch from Plain to Rich Text Send
to Outlook
06. Automating Outlook Email 3 (15:44) Create
Separate Email Form for HTML Send Email Button on Customer Form OpenForm
Command Set Forms!EmailF!MsgTo to EmailAddress Send Balance Report via
Automation Creating Your Own Subroutine Switching Plain v Rich Text in VBA
Enter Key Behavior for New Lines
07. Attachments in Outlook Email
(26:21) Sending a Static Attachment Create an Attachments Folder
Attaching Multiple Files Creating a ZIP file On Error Resume Next On
Error Goto 0 Check to see if File Exists DIR Function Add Button to
Select a File Common Dialog DLL Create a Global Module SelectFile
Function Attach File Button Two Attach File Buttons
08. Create
PDF Access Attachments (14:48) Code Definition Export Report as a PDF
File Docmd.OutputTo acFormatPDF Format Function with Dates Keep
History of Sent Reports Importance High, Normal, Low
09. Mass Email
Using Outlook (29:06) Create Customer List Form Continuous Forms
Don't Send Report to Everyone SendReport Field y/n Docmd.GotoControl
Loop Through All Customers While Loop, Wend Docmd.GotoRecord acNext
OpenForm to Specific Record Run a Subroutine on Another Form Public
Subroutines Preview Checkbox (Display or send) Disable Outlook Security
Warning WARNING: Watch For Spam Bots Trust Center Programmatic Access
Run As Administrator
10. Send Email Without Outlook 1 (20:05)
What is an SMTP Server Simple Mail Transport Protocol SMTP Server Port
Double Click Event Open Current Customer from List CreateObject
CDO.Message Collaborative Data Objects SendUsing SMTPServer
SMTPServerPort SendUsername SendPassword SMTPAuthenticate Hide my
Username and Password Global Variables & Constants Global Const Global
Modules Renaming Buttons & Moving Code Caption Property
11. Send
Email Without Outlook 2 (21:26) Clean up Code Shorten Schema Line
Set Email Variables to Form Field Values Check for Valid Data Run-time
Error from SMTP Server Sending Rich Text Sending HTML Mail Create
Listbox for Mail Format Options Change Colors with the RGB Function
12. Send Email Without Outlook 3 (20:54) Sending Attachments Using
Gmail to Send Email Email Port 465 SSL Secure Sockets Layer Displaying
Real Names in Email Fields String Concatenation Trim() Function
13. Send Email Without Outlook 4 (8:51) SMTPConnectionTimeout Error
Handling On Error Resume Next On Error Goto 0 Err.Number
Err.Description Err.Clear
14. Email from HTML or Web Page (10:37)
Create a Newsletter in Microsoft Word Access Learning Zone Newsletter Save
as HTML Filtered CreateMHTMLBody Sending a Local HTML File Sending
Email by Importing a Web Page
15. Bulk Customized Email 1 (13:25)
Create BulkEmailF Recordset Loops Loop Through Customers CurrentDB
OpenRecordset dbOpenSnapshot While Loop EOF MoveNext Customize
TO, Subject, Body Fields
16. Bulk Customized Email 2 (12:09)
Variable Substitution Merge Fields Replace Function Nz Function
17. Bulk Customized Email 3 (15:40) Email Batch Statistics Current
Email Counter DCount Total Number of Emails Percent Completed Create a
Status Box Status Subroutine Docmd.Beep DoError Error Box
18.
Bulk Customized Email 4 (19:28) Email Countdown Timer Send Delay
OnTimer Event DoEvents Abort Checkbox Pause Checkbox Flash if Paused
19. Bulk Customized Email 5 (36:23) Select an Email Template
Template Table and Form OnCurrent Event Show Allowed Merge Codes Tab
Order Auto Order Tab Stop Property Docmd.GotoControl SetFocus
Custom From Address for Each Template RecordSource Field vs Property SQL
SELECT Statement Dirty Records Me.Refresh
20. Bulk Customized
Email 6 (22:59) MoveFirst, MoveLast RecordCount Recordset Property
"Sending..." Indicator on Communicate HTML Editor Microsoft Expression Web
FTP with Windows Explorer Import HTML Text into Body Field Read Text File
CreateObject Scripting.FileSystemObject OpenTextFile ReadAll
21. Mailing List Management (39:43) Yes/No Field to Send or Not
Mailing List Table Customer X Mailing List Junction Table Many to Many
Relationships Customer X Mailing List Subform Continuous Forms Combo
Box for Selecting Mailing Lists Mailing List Query with Customer Data
MailingListWithCustomerQ MsgBox with VbYesNoCancel Buttons Get Input from
MsgBox Count Total Emails on Mail Form Open Set Mailing List Name
Automatically Set Recordsource SQL Automatically Set Background Color
Green on Change
22. Your Own External Mail Server 1 (24:15)
Send Using Your Own Dedicated Mail Form Copy Email Send Form Check For
Missing Email Addresses Recordset for Adding Emails to Server
dbOpenDynaset Add Emails to Outbox Table
23. Your Own External Mail
Server 2 (27:43) Removing Code we Don't Need Start/Stop Server Button
Message Status and Status Text Outbox, Sent, Fail Counts RS.Edit to Edit a
Recordset Record Read Status of Email Send Set Value in Table
24. Your Own External Mail Server 3 (24:49) Enhanced Countdown Timer
Separate Email Server Database Delete Email Table from Customer Database
Attach to Mail Server Table
25. Reading Email From Outlook (35:23)
Read Email Form Outlook Object Library Reference Minimum VBA Code Needed
to Read Emails Outlook.Application Outlook.NameSpace Outlook.MAPIFolder
Outlook.MailItem GetNamespace GetDefaultFolder olFolderInbox For Each
Item in Inbox Save Messages to Table in Access Subject, Sender,
SenderEmailAddress Body, HTMLBody, BodyFormat olFormatHTML, olFormatPlain
olFormatRichText Delete a MailItem Inbox Items.Count Mark Read, UnRead
Move Emails from Inbox to Completed Folder
26. Collecting Data Via
Email (42:01) Creating a Survey Email Replying to the Email Pulling
in the Data Responses from Gmail and Outlook Reading and Writing Text
Files Open TextFile For Output Open TextFile For Input FreeFile Line
Input FF InStr, Len Functions Right, Left, Len Functions ByVal vs ByRef
Variable Parameters
27. Collecting Data Via Web Page (21:49)
You will need Windows Hosting for this example Create an Online Form HTML
Form and Input Tags Create a Submit page ASP <% %> Tags Request Command
Response.Write Command Response.Redirect Command DELETE SQL Statement
28. Extracting from Attachment Field (9:02) Saving Files in an
Access Attachment Field Attachment Field Recordset myAttach.Value
SaveToFile
29. Miscellaneous (11:28) Tracking Email Delivery
Read Receipts return-receipt-to Delivery Status Notification (DSN)
disposition-notification-to Sending Text Messages from Access Email Email
to SMS Gateways Extracting Attachments from Incoming Email Outlook Mail
Attachments Attachments.Count SaveAsFile Command
30. Review
(4:30)

|