How to Get Email List from MS Excel using MS Access
There are many ways to get the email list from the Excel list for sending email to those people. Many people may just copy the email from Excel then paste it on the Outlook new mail one by one. You cannot copy one time from the column C shown on the picture below then paste it on the Outlook mail To, because Outlook needs a semi-colon(;) between each email address. Also there is a blank email for some customers on the list below.
A smart way to get a list from MS Excel is to generate a list of email with a semi-colon (;) between each email then copy them to paste in the Outlook mail To, ready to email as shown in the picture below.
In this How To, I will show you how to get an email list from Excel using MS Access step by step.
Step 1: Import data from MS Excel to MS Access
I will not show you how to import data from MS Excel in this How To.
I already have the How To on this website (the link below).
How to create table from MS Excel data here:
Step 2: Create MS Access form
Create a blank form and insert the following items into this form.
1. Combo Box “CboTableList” to list all tables
2. Text Box “txtEmailList” to display the email list after clicking on a button “Generate Email List”
3. Command Button “cmdGetEmail” with label “Generate Email List” to generate the email list on a textbox “txtEmailList”
4. Command Button “cmdClear” with label “Clear List” to clear the email list from a textbox
Step 3: Get a Table List into combo box.
Normally a combo box has a data source from a table. It looks up a data from another table and lists them in a combo box. However, we want to have a list of tables, not a system tables. The system tables mostly starting with MSys such as MSysAccessObjects, MSysObjects, and MSysQueries etc. We don’t want these system tables in our table list in the combo box. We want to show only the table that we imported/created and also have a field named “Email.” We need to create a function “GetTableList()” in the Module then call it when our form is loaded or opened. We can use the Public Function in Module anywhere in our database without modifying any code within a function.
Assign the function “GetTableList()” to a combo box “CboTableList” on the Form Open Event shown below. After the form is loaded or opened the list of tables will be added as a source of data for a combo box CboTableList.
Private Sub Form_Open(Cancel As Integer) Me.CboTableList.RowSource = GetTableList() End Sub
Function “GetTableList()” is shown in the picture below. This function will collect the tables that do not start with “MSys” name which is a system table. It also uses a function “CheckFieldExists()” to list only the tables that have a field name “Email.” We may have many tables that do not have the email fields so we should name the field that we want to get the email list from as “Email.”
Step 4: Get the Email List
- We need to create a function “GetEmailList()” in the current form. You can put the function under the module, but you will need to add a parameter as an input of function.
- I put this function under the current form so I can refer to a table that selected from a combo box Me.CboTableList for a recordset (line: Set rs = db.OpenRecordset (Me.CboTableList, dbOpenTable).
- This function will skip the record that does not have an email (line: If IsNull(rs!Email) Then rs.MoveNext).
- The field name “Email” is also specified in this function because we want to get the email from the field Email. In this function, I will use a semi-colon (;) to separate the email from each record (line: strList = strList & rs.Fields(“Email”) & “;”)
Step 5: Generate Email into Textbox
After we created the function GetEmailList() then we need to add the code below under the on Click Event of the Command Button “cmdGetEmail” with two parts:
1. Adding If Statement to check if no table selected on the combo box then give a message to select a table from the drop-down list.
2. Assigning the GetEmailList function to text box of a table is selected from a drop-down list on combo box.
Step 6: Create a CheckFieldExists() function
A function CheckFieldExists() returns true if the table has a field of specific name in the parameter. We will use this function on the GetTableList() function to get only the tables that have field name “Email” by using If Statement as shown below: If CheckFieldExists(tdf.Name, "Email") Then strList = strList & tdf.Name & ";" End If
Step 7 Clear a Textbox of Email List
Assign a null (empty) value to a Command Button “cmdClear” with label “Clear List” to clear the email list from a textbox. Put code under the On Click Event Procedure as shown below:
Private Sub cmdClear_Click() Me.txtEmailList = Null End Sub