Using Copy and Paste from to fill a built-in mailing list table from an Excel sheet

An easy way to prepare the filling of larger mailing list is by using Excel for many reasons. In the following a possible way to do this is described, but you obviously can changes where deemed appropriate.

Preparing Excel sheets

Note when preparing an Excel file to copy from, the columns in the range you intend to copy must map onto the fields in the Access table in the same order.

Mailing lists overview in Excel

If you have many mailing lists, you may want to make an overview in Excel of them, e.g.

lists in excel

RecipientInMailingList overview in Excel

The example below can be used in two ways:

  1. to fill the Recipients table (using column A).
  2. to fill the RecipientsInMailingList table (using column A and B).

excel recipients in mailinglists

Copying mailing lists from Excel into the Mailinglists built-in table

After preparing the Excel sheets, you want to copy them into Access.

Filling the tables must be done in the following order

  1. MailingList
  2. Recipients
  3. RecipientInMailingList

How to copy an Excel range in an Access table

After creating the Excel overview, you can copy a selected range into the MS Access built-in mailinglist table.

  1. Open the Built-in mailing list table
    To have it appear, you may have to open the database while keeping the Shift-key pressed. That way the database window (Access 2003) or Navigation pane (Access 2007 or higher) will appear. Click on the Tables section. Double click the table to open it
  2. select the whole Excel range you want to copy (Ctrl-C)
  3. In the mailing list table, click the ‘New record’- record selector’ [*]. (this selectes the row and moves the cursor outside the columns cell)
  4. Press paste (Ctrl-V)
    The new records get added. Any dupplicate records are ignored, but you will get warning messages.

After copying, open the Paste Errors table (if present) to check what was refused and see if corrective actions are needed.