Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 3    dismiss
 
 

Access Email Seminar

Learn almost everything about sending and receiving Email in Access
 
 

Microsoft Access Email Seminar

This seminar will teach you just about everything there is to know about sending and receiving email using Microsoft Access - with or without Microsoft Outlook. This seminar is for the intermediate to advanced Access developer. Topics include:

1. Send Email Using Outlook
2. Export Access Reports as PDFs
3. Attach Files to Outgoing Email
4. Disable Outlook Security Warnings
5. Mail Merge Using Access and Word
6. Create Rich Text or HTML Email
7. Include Images in Emails
8. Broadcast Bulk Email Batches
9. Send Email Without Outlook
10. Connect Directly to a Mail Server
11. Create an Email from a Web Page
12. Embed Custom Mail Merge Fields
13. Add a Send Delay to Large Batches
14. Build Regular Email Templates
15. Assign Customers to Mailing Lists
16. Construct a Mail Server Database
17. Acquire Data from Incoming Emails
18. Build Web Form to Collect Survey Data
19. Extract Files from Access Attachments
20. Track Email Deliverability
21. Email to SMS (Text Message) Gateways
22. Extract Attachment from Incoming Mail

 
Watch this video to learn more about what's covered:

 

Click here to watch the first lesson
of the Seminar in our Online Theater:

 

 
Access Email Seminar
Description: Learn just about everything there is to know about sending and receiving Email in Access.
Versions: I will use the 32-bit version of Access 2013, however most of the topics will work fine with any  version of Access. The common dialog box (used to select a file attachment) will not work with 64-bit Access. The lesson on disabling the Outlook security warnings may not work in older versions of Office. Almost everything else works just fine. Watch the intro video (above) for a complete list of version information.
Pre-Requisites: This course stands alone, however it is very strongly recommended that you at least know everything in my Access Beginner series (1-9) and the first three lessons of my Expert series. You will also need an email account and a working installation of Microsoft Outlook to use the lessons involving Outlook. If you wish to use the lessons on collecting data via a web page, you will need Microsoft web hosting. Again watch the intro video (above) for a complete listing of pre-requisites for this course.
Running Time: 10 Hours, 49 Minutes
Cost: $199 - Order multiple courses to receive a discount up to 50% off

 

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.

send email with access and outlook 

You will learn about the SendObject and EmailDatabaseObject macro and VBA commands. We'll talk about the Outlook Security Warning and what it means.

sendobject emaildatabaseobject 

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.

access word mail merge

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.

automating outlook

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.

rich text html emails

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.

html formatted emails

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.

sending attachments via outlook and access

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.

export report as pdf attachment

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.

sending bulk email with access and outlook

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.

send email without outlook

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.

sending email without outlook

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.

send email from access to gmail

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.

send web page as email createhtmlbody

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.

custom merge fields in emails

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.

email statistics

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.

countdown timer with delay

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.

email templates

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.

mailing list category management

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.

 database email server

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.

read incoming email from outlook

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.

get user data from web site

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.

extract attachment fields

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)

 


 

 
 

Student Interaction: Microsoft Access Email Seminar

Richard on 4/5/2014:  The Microsoft Access Email Seminar will teach you just about everything there is to know about sending and receiving email using Microsoft Access - with or without Microsoft Outlook. This seminar is for the intermediate to advanced Access developer. Topics include: - Send Email Using Outlook - Export Access Reports as PDFs - Attach Files to Outgoing Email - Disable Outlook Security Warnings - Mail Merge Using Access and Word - Create Rich Text or HTML Email - Include Images in Emails - Broadcast Bulk Email Batches - Send Email Without Outlook - Connect Directly to a Mail Server - Create an Email from a Web Page - Embed Custom Mail Merge Fields - Add a Send Delay to Large Batches - Build Regular Email Templates - Assign Customers to Mailing Lists - Construct a Mail Server Database - Acquire Data from Incoming Emails - Build a Web Form to Collect Survey Data - Extract Files from Access Attachments - Track Email Deliverability - Email to SMS (Text Message) Gateways - Extract Attachments from Incoming Mail Click here for more information on the Microsoft Access Email Seminar, including a complete course outline, sample video, sample database file, and lots more.
Jim Upton on 4/10/2014: I am unable to access your code. Would it be different in uk

