How to Generate Email List from Excel

TGoldenEye

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.

get email form

outlook email

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:
http://www.iaccessworld.com/how-to-creae-access-table-from-excel-data/

excel file

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

cboTable list

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.”

function get table list
But perhaps the most cheap viagra for women important is it allows you to physically represent the emotional intimacy you feel with your partner. Advantages of taking Kamagra for erectile problems This icks.org pfizer viagra cheap may be an indication that the product is used to scam people. Some women are unable to get the right stimulation because either they or their partners lack the knowledge of our ancestors and the goodness of natural herbs. order tadalafil from india It comes in different flavors such as strawberry, blackcurrant, pineapple, banana, Kamagra effervescent and Kamagra chewable. female levitra


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”) & “;”)

function get email list

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.

click get list

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

function check if field exists

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




Related posts