Introduction

Welcome! Import Files & Bulk Email Users

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 20. In this course we will begin by learning how to send bulk email from your Access database by exporting data to Word and using Outlook. We will then cover importing and linking data from various sources including Excel spreadsheets, text files, and HTML documents, as well as how to clean and manage this data. We will spend additional time discussing XML for importing and exporting both data and schema, and will talk about linking to Microsoft Outlook folders so you can read and update emails and contacts directly from your Access database.

Navigation

Prev: X20
Next: Lesson 01

Keywords

Access Expert, bulk email, mail merge, export to Word, import Excel, link to Excel, import text files, import HTML, web page data, import XML, export XML, schema data, link to Outlook, read Outlook inbox, edit Outlook contacts, clean data

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 20 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class primarily focuses on importing data from other data sources into our Access database. We will begin, however, by learning how to send bulk email from our Access databases. This is a lesson that I had initially planned on covering in the last class, Expert 19, but I did not quite get to it because the class was running long. This still falls in with exporting data from Access 19, but I definitely wanted to cover it.

I will show you how to send a bulk email to all the customers in your data who you mark active. I will send the data to Microsoft Word, where we can format a nice, pretty HTML email and send all of it out through Outlook. I did cover this topic to some extent in my email seminar that I just released. However, there are some new techniques in this lesson as well that I did not cover in the seminar.

Next, we will start covering importing data and linking to external data sources. We will then have to import data from an Excel spreadsheet. We will talk about cleaning up that data a little bit before you import it. We will see how to link to an Excel spreadsheet so that if it is on a network, for example, and someone makes changes to the original spreadsheet, those changes will automatically be reflected in your Access database. That is the benefit of linking to a file.

We will learn about importing text and HTML files. HTML files are, of course, web page files. I will show you how to save a web page and then extract data from a table on that web page. If there is a website that you go to on a regular basis and they have some data on there, whether it is stock quotes or weather information or whatever, I will show you how to save that page, import the HTML, and then pick the data out of one of the tables on that web page.

Now, in Access 19, when we covered exporting, I only talked about XML very briefly, and that is because I wanted to wait until this class when I could cover exporting and importing XML. XML is a very powerful way to share information, data in your database, as well as the structure of the database, called schema data. It is universal. It is used on the web and you can share data between almost any two types of databases. In this lesson, we will spend a lot more time talking about XML, what it is, and how to properly use it.

Finally, I will show you how to link to your Outlook folders. We will learn how to link to your inbox so we can read email in, delete email, change email, perhaps, and we will see how to access our contacts from our Outlook contacts folder. You can read and write contacts, change their information, and lots more.

This class follows Microsoft Access Expert Level 19, where we covered conditional formatting, and we learned how to export all the different types of data that we are going to be importing today. I strongly recommend you watch Level 19 first before taking today's class, because I talk a lot more about what the different types of data are that we will be working with today.

Today's class is recorded with Microsoft Access 2013. Most of the lessons should work fine with Access 2007 or 2010. I am pretty sure this has not changed much since 2007, but of course, if you come across something that is significantly different, please let me know. Honestly, I do not even have a copy of 2007 or 2010 installed anymore, so I have not tested this stuff with 2007 or 2010, but please do let me know if anything is significantly different, so I can put a note in the course notes.

If you have Access 2003 or earlier, I cover importing and exporting in my older Access 308 lesson. If you have the older versions of Access, that is the class you want. It is much, much different in 2003 than it is in 2013.

In the last class, Access Expert 19, we covered primarily exporting data from your database. In lesson 1 today, we are going to continue the exporting theme, and I am going to show you how to send bulk email by exporting data to Word using a mail merge with Outlook.

In lesson 2, we are going to learn about importing and linking to Excel data. In lesson 3, we are going to see how to import text files and HTML documents. You will see how to pull data from pretty much any web page.

In lesson 4, we are going to learn a lot more about XML. We will do more with exporting XML in a couple of different formats and learn how to import XML into our database.

In lesson 5, we are going to talk about linking your Access database to Microsoft Outlook folders, so you can read in emails from your inbox or other folders, and you can actually read and write to your contacts.

In lesson 6, we are going to talk about how to import XML into our database.

Quiz

Q1. What is the first topic covered in this class?
A. Sending bulk email from Access
B. Importing Excel data
C. Data validation
D. Creating reports

Q2. How will the bulk email lesson send data to customers?
A. By exporting customer data to Microsoft Word and emailing via Outlook
B. Using Access internal email function
C. Sending text messages directly
D. Printing mail labels