Reply from Richard Rost:

Jim, of which code are you speaking? Nothing on my site should be different in the UK.

Jim U on 4/10/2014: I don.t seen able to access any of the code for copying and. Pasting. I am just getting error message page cannot be displayed. I have tried it at to locations.

Reply from Richard Rost:

I just tested it, and it's working fine for me. What is the EXACT error message you're getting? Is it a web 404 error, or is it an error my site is giving you?

Robert Miller on 4/15/2014: Rick, LOVE LOVE LOVE this seminar!! I already have tons of ideas that I want to incorporate into my databases.

Question: Will you be creating a handbook for this seminar? Or do you just have handbooks for regular lessons? I would like to get my hands on a paper copy of this seminar so that I can use it for references without having to log in all the time to review a video. It would be greatly appreciated!

Thanks again for an awesome seminar!
Rob


Reply from Richard Rost:

Yes, the handbook is actually finished. My author sent it to me yesterday. It's in my inbox waiting for me to review it and post it, but I'm on the road (AGAIN!) for the next few days. I'll get it online as soon as I can. Glad you love the seminar. It took me FOREVER to finish. :)

Larisa K on 4/23/2014: Hi Rick- Thank you so much for such a good seminar.
But I need your help, I can't access to my gmail account. it show me only one general error "overflow" which is hard to figure out where is the problem. Is any way I can specify error description to see what's wrong?
I did try "smtpserverport" = 25 and 465

Reply from Richard Rost:

Do you have two-factor authentication turned on? If you have an Android phone you might have the Authenticator app which gives you a code to get into your Gmail if you lose your password. If this is the case, you need to set up a special-access password to connect to Gmail from other programs, like your Access database. See this article on Gmail two-factor authentication.

Larisa on 4/23/2014: thanks for your response. I don't have two-factor authentication. it says disable in my security setting. I did search on-line what could cost this problem but nothing help me. Any more ideas?

Reply from Richard Rost:

You've got me. I've got a couple of different Gmail accounts, and this technique works fine with all of them. Anyone else?

Larisa K on 4/25/2014: I got, it works, by changing authenticate from 1 to true
smtpauthenticate = true

Richard Orr on 5/8/2014: Good Day Richard
When testing my VBA code at the end of lesson 7 I found that if the MsgAttach1 and MsgAttach2 were blank I would get an error message. After some thought I decided that as we were now using a button to select the file to attach the dir code lines were no longer relevant.
So have deleted:

If Dir(MsgAttach1) = "" Then
MsgBox "Attachment 1 does not Exist"
Else
OMsg.Attachments.Add (MsgAttach1)
End If

and replaced it with

If Not IsNull(MsgAttach1) Then
OMsg.Attachments.Add (MsgAttach1)
End If

I'd appreciate your thoughts and any suggestions for improved code.

I'm appreciating the seminar and slowly learning VBA code. Great stuff.

Best regards Richard (Melbourne, Aust)

Alexander Hedley on 5/15/2014: Time Stamp: 8:26

If Not IsNull(MsgCC) Then Msg.CC = MsgCC

If Not IsNull(MsgCC) Then Msg.BCC = MsgBCC

Should the second line not be

If Not IsNull(MsgBCC) Then Msg.BCC = MsgBCC

IsNull(MsgCC) => IsNull(MsgBCC)

Abdul Quadir Mohammed on 5/19/2014: Dear Richard,

Attaching a file works fine.

I used the same to reference a link to necessary files

