How do I get a list of email addresses from Excel to Outlook?

Excel has many fantastic features for data analysis, including the ability to export lists of emails into Microsoft Outlook or other programs. However, if you're working on a project where your contact list is already built into Excel (or its source file), there's no easy way to move this information over to another program like Outlook without first converting it. Here are some tips to help make this process easier.

To demonstrate what we'll be doing here, let's say you've got a spreadsheet full of people who work at different companies all around town, so their company name will appear next to each person's row. You want to convert this into a distribution list for sending out e-mails later because most employers require such a thing from employees before they can send out any official correspondence via mail.

For example, my current task list looks something like this:

You may not realize it, but this isn't actually very useful. The problem with having just one column filled with the word "Company" is that you won't see which company each employee works for unless you scroll down through thousands of rows. This makes it difficult to find someone when searching for specific jobs. It would also be nice to add additional columns for positions held, salary ranges, etc., but that requires lots more effort than simply importing those same contacts into Outlook.

The good news is that the following methods should allow us to use our existing Excel contacts as a distribution list for Outlook while still keeping everything neatly organized within the original spreadsheet itself.

How do I extract a street name in Excel?

First off, we'll start with extracting the street name associated with each entry -- the part after the "@" symbol. If you look closely at the screenshot above, you'll notice that only the left half of the string includes characters up until the @ sign. In order to isolate the street number portion of the e-mail address, we'll need to manually copy the right half of the string then paste it back onto the rest of the string using CTRL + V.

Once pasted, select both pieces together with your mouse, then highlight and delete anything between the two strings. Now click File - Info - Properties. From the General tab, change Custom field size to 255 character fields, type "Street No." in the Name box under Field Size Mode, set Data Type to Text String, and check the option Automatically detect values stored as text. Click OK to save changes.

Now go ahead and repeat this step for every single cell containing either a Company name or a Street Number. Once done, you'll end up with a long table like this:

As mentioned earlier, this might seem tedious, but once you finish copying/pasting the streets and reworking all the cells, it goes much faster!

How do I separate names from addresses in Excel?

Next, we'll take care of separating the actual address from the name. We'll use tabs again to accomplish this. To begin, create multiple tabs called something along the lines of "Name," "Address," and "Text." Then fill each tab with whatever text you'd normally enter into the appropriate fields for your contacts. For instance, the Address tab could include things like Home Phone, Mobile Phone, Work Extension, Fax, Postal Code, City, State Code, Country Code, Pager Email, IMEI#, and similar details.

In addition to filling each tab, we'll also need to insert spaces between each piece of text. Selecting and deleting individual words doesn't quite cut it, since inserting space automatically inserts blank spaces wherever possible. Instead, press Ctrl + Shift + Spacebar simultaneously to select entire chunks of text at a time.

Here's what mine looked like initially:

After pressing the shortcut combination, my screen was covered in tiny little boxes. Not exactly pretty. But don't worry about those yet. First, try selecting just the top line of text within each section. Press Delete and hit Enter. Repeat this action throughout each section of text, checking to ensure nothing accidentally gets deleted. When finished, your final results should resemble this:

If you happen to lose track of which sections belong to whom, you can easily rename them. Just open the respective tab and double-click on the title bar to edit it. Alternatively, you can right-click on a particular tab header and choose New Group... followed by Rename.

Note: if you prefer to keep the default tab titles ("Name", "City"), you can always switch them out whenever you wish. Simply navigate to View - Tab Bar - Change All Tabs to None. Your tabs will disappear completely, leaving room for larger images at the bottom. Unfortunately, this method does not allow users to drag tabs into place, even though you can rearrange groups. Hopefully Microsoft adds this feature in future updates.

How do I separate address and text in Excel?

Question

I am using Office 2010 and want to export my contact information into Microsoft Outlook so it is easier for me to manage the data. The problem arises when there are multiple entries that contain more than one type of item, such as street name, city, state or zip code. The following are some examples of what has been entered into my spreadsheet:

How do I separate addresses without commas in Excel?

Answer

There is no easy way to split up your comma separated values by hand. However, you could use VBA (Visual Basic) to create a function that will automatically remove all white space characters before splitting each entry at every comma character. This means that if any spaces appear between words then they would not be included in the resulting array.

Here's the source code for this method:

Sub FindAndReplace()

Dim arrInput As Variant

Dim strDelimiter As String

Dim nPos As Integer

Dim i As Long

Dim lngStartPosition As Long