Q3. What is the main advantage of linking to an Excel spreadsheet instead of importing it?
A. Linked data updates automatically in Access if the original Excel file changes
B. It is faster to import
C. It allows editing Excel files directly in Access
D. No file is required on the network

Q4. Which type of external file will you NOT be learning to import in this lesson?
A. PDF files
B. Excel spreadsheets
C. Text files
D. HTML files

Q5. Why is XML considered a powerful way to share information?
A. It can store both data and structure (schema) information and is universal
B. It is a Microsoft-exclusive format
C. It cannot be read by other databases
D. It is simpler than CSV

Q6. Which tool will be used to format HTML emails for bulk mailing?
A. Microsoft Word
B. Microsoft Excel
C. Notepad
D. Access Form designer

Q7. How can you use Access to import data from a web page?
A. By saving the web page as HTML, then importing and extracting data from its tables
B. Directly connecting Access to the internet
C. Copying and pasting into Excel first
D. Taking a screenshot of the web page

Q8. Which version of Access is this class recorded with?
A. Access 2013
B. Access 2003
C. Access 2007
D. Access 360

Q9. Where should students using Access 2003 or earlier look for lessons on importing and exporting?
A. Access 308 class
B. Access Expert Level 20
C. This class is sufficient
D. Access Beginner Level 1

Q10. What is one of the things you will learn to do with your Outlook folders in this class?
A. Read and write contacts data
B. Compose new Outlook themes
C. Add attachments to emails automatically
D. Export Access tables directly into Outlook calendar

Q11. Before importing Excel data, what should you do with the data?
A. Clean it up to ensure it is ready for import
B. Email it to all users
C. Encrypt the worksheet
D. Convert it to PDF first

Q12. In lesson 4, what file type will be explored in more detail for exporting and importing?
A. XML files
B. CSV files
C. PDF files
D. JPG files

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Expert Level 20, where I will be covering how to import data from a variety of external sources into your Access database. Before we get started with importing, though, I wanted to finish up a topic that I ran out of time for in the last class, Expert 19, and that is sending bulk email from Access.

In today's first lesson, I will demonstrate how to send mass emails to all your active customers by exporting your data from Access over to Microsoft Word, formatting a professional-looking HTML message, and then sending it using Outlook. Even though I have talked about sending bulk email before, especially in my recent email seminar, I am including some new techniques here that were not covered in that seminar.

Once we cover email, we will move into the heart of today's class, which is working with data from outside sources. I will walk you through importing data from Excel spreadsheets into Access, including some tips on cleaning up your data before you bring it in. I will also show you how to link to an Excel sheet instead of importing it, which is very useful if the spreadsheet is shared on a network. When you link to it, any updates made to the Excel file are instantly available in Access, so you are always working with the latest information.

We will also look at importing both text files and HTML files. HTML files are saved web pages, and many sites display useful data in tables. I will show you how to take a saved web page and pull out table data, whether it is stock information, weather, or anything else you need to track.

Regarding XML, when we talked about exporting data in Expert 19, I only briefly touched on XML. In this class, I will spend more time on both importing and exporting XML, including how XML can store not just data but database structures as schema data. XML is a universal format that allows you to share information between many different database systems, not just Access.

Toward the end of this class, I will introduce how to link your Access database directly to Outlook folders. This will let you not only read your incoming emails and other folder contents but also modify or delete messages if needed. Additionally, I will cover working with your Outlook contacts, which means you will be able to read and update your contact list directly from Access.

As a reminder, this class builds on what we learned in Expert Level 19, where we focused on exporting data and conditional formatting. If you have not seen that class yet, I suggest watching it first since I reviewed all the export types we are handling today and explained the basics.

All demonstrations in this class use Microsoft Access 2013. Most of the lessons will also work with Access 2007 and 2010, as not much has changed in these areas since 2007. However, because I do not have those older versions installed, I have not tested everything personally, so please inform me if you find any significant differences, and I will update the class notes accordingly.

If you are using Access 2003 or earlier, be aware that things are much different. For those versions, please refer to my older Access 308 lesson, which covers importing and exporting for those editions.