Please advise on how to convert the entire file location string into a hyperlink using vba so that on a double click the user may open and view the file.

I await.

Larisa K on 5/19/2014: I just finish updating my program with this seminar. I can t say enough thank you!!. You are the best teacher I ever had in my live. So easy to follow you and understand. You topics always going wider than I expected. Every your new lesson is 100% important. Thanks so much!!
Waiting for your next seminar, I am looking for seminar which will show how to upload database on web server and work from different pc-locations to get the data.

Andy Treusch on 6/3/2014: when i attempt to run this msgbox the subject in the inbox i bomb out in the loop with "New or Modified User Account Information". It does not show the subjects.

I do have three accounts in outlook.

time index is 7:34

Andy Treusch on 6/3/2014: I noticed no intellisense not work on this line:

Dim oCompleted As Outlook.MAPIFolder

TIME INDEX 7:34

Mary Reinhardt on 6/5/2014: Hi Richard,

I am having difficulty with an two fields on my importing of text from an email. I have two fields, one is called Name: and the other is Company Name: my error is that Company Name will populate my Name: field in the database (actually the title of the field is TeacherName:in the db). Do you have a suggestion for code that will improve this? Or a direction of which course will help me to figure this out? Thanks so much!

Reply from Richard Rost:

If this is an import you have to perform on a regular basis, just specify the field names in your Import Spec. If it's a one-time thing, just correct them after you import them into a temp table, then append them into your main table.

Gie on 7/29/2014: Will this seminar include how to set-up the sender email? I am using 2 outlook email accounts, one my own email and the other a group email or shared inbox. I want to be able to send using the group email.

Reply from Richard Rost:

The seminar doesn't not specifically cover setting up your email accounts in Outlook. It's assumed you have a working copy of Outlook and you have at least one account set up. Once that's done, Access will use your DEFAULT Outlook email account to send all of its mail.

Kaylen D on 9/19/2014: Lesson 1, I tried both the macro button and the vba method to open a specific report for a record in a form but getting the same error Runtime 3211: the database engine could not lock table 'tblContacttypes' because it is already in use by another person or process". Fyi, the form has two subforms attached to it.
Scott Adkins on 10/29/2014: Richard I am running Windows 8 and Access/Outlook 13. I changed the suspicious activity warning exactly as in the seminar but I still get that stupid allow/deny message. Is there something else that needs to be changed?

Reply from Richard Rost:

Did you make sure you made the change while logged on (or at least running the program) AS ADMINISTRATOR? Aside from that, I pretty much covered everything in the seminar.

Personally, I don't like using Outlook to send Email. The lessons on using Gmail (or another SMTP server) are my preferred method.


Scott on 11/3/2014: I played with it for a while and learned this. If you try to send with a macro, you get that security warning. If you do it with vba, and follow the steps in the seminar, then it works fine.
Arabi Mahmood Moodi on 11/28/2014: where can i find source code page ?
secondly
i have introduced outlook on my computer to day for for sake of outlook mails ,and it has been the my first time to try it on mails but why is it that all emails i send do not go to my old yahoo mails? but i just receive them on outlook mail account i have just created ?
now how can i send to people on Google and yahoo through outlook?
Arabi

Reply from Alex Hedley:

Source code for what?

Which account did you use with outlook?

It should be able to send to any email address.

Arabi Mahmood Moodi on 11/29/2014: Dear
i meant source page code for SMPT Savor, and others.
Even after learning lesson 10 with almost 8:49 time index i can see Mr roset peeking it somewhere from unknown link
i only need to have it from where i can peek it with copy and past and i think this is what Mr Roset needed us to do
therefor can you please help and put all these indexes near ?
secondly the account i opened with outlook was my real account [admin removed]
i can receive incoming email with this account on outlook from my database but nothing goes in yahoo account of the same account why ?
Sorry for such disturbance


Reply from Alex Hedley:

