How do I Import contacts from Excel into an email?
If you want to move some data out of one application or another, but don't know where that information resides, then importing it into Microsoft Office is probably going to be part of the solution for you. Importing contacts from Excel isn't too difficult if you have all of the right tools at hand. The following article will walk through the process so you can import your contacts from Excel directly into Outlook 2010, 2013, or 2016.
As mentioned above, there are two methods available to convert your existing Excel spreadsheet (or any other source) into a CSV file which contains a record for each individual contained within your workbook. These files can then be imported directly into various applications such as Outlook, Word, Powerpoint, etc., without having to rely on the standard Windows Mail "Send To" feature. Of course, this also works with mail clients like Apple's Mail app, Thunderbird, Entourage, Postbox, Gmail, iCloud, Yahoo! Mail, AOL Mail, and more.
The first method involves using VBA code to extract your entire Contact Sheet, sort them alphabetically by last name, remove duplicates, add headers, and put everything together into a nice neat CSV formatted file. This approach may take longer than just grabbing only certain columns, sorting, and exporting as described below. However, it does allow you to customize exactly what gets exported to a specific format. If you're working with hundreds or thousands of records, however, it might not matter much since both approaches produce similar results. You'll see examples of both in the screenshots section below.
In addition, you could always simply copy & paste all of those entries into a new text document, save it as a CSV, and open the resulting file with Notepad++. There, you can adjust column names manually or let Notepad++ automatically rename the fields based upon their content. Once done, you should end up with something resembling the screenshot shown below. It's pretty close to what we ended up producing after completing our own version of this task.
It really depends on whether you need to tweak your personal preferences regarding field naming conventions, as well as how many items exist in your overall database. For example, it would make sense to include all contacts regardless of gender when migrating from Excel to Outlook. In contrast, if you were moving between different versions of Exchange Server, perhaps you'd prefer to separate users/contacts according to department or office location.
Whatever your needs, keep reading -- we've got plenty of tips and tricks along the way!
How do I Import email addresses from Excel to Outlook 365?
To begin, download a free tool called XLOOKUP [Broken URL Removed] from www.msofficecentral.com. Then launch Excel and go to File - Options - Customize Ribbon - Main Tabs - Data - Import And Export. Select Text Files (.txt) and click Next. Finally, select Create New Worksheet for Imported Data, check Show Field Names As Comments, and choose Comma Separated Values (.csv). Hit Finish and wait while the data imports.
Once complete, head back over to XLOOKUP again. Click Edit Fields... on the bottom left corner of XLOOKUP's main window. From here, pick All Fields except Header Row(s), and unselect First Column Name. Now, hit OK. Your screen should look somewhat similar to the image below. At least, that was my experience once XLOOKUP had finished running its magic. Note that the dropdown box next to Last Name defaults to Yes. Change it to No if prompted. We actually won't be extracting that info until later. Click Close.
How do I create a contact list from Excel?
Now that we've created a nice clean CSV file containing all of your contacts' relevant information, it's time to merge and organize them properly. Start off by opening Excel. Head to Home - Insert - Table. Pick whatever type of table suits best for your purposes. Make sure to drag down the rows to fit the total number of people you wish to grab from your Contacts List. When satisfied, delete unnecessary rows by pressing Delete Cells B3:B11. Also, if you plan on copying these cells elsewhere, note that they contain formulas. So, before deleting anything, ensure that no values are entered anywhere else outside of the ones selected.
You now need to decide where you want to place your newly generated table. Since we're dealing with a large amount of data, consider placing it somewhere easily accessible. That said, remember that you can insert tables wherever you want inside of a normal sized sheet. Just double-click on the cell and press Ctrl +T to bring up the Tables menu. Here, select the table you want to embed and drag it onto the desired spot. Repeat this process for every row.
Click the small arrow icon next to the upper right side of your header row and select Sort Rows.... A dialog box will appear asking you to enter a custom name for your sorted table. Type something sensible like SortedTableName. Press OK.
Finally, highlight the entire contents of your table and press Shift+Down Arrow on your keyboard. A contextual context menu bar will pop up. Choose Merge and Consolidate... and follow the prompts. Confirm the destination path and press OK. Wait for completion. Once completed, your table will resemble the screenshot below. Again, depending on your requirements, feel free to modify things around a bit. Notice how we separated males and females as per your preference earlier. But for this particular scenario, we chose to lump everyone under the same umbrella.
Can I Import a list of Contacts from Excel into Outlook?
Yes, you most certainly can. Simply repeat the aforementioned procedure, replacing the word "Contact" throughout the instructions with "Person." By doing so, you'll generate a Person table instead of a Contact one. Afterward, proceed to merging and organizing as explained previously. Once complete, change the Destination Path option located near the top of the final dialogue box. Instead of selecting Personal Document Settings...", choose Other Documents... and browse to whatever directory holds your preferred address book program.
Afterwards, follow the exact same steps outlined above. Only replace the words "Contact" with "Person".
How do I create a mailing list from Excel?
This is quite simple as well. Begin by downloading XLOOKUP and run the wizard as detailed above. Once complete, edit the Last Name field and set it to No. Do likewise with the rest of the fields listed there. On your target machine (Outlook, say), navigate to Tools - Address Book - Preferences. Under Advanced Features, scroll down until you find Import / Export Utilities. Ensure that Automatically detect changes during import is checked. Afterwards, under Email Addresses, enable Send me emails about new arrivals in my contact list. Next, switch over to Rules. Check Always apply rule to current incoming messages. Lastly, click Save.
When Outlook loads, you'll notice a brand new tab has appeared. Go ahead and give it a test drive. Remember, whenever you receive an email with someone in your contact list attached, that person's profile page will show up in the pane alongside yours. Within seconds, you'll realize why this is a great means to manage your ever growing address books. Even better yet, you can assign tasks to individuals via email or otherwise.
You can save time by importing your contacts from Excel to Outlook, rather than manually adding them one at a time. We'll show you exactly what settings to select for this task. So that we all work with compatible data files, let's first clarify some terms used throughout this article:
Contact List - A spreadsheet listing names and emails (both personal and business) on separate rows. You may have multiple Contact Lists within a single document or spreadsheets. For instance, there could be several lists for family members, colleagues, etc.; each with its own set of columns.
Export File Format (.xlsx,.xlsm,.csv) - An XLSX file contains tabs separated by sheets, while the equivalent XLM format is XML based on OpenDocument SpreadsheetML standard. The CSV file is a plain text file containing tabular data which has been exported from any program using Save As feature. It is not related to Microsoft Office suite but it is considered as universal file type because it works across different programs including Apple Mail, Thunderbird, Evolution, Eudora, KMail, Sylpheed, Icewarp, Horde/Icecast and more. If you are unsure about the exact name of the file extension, try opening it in Notepad or TextEdit.
Import and Export Wizard - This tool provides users with a simple interface through which they can choose certain parts of their documents and then download selected items to another location. Users don't need to install additional software or plug-ins. The wizard will automatically open when you click "File" menu option. However, if you want to skip these screens, just go directly to Step 3 below.
CSV File - Also known as Comma Separated Values or ASCII Text File, CSV stands for comma separated values. Although new lines are also included, carriage return characters ("CR") are excluded. In other words, whenever you see a line break such as CR/LF (carriage return followed by a line feed), it indicates two distinct records instead of one continuous string of information.
So now that we've covered the terminology, let's begin setting up our mail client once again.
Note: Before proceeding further with this tutorial, please make sure that both sides of your imported contacts look similar. That means either all of the entries should be formatted similarly, e.g., same column headers, or else there shouldn't be too many differences between the source and destination folders. Otherwise, you might end up deleting someone else's entry unintentionally.
In addition, note that since most people tend to store phone numbers separately, so only include those ones in your target field group. And remember to remove duplicate entries, otherwise you won't be able to merge them together later.
Can you import Excel contact list into Outlook?
If possible, start off by exporting your contacts from Excel. Here's how to do that:
Open Excel. Go to Data & Sort & More Formats... located under Home ribbon. Select Custom sort order radio button and enter the following criteria: Last Name & First Initial & Middle initial. Click OK after entering your sorting parameters. Then head back to Home ribbon and locate Address Block section. There, check the box next to Email Addresses. Finally, scroll down until you find Phone Numbers and Mobile Phones sections. Make sure to uncheck everything except Personal Fax Numbers.
Now press Ctrl + Alt + Shift + Esc keys simultaneously. This activates Windows' Task Manager. When it opens, highlight Processes tab and navigate to Microsoft Word.exe. Right-click on Word icon and select Send to -> Clipboard. Afterward, switch back to Excel window and right-click anywhere empty space. Choose Paste special and select All Cells. Now head over to Find and Replace dialog box via Edit " Search and replace.... Enter search term(s). Press Replace All. Your copied data will now appear in excel sheet.
Go ahead and close Window Explorer. Next, restart Outlook 2010-2016. If you're running newer version of MS office, simply launch Outlook app and wait till it loads completely. Once done, follow these instructions below.
How do I import email addresses from Excel to Outlook?
When you import your contacts from Excel to Outlook, you should expect to see all existing email addresses listed in corresponding boxes. But if there are missing or extra addresses, here are ways to fix that:
First thing you must do is ensure that your email account setup matches your current profile. To do this, open Control Panel " Accounts. Check out whether you have entered proper email ID and password combination. If yes, proceed to the second solution below. Otherwise, you should verify your credentials before continuing forward.
Next, head over to Account Settings page and double-check your internet connection speed. Some older versions of Outlook had issues connecting to slow networks, like dialup connections. Therefore, you should check if Outlook correctly connects to your ISP provider.
Finally, if nothing seems wrong with your configuration, try checking your spam filters. Spam controls sometimes block legitimate messages sent by important accounts like banks or credit card companies.
To avoid getting caught in this situation, you should enable allow less secure apps option under Account Options area. This way you can still access sensitive services without worrying about being flagged as suspicious IP address.
How do I import a list of Contacts into Outlook?
Once you successfully connect Outlook to your Gmail account, it would take no longer than 10 minutes to sync your entire list of friends from Excel to Outlook. Note that if you want to sync more than 100 contacts, you should consider upgrading to Premium edition of Outlook 365.
Here's how to add your Excel contact list to Outlook:
Head over to Start Menu. Type outlook.com and select the best match. Head over to New mailbox screen. On top left corner, click Create my free @outlook.com account link and sign in.
Launch Outlook web application. From the main dashboard, browse down until you reach My Folders heading. Click on it and expand Other Folders. Next, right-click on Contacts Folder and select Move… option. Navigate to your desired location and hit Finish.
On top right side of Outlook home screen, hover your mouse pointer over inbox button and click on it. Scroll down and click on Import mailboxes... located towards bottom part of your browser. Browse down until you spot Contacts option and click on it. Next, click on Browse button and select the file you wish to import. Hit Ok after selecting the correct file.
Afterwards, refresh your inbox and test whether the newly added contacts are available.
How do I create an address book from Excel to Outlook?
This process requires a bit more effort and patience compared to syncing email addresses. Nevertheless, creating an address book from Excel to Outlook isn't difficult. Follow these instructions below for better results:
Download latest copy of Excel. Launch it. Switch to Ribbon view. Click on Developer tab. Locate Imports submenu and select External Data category. Inside this category, click on Get external data….
Select Source Database dropdown menu and choose Connection String value corresponding to your database schema. Leave remaining options unchecked. Set the Filter By row header checkbox to True. Lastly, hit OK.
Your Excel data will now transfer to the adjacent pane. Double-click on your preferred table and select Load Tab Delimited.. Browse down until you find Contact Information cell and double-click on it. Change delimiter character to Pipe symbol and leave rest unchanged. Press Ok.
Next, head over to Insert menu and select Field Group. Expand Main Tabs and drag Contact Names field to the center area. Repeat this action for every necessary field. Do not forget to update Display Name attribute inside this field group.
Click on Design Mode button located near upper left corner of your screen. Drag AutoFields button onto the lower right part of your screen. Keep both buttons placed above InfoGroup1 field group.
Right-click anywhere blank space inside InfoGroup1 area and select Rename... menu item. Input a unique title and press ok.
Save changes made to your design layout by clicking Apply Layout... located on top right corner of your screen. Refresh your inbox and test whether all required info loaded properly.
Repeat the same procedure described above but change filter parameter to False. Simply put, you should load your data according to your preferences.
For example, you may prefer loading only email addresses, ignoring others like mobile phones or faxes. Or maybe you'd rather keep all of your contacts regardless of formatting. Whatever choice you decide upon, make sure that your chosen format meets requirements of your intended recipient.
Have something to add to this story? Share it in the comments.
If you're looking for ways to make data entry faster or more efficient, importing contacts from Excel is one way that can help. This article will show you how to export those contacts from Excel to Outlook (2016-2010) so they are ready to be emailed out as a mass mailing. We'll also look at some other methods to send emails with lists of names stored on your computer.
For example, if you have a spreadsheet containing addresses, phone numbers, birthdays, etc., it's easy enough to copy all this information into a new mail message window by hand -- but wouldn't it be nice if there was a quick way to just click "Send" instead of copying each individual address over again? That's what we're going to explore here.
To see our solution in action, download the sample file used below (it contains two sheets of test data). The first sheet has addresses and the second sheet has dates of birth.
The following instructions assume that you've already set up Microsoft Office 365/Outlook 2010-2013 to accept incoming messages via POP3. If not, follow these instructions on MSDN.com [No Longer Available]. Once you've got your Inbox set up, go ahead and open Microsoft Word 2013.
Importing Contact Data From Excel Into Outlook
First things first, let's take care of the technicalities involved in exporting those contacts from Excel to Outlook. You probably know that when you select cells in columns A through J on Sheet1, then press Ctrl + C followed by Ctrl + V, you end up with copied cell contents inserted into every single column in Sheet2. Well, in order to actually insert them into Outlook, you need to do something else.
In fact, to accomplish this task, you need to manually edit five different registry entries. It isn't exactly user friendly. However, once you know where everything needs to go, getting started is pretty straightforward...and we'll walk you through the entire process in detail right now!
Open Registry Editor (type regedit into Windows search bar), navigate down to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\General. Then find and double-click on CurrentVersion\Explorer. Now, under Explorer\Main, scroll down until you spot ShellExView32.exe. Right-click anywhere within the white space left next to ShellExView32.exe, choose New " DWORD Value, name it EnableConverterModeDWORD, and change its value to 0. Click OK, close Registry Editor, and restart Outlook.
Now, whenever you try to paste any text using CTRL+V, a dialog box should pop up asking whether you want to enable Paste Special before pasting. Choose Yes, and you should be able to successfully transfer cells between worksheets without having to worry about converting anything.
Next, head back to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\\xpspim\8D809AFFF\Parameters. Open Parameters using Notepad or another text editor. Locate and delete xpsdl.dll. Save changes, close the registry editor, and restart Outlook.
Finally, right-click anywhere on the Taskbar and choose Properties. Select Compatibility tab, uncheck Run this program as administrator, check Automatically detect settings that match my system preferences, and hit OK. Restart Outlook.
Once you finish making sure that all the necessary registry edits were made correctly, you can fire off a command line instruction to bring up the Mail Setup wizard. Go to Start | All Programs | Accessories | Command Prompt. Type msi.cpl /qr %windir%\\system32\\msiui.exe and press Enter. Within seconds, the Mail Setup wizard should appear.
On the Mail setup screen, select Create Email Message and enter your recipient(s) info. Under To add recipients, select Address book, browse to the location of your Contacts folder, select your contacts, and click Next. On the next screen, choose MAPI Provider - Exchange Source, type in User ID, Password, Server Name, Port Number & Encryption, and click Finish.
Let's say you wanted to save that configuration somewhere safe so you could quickly access it later. First, run the following command in Command Prompt : msinfo32. Afterward, locate the item named Profile GUID and note the number beside it. We recommend adding this profile identifier to your favorite text document and saving it in a place like Dropbox. Alternatively, you can simply bookmark that same page in Chrome.
When you return to the main interface after running the above command, you may notice that the Add Recipients button is grayed out. That's because Outlook doesn't yet recognize the source provider. So, click Change Settings located underneath the dropdown menu, and switch the radio buttons around until you reach ESMTP Sender Application. Finally, input your SMTP server credentials and click Submit Changes.
You should receive an error message saying that the selected account cannot connect to the specified network. This means that either your Internet connection is faulty or your Network adapter drivers aren't installed properly. Check both areas.
It's worth noting that sometimes Outlook will automatically prompt you to install additional software components. For instance, if you're trying to send out large amounts of e-mail very frequently, such prompts might become annoying. Nonetheless, you can easily disable these notifications using Tools | Options | Trust Center. Just deselect Send me relevant plug-in warnings.
After completing the steps outlined above, you can start sending out emails using whatever method you prefer. Try opening up your default e-mail client, composing a new message, and typing in all your contact info. See how long it takes to send out your e-mails!
Can I import a list of contacts from Excel into Outlook?
Yes. But unfortunately, there isn't really much functionality built-into Excel itself that lets us perform this sort of task. Instead, we utilize a third party tool called CSVtoExcel which allows us to convert a comma separated values (.csv) file into an Excel table format. By changing the extension of the exported file to.xlsx, we can then upload this file directly into Outlook 2010-2013. Here's how we would do that:
Create a new Excel workbook and call it something meaningful. Copy all your excel files onto the master workbook. Head to File | Open | Workbooks | YourWorkBookName.xlsm. Double-click on the first spreadsheet in the list. Press F9 to activate Developer mode. Scroll down until you find Cell range = "" and highlight all cells in this section. Hit Alt + X, Y to drag the highlighted area across all rows, including empty ones. Repeat above procedure for all spreadsheets.
Head back to the Master Spreadsheet. Highlight Cells B6:J6 (or whichever range corresponds to the total amount of records you wish to import), and go to Home | Find & Select | Search. Input *.csv and select Find What: Contents Of Files (*.*) in the popup window. When you press OK, Excel will scan for matching filenames. Make sure the Include subfolders option is checked. Leave all options besides Header Row unchecked.
Click Execute, wait for completion, and repeat Step 4 for all remaining spreadsheets except the last one. Don't forget to include the final row header. Close the application, rename the current file as MyTable.xlsx, and proceed to open the newly created file. Noticeably, there's no longer a.csv extension.
Go to Insert | Table. Drag and resize your imported table into a new blank page. Resize the table to fit all your rows in. Delete unnecessary rows, adjust column width slightly, and voila! Now you have a cleanly formatted table loaded directly into Outlook. With this approach, you won't even need to mess with the aforementioned registry tweaks.