To sum it up, after a quick lesson on using Access, Word, and Outlook together to send bulk email, we will move on to importing and linking Excel data, handling text and HTML files, working with XML for both importing and exporting, and connecting Access to Outlook folders for email and contact management. By the end of this class, you will be able to pull data from almost any source and make the most out of your Access database.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Sending bulk email to active customers
Exporting Access data to Microsoft Word
Formatting HTML email in Word
Sending email through Microsoft Outlook
Importing data from Excel spreadsheets
Cleaning up data before import
Linking to Excel spreadsheets for live data
Importing text files into Access
Importing HTML files and web page tables
Extracting data from tables in HTML files
Exporting data as XML from Access
Importing XML data into Access
Using XML schema data
Linking Access to Outlook folders
Reading and managing email from Outlook
Accessing and editing Outlook contacts

Article

Welcome to this Microsoft Access expert tutorial on importing data from various sources, with a focus on practical methods for managing your data efficiently. Today, we will cover several key topics, starting with how to send bulk emails from your Access database using Word and Outlook. We will then move into techniques for importing and linking to external data, such as Excel spreadsheets, text files, HTML documents, XML files, and even Microsoft Outlook folders.

Let us start by looking at how to send a bulk email to customers you have marked as active in your database. The process involves exporting the relevant customer data from Access to Microsoft Word by running a mail merge, which integrates nicely with Outlook to deliver your messages. This method allows you to format your emails using HTML in Word, so your communications can look professional. To accomplish this, you first prepare a query in Access that selects only the active customers. Then, use the mail merge feature in Word to connect to that query as your data source. You format your email in Word, insert merge fields where you want personalization like names or email addresses, and then send the finished emails using Outlook. This approach is flexible and leverages the strengths of all three Microsoft Office programs.

Now, let us shift our attention to importing and linking data from Excel. Sometimes you will receive information in Excel format that you need inside your Access database. Importing brings a snapshot of the data into Access, so it does not change if the original Excel file is updated later. Linking, on the other hand, connects Access directly to the Excel file. This is useful if the file is on a network and you expect updates. Any changes made to the Excel file will appear in your linked Access table, keeping your data synchronized without manual intervention. To import, use the External Data tab in Access, choose Excel, and follow the wizard's steps. To link, use the same wizard, but select the Link option instead of Import. You may need to clean up the raw Excel data before importing or linking by ensuring your column headings are clear and consistent, and that there are no unnecessary blank rows or formatting issues.

Beyond Excel files, you often encounter data in text files or HTML files, particularly when extracting information from web pages. Access allows you to import structured text files such as CSV or tab-delimited files. Use the External Data tab, choose Text File, and let the wizard guide you through selecting delimiters and column options. For HTML files, start by saving the web page from your browser. Then, in Access, import the HTML file. During the import process, Access will present you with a list of tables it finds within the web page, such as lists of stock quotes, weather data, or any other tabular information. Choose the table you want, and import it just like any other data set.

XML files are another powerful way to exchange data. XML is a universal format used for sharing structured data between different database and web applications. In Access, you can both export your data as XML and import XML from other sources. When you export as XML, you have the choice to include just the data or both data and schema, which is the structure of your tables. To export, select your table or query, go to External Data, and choose XML File. To import XML, use the External Data tab, pick XML File, and follow the prompts to map the XML elements to the appropriate fields in your Access tables. Understanding schema is important, as it tells Access how to interpret the structure of the imported file.

An advanced feature you might find useful is the ability to link your Access database directly to your Outlook folders. This opens up the possibility to read data from your inbox, manage your messages, and even update or create new contacts within Outlook programmatically. To set up a link, open Access and use the External Data tab, choosing More and then Outlook Folder. You will see a list of available mail and contact folders to link. Once linked, your emails or contacts become accessible in table format inside Access, and you can run queries, update records, or even delete information as needed. To interact with Outlook data programmatically or automate processes, you will need to use VBA.

Here is a simple example of how to use VBA to send an email in Access through Outlook:

Dim olApp As Object
Dim olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
With olMail
.To = "[email protected]"
.Subject = "Test Email from Access"
.Body = "This is a test email sent through Outlook using Access VBA."
.Send
End With
Set olMail = Nothing
Set olApp = Nothing

This script creates a new Outlook mail item, fills in the recipient, subject, and body, then sends the email. You can modify this code to loop through a list of email addresses if you want to send to multiple recipients.

Throughout this process, always check your data for consistency and accuracy before importing, linking, or exporting. Data coming from external sources may require cleanup, such as removing unwanted characters, handling missing fields, or normalizing the format.

In summary, mastering the ability to import, link, and export data with Access empowers you to handle a wide variety of data sources, automate workflows, and maintain consistent, up-to-date information across your organization. Whether you are importing from Excel spreadsheets, extracting tables from web pages, exchanging data using XML, or integrating with Outlook, Access offers the tools you need to connect and work with your data efficiently.