Ah so you would like the VBA code Rich uses, have you downloaded the Sample Database?

You can also buy the Handbook, I add all the code to these so you can copy/paste them into your own database.

So if you log into your yahoo account in a web browser are you not seeing emails?
If they are coming into the the db it means they are in your account.

It's no disturbance at all, the forums are here to help :)

Arabi Mahmood Moodi on 11/30/2014: Sir
Can you help me with SMTP SERVER and server port of yahoo

because the one i have searched for from yahoo search is (plus.smtp.mail.yahoo.com and the port as 465 )
but is not working
this is because my account is registered with yahoo.


Reply from Alex Hedley:

I found the following settings at Yahoo

SMTP: smtp.mail.yahoo.com
PORT: 465 or 587
SSL: Yes
Requires Authentication: Yes

jeff horvate on 12/5/2014: when I go to build event, it go to vba mode, I want macro mode? I got office 2007.

Reply from Alex Hedley:

Does your Event already have [Event Procedure] beside it?
Try deleting this then clicking the Ellipse(...).
This should then pop up a Builder options.

If this doesn't work you may have changed your settings to not always use code.
File | Options
Select Object Designers from the list.
Scroll down to the Forms/Reports section.
Click on the Always use event procedures box. (Un tick this)
Click on OK to close the window.

Tony Roberts on 12/29/2014: HI, thanks for the course, the best I have seen.

I have been trying to insert a new line in the MsgBody after pulling the person's name so the the message is not all on the same line. I have tried the usual commands but it will not produce a new line. Can you advise?
Automating Outlook email 3 time 10:15

Tony

Reply from Alex Hedley:

Hi Tony what commands have you tried?

You could try vbNewLine.

There are constants in VBA like:

Constant Definition
-------------------------------------------------------------------
vbBack A backspace character [Chr(8)]
vbCr A carriage return [Chr(13)]
vbCrLf A carriage return and line feed [Chr(13) + Chr(10)]
vbLf A linefeed [Chr(10)]
vbNewLine A platform-specific new line character, either
[Chr(13) + Chr(10)] or [Chr(13)]
vbNullChar A null character of value 0 [Chr(0)]
vbNullString A string of value 0 [no Chr code]; note that this is
not the same as ""
vbTab A tab character [Chr(9)]

Tony Roberts on 12/29/2014: tried all of them and discovered I needed to use html
as I was using a richtext text box.

Thanks for your response

Reply from Alex Hedley:

Ah a <br /> will do the trick

Kath McLeod on 1/13/2015: Hi Richard, in completing Lesson 7 if find that when i click on the Attachment 1 or 2 buttons the Attachments folder opens up, I choose a file and am returned to the form but no attachment is loaded. I have checked the VBA and it all appears fine. Could you please advise what I am doing wrong. If i cancel without selecting a file, i get the "A file was not selected" error message. So that is working fine. I am reluctant to move on with the lessons until i can work this one out.

Cheers
Kath

Reply from Alex Hedley:

Is the Field on the Form the name that the functions passes back to.
It'll be something like msgattach = launchcd...
That will need to match the Textbox on the Form.

Kath McLeod on 1/13/2015: this is additional to my previous email. I have redone the forms and code and still get error message which hangs up on:
OMsg.Attachments.Add (AttachFolder & MsgAttach1)

I am up to
If Dir(AttachFolder & MsgAttach1) = "" Then
MsgBox "The file you selected for an attachment does not exit!"
Else
OMsg.Attachments.Add (AttachFolder & MsgAttach1)
End If at Time index 26:21

kath

jeff horvate on 2/3/2015: How come when I type part of email. access does NOT catch the error. I am working office 2007


Reply from Alex Hedley:

What part are you typing and what error are you expecting to be caught?