Dim lngEndPosition As Long

Dim vItem As Variant

'Change these two variables below to suit your needs

Const sSource = "C:\Users\Documents\MySpreadsheets" & "\Contact_List1.xlsx"

const sOutputFileName = "c:\temp\contactlist2.csv"

With CreateObject("Scripting.Filesystemobject")

.getfolder(sSource).Open For Read Access Write Mode

Set oFSO = New FileSystemObject

Set fso = oFSO.GetFolder(sSource)

Set fldrCurrent = fso.FindFirst(sSource & "\*.xl*", [SearchOption] -4202)

Do While Not fldrCurrent Is Nothing

If Right$(fldrCurrent.Path,"\") = ".xls" Or Left$(fldrCurrent.path,"\")="." Then

On Error Resume Next

'Create our output file on disk

Open OutputFileForWriting For Append AS #1

Print #1, Join(arrData, Chr$(10))

Close #1

End If

Loop Until fldrCurrent.NextEntry(-4198)%=0

Do While fldrCurrent.ReadText(-4197, 1, "#text')=""""'and Len(#text'')=6Then

arrData = Split(Trim(fldrCurrent.ItemArray), ", ")

ReDim Preserve arryFld(UBound)

arryFld(Ubound+1) = Trim(fldrCurrent.ItemArray)

UBound = UBound + 1

End If

Loop

'Delete anything left over after we're done processing...

Kill OutputFileForReading

End With

End Sub

You'll find the path where your files are stored above. Replace the Contact_List1.xlsx with your own file location. You should also change c:\temp\contactlist2.csv to replace whatever file extension (.txt,.csv,.xml etc.) your CSV contains. Save the script anywhere you like, but make sure you keep its folder structure intact.

Go ahead and run the macro now. It may take a while depending upon the size of your workbook. When it finishes running, close out of Word and open up Excel. Head back to the Script Editor window, click View Code. Paste in the script you just created and select Run ActiveX Command Button. Now go back to Word and check out the results!

Now that you've got the process down, let's break down exactly what happens here step-by-step. Here's a quick breakdown of the lines of code:

With CreateObject("Scripting.Filesystemobject")

Set oFSO = New FileSystemObject

Set fso = oFSO.GetFolder(sSource)

Set fldrCurrent = fso.FindFirst(sSource & "\*.xl*", SearchOption "-4202")

Do While Not fldrCurrent Is Nothing

If Right$(fldrCurrent.Path,"\") = ".xls" Or Left$(fldrCurrent.path,"\")="." Then

On Error Resume Next

'Create our output file on disk

Open OutputFileForWriting For Append AS #1

Print #1, Join(arryFld, Chr$(10))

Close #1

End If

Loop Until fldrCurrent.NextEntry(-4198)%=0

Do While fldrCurrent.ReadText(-4197, 1, "#text'')"=""""" And Len(#text'')=6Then

arryFld = Split(Trim(fldrCurrent.ItemArray), ", ")

ReDim Preserve arryFld(UBound)

aryFld(Ubound+1) = Trim(fldrCurrent.ItemArray)

UBound = UBound + 1

End If

Loop

Kill OutputFileForReading

This works well because the first time through the loop, the items being read from the sheet only include the text within quotes (""). In other cases, you might encounter empty rows which cause errors. To avoid those issues, add another condition inside of the second loop to look specifically for cells containing nothing except tabs.

As long as the cell doesn't start with a tab, blank line, comment, or a quote, everything else gets processed correctly. Once the next row starts with something other than simple text, ignore it and move onto the next iteration of the main loop.

When you come across a line which includes both a district AND a coordinate, simply grab the value from either field and paste it directly into the appropriate column in Excel. That's about all there is to it!

How do I separate addresses from districts in Excel?

Answer

So many people ask this question, yet very few seem willing to put forth much effort on their own. There really isn't too complicated of a solution. All you have to do is tell Excel what delimiters you'd like to see used throughout your entire dataset.

In this case, since we're dealing primarily with postal codes, it makes sense to choose a delimiter that closely resembles the format of ZIP Codes. So, using the same example as earlier, lets say you wanted to pull out states. Simply highlight the area you wish to modify and head to Home-" Controls-" Delimeter Boxes. From the dropdown menu, select Postal Address Patterns. Select State/Province Postcodes.

Once again, press OK and scroll through until you reach the end of your data set. Make note of any patterns you notice and try to pick apart areas that repeat themselves consistently. Once you find a pattern, copy and paste that section somewhere safe and leave it alone. Keep going until you finish converting everything.

Now, whenever you ever deal with any sort of postal code, you won't even need to remember how to manually isolate each different portion of it. Everything will already be done for you!

It's worth noting that there are plenty of additional options available in the Delimiter Boxes panel. Some of them allow users to specify whether to preserve whitespace or delete it altogether. Others give users control over the number of digits allowed per segment. Still others offer users more flexibility when deciding what delimiters to use. Depending on your circumstances, you might decide to incorporate several types of delimiters into your project. Just remember to always stick to one consistent rule.

How do I separate addresses from coordinates in Excel?

Answer

While most customers who purchase mailing labels usually don't bother separating postal codes from actual addresses, doing so can still simplify things later on. Let's assume you had a large database full of phone numbers, names, and addresses. Instead of having to search for specific sections to figure out your neighbors' homes, you could easily filter through the whole thing based on postal zones.

To get started, navigate to Insert-" Text Box-> Text Box Special Formatting-> Separators. From the dialog box that appears, enter a single dash (-) followed immediately by Tab. Hit OK once again.

From that point forward, whenever you need to convert a postal code to an actual address, double-click on the separator symbol and drag your mouse cursor rightward along the length of the bar. A small pop-up window will appear asking you to input a new selection range. Input a postal zone and hit Enter. Your converted postal code will be displayed beneath.

Repeat this process wherever necessary. Whenever you come across a situation where you must determine which part of the address corresponds to which piece of geography, simply follow the steps outlined previously.

How do I extract addresses from streets in Excel?

Answer

Let's face it, there aren't many situations where someone actually cares to view a map of their neighborhood. Even fewer people feel compelled to draw a circle around a particular house in order to identify it as belonging to a

We've all received those emails asking us for our contact information, and we're usually annoyed by the request because it's not very useful or easy to provide. However, there are times when you might want to give out your entire distribution list in one go (e.g., a promotion), so here is a quick guide on exporting and sending large lists of people via email using Microsoft Office programs.  Let's start off first with copying data from Excel into Outlook.

Can you copy a column of email addresses from Excel?

Yes! You can use this method if you only want to export email addresses into another program like Outlook. The problem will be that each recipient gets their own separate entry, which means you won't receive any other details about who they are. This isn't really what most users would expect either - especially since many companies require much more detail than just name, title and email address. So let's see if we can solve this issue without losing too much data.

The following instructions assume you already know how to create a Contact Form with several fields such as First Name, Last Name, Address 1, City, State/Province, Postal Code, Email, Phone Number and Message Text. If you don't then check out my article about creating forms from scratch. Now that you understand how the form works, lets continue onto importing data directly into Outlook.

Here's what you'll need to do:

Select "Paste Special" under Paste Options menu item at the top of the Data tab.

Choose Transpose option.

This should add every single cell within the selected range to the right hand side of the spreadsheet where you pasted them. Once done click OK.

Now head over back to your Contact Form settings. In the Send Mail To field type "CONTACT1", replace CONTACT1 with whatever group of recipients you wish to mail to. Head down beneath the Recipients section and select New Group...

In the next window simply enter a description for your new group, and hit Create. Repeat steps 4 & 5 until you've created as many groups as you'd like to mail to. By doing this, you can now combine multiple columns of email addresses together to make up a larger mailing list in one shot. For example, you could merge the names and emails of everyone living in California while also adding in the phone numbers of anyone calling from that state.

You may find yourself wanting to change some things around after completing the above process. Maybe you found that the message text was way too long, or maybe you needed to change something else entirely. Either way, you can always come back into Windows Explorer and edit the files manually. Just open up the.csv file for editing purposes. That said, sometimes it's easier to work with a smaller subset of items rather than try to remember everything later. Let's take a look at how you can easily send a mass email to thousands of people.

How do you email a list of names from Excel?

If you're familiar with VBA scripting language, you probably noticed that you can run code based upon selection criteria. Essentially, you can write scripts that automatically perform specific tasks based upon user actions. Here's what you'll need to do:

Head over to Developer Tools, located towards the bottom left corner of your screen. Click Visual Basic Editor and paste the below script between the two braces. Make sure to save the file with a ".vbs" extension. Save it somewhere accessible like C:/Program Files/Microsoft Office/root/office16/bins/. Restart your computer and navigate to the location of your saved script. Right click the icon and choose Run Module.... A small box will pop up saying File Not Found. That's okay, it happens often due to permissions issues. Simply right click the folder and choose Properties. Under Security Settings set Permissions to Customize. Change Everyone Full Control to Read Only. Then close properties and restart again. It should now show the Scripts Folder listed in the explorer dropdown. Double click your filename and follow the prompts. When finished, exit the application.

Note: If you're running Vista or 7, you must enable ActiveX Controls before opening the VB editor. Go to Start > All Programs > Accessories > Command Prompt / Shell. Type cmd.exe and press Enter. At the prompt type msc vbscript.cpl. Press return and browse to where your script resides. Once inside the active X control panel, locate the button labeled Advanced. On the second row from the bottom, double click Enable Content. Finally, in the dialogue box that appears, tick Automatically launch applications and contents displayed in this applet. Hit Ok and the VB editor should appear. Copy and paste your script between the brackets. Again, ensure that you save the file with a ".vbs" extension.

Once completed, select View -& References and search for Dim wshShell. Set reference to nothing, as shown. Next, select Tools -& References Manager, and scroll through available references. Choose Microsoft Scripting Runtime Library, and select Yes. Close Reference manager. Open your newly created.vbs file. Find the line Dim oWSHShell = WScript.CreateObject("wscript.shell") and change it to Dim oWSHShell = GetObject(“winmgmts:{impersonationLevel=impersonate}"- Select “SendEmail” From CurrentProject Property Pages) Then, change winmgmts: to shell, and impersonationlevel to ImpersonateUser. Note, some computers may default to NetworkService whereas others may be logged in with LocalService. Check out TechNet forum post #1043 for further clarification. After changing the last line, input your credentials to access the SMTP server. Lastly, input the same username and password you used earlier. Once complete, click Debug and run the script. Your results will vary depending on your Internet connection speed.

Can you email a list from Excel?

Excel has a feature called External Data Connections. With this tool you can connect various sources such as databases, web services and XML documents. One particular service known as OLEDB allows you to extract CSV files from spreadsheets. What does this mean? Well, it means that you can actually pull data straight from Excel and output it to Outlook as a list. Sounds simple enough, right?

To begin, open up Excel and insert a table containing email addresses. As mentioned previously, we're going to ignore additional fields such as FirstName, Title etc. We're only interested in getting the email addresses themselves. Next, head over to Home Tab-" Insert-" Object ("Text Import Wizard"). Select Comma Separated Values (.csv) file format and pick Browse. Navigate to wherever you stored your exported csv file and select it. Leave the Delimeter setting blank. Ensure that Use local separator is checked. Finally, click Finish.

Upon completion, you will notice that the object has been added to the Ribbon. Highlight the object and drag it over to the Sheet tabs area. Drop it once it shows up properly. Notice how the ribbon changes accordingly. Look at the little arrow beside the external source label. This indicates that the data is being pulled from outside of Excel.

Next, highlight the header cells and delete them. Do this by selecting the whole block of headers and pressing Delete key. Finally, head over to Home Tab-" Design-" Formatting Paintbrush. Add style formatting to the rows to denote different categories. For instance, we could color red for Emails and green for Phones. Also, feel free to move around the headers anywhere you see fit. Don't forget to apply the styles afterwards.

Finally, head over to Home Tab-" Ribbon-" More Commands..." Click Apply "OK. This final step forces the ribbon to recognize the imported objects. Upon execution, you should get a notification that says "Data Validation Completed." Congratulations, you've successfully sent an email list directly from Excel!

How do I send a mass email list from Excel?

So far, we've looked at methods of transferring a list of email addresses from Excel into Outlook. But what about mass emails? Well, unfortunately, the previous method doesn't allow us to include anything beyond basic info such as subject lines, content or attachments. However, there is still hope. There is a function called SHFileOperation() which allows us to upload multiple files simultaneously. Using this function, you can attach a bunch of Word Documents in bulk. Below is sample code showing exactly how to implement this technique.

Sub SendListEmails2()Dim fso As New FileSystemObjectSet fso = CreateUnifiedFolderFromString("C:\Users\Desktop\testfolder")fso.CopyDirectory _

"(@MID(*{93E92860-2400-11CF-8FDA-00004A94CBF6})ChildDocumentsAndSettings)", "@EXD*L(\")", TrueWith Application.GetNamespace("Word.Document.")Do While Len(Dir$()) <> 0For Each Doc In.ItemsEnd WithDoc.SaveAs Filename:="Outlook_Test.docx"Next End Sub