Kath McLeod on 2/12/2015: Alex this is the code MsgAttach1 = Selectfile(Me, AttachFolder) which is the same as Richards. Also I downloaded Richards and it works perfectly. (i thought is may be because i have windows 64bit but if Richards works it is not that. I need to get this working so i can move on. Any further suggestions?

Kath

Reply from Alex Hedley:

Do you have Office 32-Bit installed?
What happens when you press it?
Check the spelling of your controls

Brauna Rosen on 4/23/2015: Hi Richard,
If I want to use the Email Merge to Word and I have a few email accounts in Outlook, how do I choose the correct "From" email address?
Great Seminar!
Thanks,
Brauna

Reply from Alex Hedley:

You can't unfortunately
You'll have to create a profile with just one account, not multiple and choose that one.

Martijn Eisinga on 5/25/2015: Hi

I am trying to make various buttons in my forms that send out a template email - text is always the same and just takes (firstname)or other details from a candidates record.

See below:
DoCmd.OpenForm "EmailF"
Forms!EmailF!MsgTo = EmailAddress
Forms!EmailF!MsgSubject = "Your application " & FirstName
Forms!EmailF!MsgBody = "Dear " & FirstName & " , We received your application " & _
"we would like to invite you " "."

Tried all andcannot figure it out.

The body of each message is like 6/7 lines of text + signature.

Is this the way or is there an easier solution?

Kind regards
Martijn Eisinga

Reply from Alex Hedley:

Have you seen Lesson 19?

19. Bulk Customized Email 5 (36:23)
Select an Email Template
Template Table and Form


Steffen Morning on 7/3/2015: Very interesting class and it shows almost what I need. My problem is that I wish to import and move ONLY the currently selected e-mail in Outlook and not all of them. I figure a simple change to the line "For Each oMailItem In oInbox.Items" would do the trick but I don't know how. Can I simply change the word "Each" to "Selected"?

Reply from Alex Hedley:

I'll need to look at the code.

For Each is a keyword used in the loop, it's the oInbox.Items that would need to be checked for first.


Do While True
count = count + 1

If count = 10 Then
Exit Do
End If
Loop

Ray White on 8/31/2015: If I am using Outlook 2010 reference in my VBA when developing my program and then send the program out to my users will Outlook 2010 version be the only version that will work with the program or will any version that the user has will work?

Reply from Alex Hedley:

You could check the References Count
intCount = Access.References.Count
Remove and ReAdd

You could use the AddFromFile

jeff horvate on 11/23/2015: I have 6 emails account in my outlook. I wonder if I can read 6 email inbox Set oInbox = oNS.GetDefaultFolder(olFolderInbox)like read a inbox then read c inbox. read is different email address

Reply from Alex Hedley:

This is untested but should work for research

Dim objNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set objNS = GetNamespace("MAPI")
Set objFolder = objNS.Folders("OTHER MAILBOX NAME")
Set objFolder = objFolder.Folders("Inbox")

Kim Beaul on 12/10/2015: I'm having problems with Mail Merge. When I click on Mail Merge when I have a query open with the data I want to include in the email Word opens with pop up dialog box "confirm data source" with OLE DB Dabatabase Files as the only option. When I click OK the icon for Access starts flashing in the task bar with pop up message "Word was unable to open the data source" Do you have any idea why this is happening. Right now my work around is to export the query to excel and then use it as the data source for my mail merge. I'm only on the 3rd video so I'm sure one of your other email methods will meet my needs better but I would like to know why I am getting this error.

Thanks - your videos are awesome
Kim

Reply from Alex Hedley:

Does your Query contain a Parameter?

MS Article should help.

Kim B on 12/10/2015: This is the query I am using
SELECT Student_Course.MailMerge, Student_Course.FieldPlacementID, Student.Fname AS StudentFirstName, Student.Lname AS StudentLastName, FAST_StudentAddresses.Term, FAST_StudentAddresses.[Email Address] AS StudentEmail, AgencyInformation.Agency, AgencyInformation.Address, AgencyInformation.CityID AS City, Contact.FirstName AS ContactFirstName, Contact.LastName AS ContactLastName, Contact.Phone AS ContactPhone, Contact.Ext AS ContactExt, HyperlinkPart([Contact].[Email],1) AS ContactEmail, Student_Course.SentStudentConfirmation, Student_Course.SentAgencyConfirmation
FROM (Contact RIGHT JOIN (AgencyInformation RIGHT JOIN (Student RIGHT JOIN Student_Course ON Student.StudentID = Student_Course.StudentID) ON AgencyInformation.AgencyID = Student_Course.AgencyID) ON Contact.ContactID = Student_Course.ContactID) LEFT JOIN FAST_StudentAddresses ON Student.StudentID = FAST_StudentAddresses.[Spriden ID]
WHERE (((Student_Course.MailMerge)=True) And ((Student_Course.FieldPlacementID)=Forms!Menu_StudentFieldPlacements!cboFieldPlacement) And ((FAST_StudentAddresses.Term)=Forms!Menu_StudentFieldPlacements!cboTerm));


Reply from Alex Hedley:

That contains a few parameters
Forms!Menu_StudentFieldPlacements

Did you try the suggested article?

Ray W on 12/22/2015: I don't understand how this works?

Reply from Alex Hedley:

You could check what References are added then add the necessary dll

Example
References.AddFromFile "C:\WINNT\system32\x.dll"

Wayne Markel on 12/24/2015: Lesson 9 - Time 23:33 I set my Outlook Program Access Security to "Warn me about suspicious activity when my antivirus software is out of date" Works to suppress warning message in Access. Isn't this a better choice than "Never warm me...."?

Reply from Alex Hedley:

It's your choice, whichever you feel most comfortable with.

Wayne Markel on 12/24/2015: Lesson 10 Time 20:05.
Gmail settings use port 465 and add
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

Ray White on 12/29/2015: Sending email with SMTP:
I have an email with Godaddy and it work fine, I have email with Yahoo and it works fine, But Gmail and Hotmail will not work, I have tried every option they are with not luck. Are there any setting in the Gmail or Hotmail account that I need to set?

Reply from Alex Hedley:

GMAIL now needs you to lower your security settings.
I think it's this setting - Google page.

There's probably a similar setting for Hotmail too.

Ray W on 12/31/2015: Thanks Alex
That did work for Gmail.

Jerry Freeman on 1/9/2016: Richard: I am trying to send an email to multiple recipients without having to create a new form; it is just based off a button click. ie-email ADMIN. Then sending an email to all in the ADMINEmail Recordset. But only the last record in the recordset is showing up in the To: field when Outlook opens. Any help appreciated. Code as follows:

Dim OObj As Outlook.Application
Dim OMsg As Outlook.MailItem
Dim db As Database
Dim rs As Recordset
Dim EmailAddress As String
Set OObj = CreateObject "Outlook.Application")
Set OMsg = OObj.CreateItem(olMailItem)
Set db = CurrentDb
Set rs = db.OpenRecordset("65_EmailGroupADMIN_T")
With rs

If .EOF And .BOF Then
MsgBox "No emails will be sent becuase there are no records assigned from the list", vbInformation
Else
Do Until .EOF
EmailAddress = ![Email]
.Edit
.Update
OMsg.To = EmailAddress
.MoveNext
Loop
OMsg.Display
End If
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Set OMsg = Nothing
Set OObj = Nothing
End Sub

Reply from Alex Hedley:

You have the lineOMsg.To = EmailAddress
So To will always contain the last email address in your loop
You will need to build up a string of emails in your loop instead

Dim strEmails
strEmails = strEmails & EmailAddress & ";"

Then set the .To to your list
OMsg.To = strEmails

Jerry F on 1/13/2016: Thank you! Your awesome! This is what the code ended up looking like, and I have ran it on multiple tables now to get emails, but how does strEmails "know" what it's value is. It is dim'd @ the start of the code, but never "set" to = anything....I appreciate your help, just trying to understand the "why" behind what I did.

With rs
.MoveFirst
Do Until .EOF
EmailAddress = ![Email]
.MoveNext
strEmails = strEmails & EmailAddress & ";"
Loop
OMsg.To = strEmails
OMsg.Display

Reply from Alex Hedley:

You set it here with the assignment operator "="
strEmails = strEmails & EmailAddress & ";"

So you have a list
Fred, Joe, Alice, Bill

strEmails = ""
First loop
strEmails = "Fred"
Second
strEmails = "Fred; Joe"
etc

So its saying get what strEmails currently is and add on another string then set this back to itself so it overwrites the first person with the first and second person (lather, rinse, repeat to quote friends)

Jerry F on 1/13/2016: Great! Again, thank you so much for your time and information! Greatly appreciated!

Reply from Alex Hedley:

Happy to help :)

Ray White on 1/28/2016: I am trying to run a Bulk email from my Agents data form with a Criteria By "Department" say just email everyone in the Billing Department. I have this in my Bulk Email form on open.
But when I open the Bulk Email for I get
"Run-time Error 3061"
(Too Few Parameters. Expected 1)
Here is my on Open Code:

Private Sub Form_Open(Cancel As Integer)

Dim DB As Database
Dim RS As Recordset
Dim DepartmentName As String
Dim MsgRecordSource As String

DepartmentName = Form_frmAgent.DepText
MsgRecordSource = "SELECT * FROM qryAgentContact WHERE Department=" & DepartmentName

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(MsgRecordSource, dbOpenSnapshot)

RS.MoveLast
TotalEmails = RS.RecordCount

RS.Close
Set RS = Nothing
Set DB = Nothing

Me.MsgSubject.SetFocus

End Sub


Reply from Alex Hedley:

Is Department a String Field?

MsgRecordSource = "SELECT * FROM qryAgentContact WHERE Department=" & DepartmentName
=>
MsgRecordSource = "SELECT * FROM qryAgentContact WHERE Department='" & DepartmentName & "'"

You'll need to wrap it in quotes so
" SELECT * FROM qryAgentContact WHERE Department='Finance' "

Ray W on 1/30/2016: THNK YOU Alex :)
Works perfect.
Such a little things can be so hard to figure out sometimes.
Thank you for your support.
All of these videos are the best training I have ever purchased in 15 years.

Reply from Alex Hedley:

Happy to help :D

Kim Beaul on 2/12/2016: Is there a way to insert a table into the body of the email message? Right now my message looks like:
Dear Laura

To date you have completed 46 hours for your field placement at SAM Program (East Site). By the end of the week of Feb 1 - 7 64 hours should have been completed. If you are short of hours or have any questions regarding your hours please contact your Field Placement Specialist Julie Pollock julie.pollock@mohawkcollege.ca (905) 575-1212 Ext. 2033.

These are the hours that you submitted:

Jan 11 - 17: 12 hours
Jan 18 -24: 8 hours
Jan 25 - 31: 12 hours
Feb 1 - 7: 14 hours

My boss would like the lower portion to be in a table.

My code is:

Public Sub PrepareMessage()

SendEmail

Forms!Email_Form_StudentFieldPlacementHours_HTML!MsgSubject = "Accumulated Field Placement Hours for " & Fname
Forms!Email_Form_StudentFieldPlacementHours_HTML!MsgBody = "Dear " & Fname & "

To date you have completed " & TotalHours & " hours for your field placement at " & Agency & ". By the " & _
"end of the week of " & Week4Date & " " & ExpectedHours & " hours should have been completed. _
If you are short of hours or have any questions regarding your hours please contact your Field Placement Specialist " & FPSName & _
" " & FPSE & " " & FPSPhone & ".

" & _
"These are the hours that you submitted:

" & Week1Date & ": " & Chr(9) & Chr(9) & HrsWeek1 & " hours " & Chr(9) & Chr(9) & Week1Comment & _
"
" & Week2Date & ": " & Chr(9) & Chr(9) & HrsWeek2 & " hours " & Chr(9) & Chr(9) & Week2Comment & _
"
" & Week3Date & ": " & Chr(9) & Chr(9) & HrsWeek3 & " hours " & Chr(9) & Chr(9) & Week3Comment & _
"
" & Week4Date & ": " & Chr(9) & Chr(9) & HrsWeek4 & " hours " & Chr(9) & Chr(9) & Week4Comment


End Sub

Thanks
Kim

Reply from Alex Hedley:

Are you comfortable writing HTML?
If you set the message to be HTML and not plain text you can build up a HTML Table
<table> with your tr and td etc

Kim Beaul on 2/13/2016: RE: Table inside email message. I figured it out!!


Public Sub PrepareMessage()
SendEmail
Forms!Email_Form_StudentFieldPlacementHours!MsgSubject = "Accumulated Field Placement Hours for " & Fname
Forms!Email_Form_StudentFieldPlacementHours!MsgBody = "Dear " & Fname & "

To date you have completed " & TotalHours & " hours for your field placement at " & Agency & ". By the " & _
"end of the week of " & Week4Date & " " & ExpectedHours & " hours should have been completed. _
If you are short of hours or have any questions regarding your hours please contact your Field Placement Specialist " & FPSName & _
" " & FPSE & " " & FPSPhone & ".

" & _
"These are the hours that you submitted:

" & _
"" & _
"" & _
"" & _
"
DateHoursComments
" & Week1Date & "" & HrsWeek1 & _
"
" & Week1Comment & "
" & Week2Date & "" & HrsWeek2 & " " & Week2Comment & "
" & Week3Date & "" & HrsWeek3 & " " & Week3Comment & "
" & Week4Date & "" & HrsWeek4 & " " & Week4Comment & "


Kim Beaul
Database Administrator
End Sub

All I need now is to indent the table about 1-2 inches.

Kim

Reply from Alex Hedley:

  as many times as you need for a single space " " for html or & vbTab for plain text
See this Glossary item for others.

Arnel Arnaldo on 2/19/2016: Video not found or access denied: url

Reply from Alex Hedley:

Can you try logging out and logging back in, clearing your cache etc

James Gray on 2/26/2016: Outlook 2016 seems to add "mailto:" to all hyperlinks. In my code, when it looks up the specific email address to send objects to, the email address turns into the original email from the database#mailto:email address from the database#. Outlook then errs out, not understanding that to: email address. I am unable to edit this mailto: out of the address in the editor. Interestingly, any email address I entered using older versions of access seem to work...Any clues as to how to fix this?

Reply from Alex Hedley:

Which part of the db is doing this?

Arnel Arnaldo on 2/26/2016: Hi Richard,
Using the macro to set the values for EMailDatabaseObject parameter To to [Forms]![]![], the To on the message field appears as #mailto:#.
Can you explain why?

Reply from Alex Hedley:

Is there a value in the form control?

James Gray on 4/20/2016: Just a quick note: I was having trouble getting the "minimum code" to function properly. I was getting a "transport not connecting to server....error. I googled the error and finally came up with a line of code to add to the minimum and it seems to eliminate the error. That line of code is:msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True. Everything now seems to work just fine. I am using Office 365 and Windows 10. Thanks.
James Gray on 4/21/2016: It looks like the statement for adding attachments in now Msg.AddAttachment filename instead of Msg.Attachment.Add filename. Maybe a change in CDO language in new Office 365?
Chris Thompson on 12/29/2016: FYI, 2010 Outlook has the same Programmatic Access Security as 2013 Outlook used in your video. The available options are identical.
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP