Discover the Anyleads suite | Find emails, verify emails, install a chatbot, grow your business and more!.
blog

How do I create a mass email list in Outlook from Excel?



How do I create a mass email list in Outlook from Excel?


A contact group is an easy way to share information with your coworkers without having them all sign up for separate accounts. Contact groups also make it easier to send out emails on behalf of the company by linking one address to many employees' inboxes at once.

We're going to show you how to quickly import contacts into Microsoft Office 365 via CSV (comma-separated value) file, which can be found under File " Options " Import & Export " Address Book Settings. Then we'll show you how to use those contacts as part of a mailing list within Outlook later on. You could even use this technique to collect email addresses from other programs like Google Contacts or Apple's iCloud.

Once that's done, let's look at some best practices when sending bulk messages through Outlook using these new lists.

This post was originally published in 2017 but has been updated since then.

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

There are several ways to pull data from Excel into Outlook. If you want to simply grab a single column of numbers, you can select cells A2:B5 and press Ctrl + C followed by Ctrl+ V to copy and paste them directly into the body of an outgoing email.

If you have more than a few columns worth of values, however, here are two methods that will allow you to add rows based off of what's already stored in Excel. We've tested both options below.

Option 1: Use AutoFilter to Grab Rows Based On Criteria From Another Worksheet

First things first: Create a test spreadsheet with four different columns containing names, phone numbers, email addresses, and dates of birth respectively — and make sure there are no blank spaces anywhere. This example uses only three people per row, so if you want to try adding a large number of people, increase this accordingly.

Next, open another sheet where you'd like to place our imported data. In this case, we used Sheet1 because it contains fewer columns than the previous ones we just created. Make sure not to include any headers yet.

Select Columns B and D, enter =sum(Sheet1!$C$3), hit Enter, hold down Shift and click Cells B4. Finally, right-click the selection, choose Format Control, scroll down until you see Conditional Sum Formulas, check Add filter dialog box instead, input Formula #1"="Formula #2, and pick OK. Your formula should now read $B$6=SUM($D$3). Hit Enter again and watch how the spreadsheet automatically creates rows based upon your criteria above.

Now that you've got enough entries to work with, it's time to move onto creating a mailing list in Outlook. First, though, let's go over how to actually send a campaign through Outlook itself.

By default, Windows 10 doesn't give users access to Mail app unless they specifically launch it. You can change this behavior by opening Start Menu " PC settings " Accounts " Email " Send automatic replies. Here, switch Automatic Replies Off and put a checkmark next to Ask me before sending text outside of apps.

To edit existing campaigns, head back to the same screen and click Manage known senders, then Find Senders. After selecting each individual person whose details you wish to update, click Edit Details. Once inside their respective tabs, find Campaign Name and Change To fields and type your preferred name and email address respectively. You can repeat this process for every recipient who needs unique labels.

After everything's set, click Save Changes and exit the window. Now, whenever someone sends you an email, you won't receive it immediately. Instead, you must reply manually to tell Outlook whether you would like to mark it as spam or forward it along to your primary email account.

The reason why we had to run a test first is because Outlook limits the amount of times you can respond to a certain sender in a given timeframe. It does this so you don't flood your own mailbox with too much junk. The limit isn't very high, either — it starts at five mails sent within 15 minutes. So keep that in mind if anyone happens to send you dozens of texts daily.

You can always reset the counter yourself by heading back to the main menu and clicking Recipients " Resend Messages. Select All Sent Items and navigate to the specific message you wish to re-send, then double-click its entry to bring up the Properties pane. Under Message header, locate Delivery Status field and change it to Delivered — uncheck it entirely to prevent future deliveries. Click OK after making changes and exit the properties page.

In addition to changing delivery status, you may need to adjust a handful of additional settings related to your contact list. For instance, you might want to sort them alphabetically or perhaps by date of last activity. Head back to the main interface and click Sort By, then browse through available categories and decide which order suits you best. There are plenty of sorting features built into Outlook, including customizing your lists according to rules such as last modified, creation date, etc., but we recommend starting simple and sticking to basic sorts like Last Activity Date, Birthdays, and Emails Received. That said, feel free to experiment with advanced sorting functions once you've become familiar with how it works.

One final tip: When dealing with large amounts of data, avoid copying and pasting entire sheets wholesale into Outlook. Doing so means Outlook takes ages to load and causes performance issues due to the sheer size of your document. Try splitting your list into smaller batches instead.

Can you copy and paste multiple email addresses in Outlook?

Yes, you absolutely can. However, remember that you cannot use wildcards or commas while doing so. Otherwise, you'll end up importing duplicate records.

Let's say you wanted to send out a welcome letter to everyone on your list except John Doe. Since his birthday falls between March 3rd and April 2nd, you wouldn't want him showing up twice under Birthday field. Fortunately, you can exclude specific items using exclusion formulas and wildcard characters.

For instance, here's what the formula looks like: =NOT(ISBLANK("Birthday"),EQ("*#",LEFT("Birthday","7"),"#"))

It checks whether someone exists in that particular area and, if he/she does, ensures that it matches a pattern beginning with * followed by six digits. It begins with % because otherwise it would match anything beginning with 0. Hope that helps!

https://vimeo.com/22913306



How do I copy email addresses from Excel to my email?

Microsoft says that Outlook supports 100,000 distinct email addresses. While that sounds impressive, it probably still pales in comparison to most companies. At least, this number gives us an idea about how many people we can fit into a single mailing list at once.

When building a list of thousands, it's important to know that Outlook keeps track of unique email IDs internally rather than full addresses. Therefore, you'll need to convert a long string of letters and numbers into something recognizable. Luckily, there are tools online that take care of this for you. Just search for "[email] lookup tool."

If you prefer working locally, you can skip steps one and two altogether and start inserting your Excel data straight into Outlook. Simply highlight the relevant section and drag it over to the corresponding spot in the ribbon dropdown menu. Right-click the item and select Insert Fields. Choose Text Box and proceed as usual.

Can you mail merge from Excel to Outlook?

Mail Merge allows you to combine data from multiple sources into a single document. As mentioned earlier, this feature is restricted depending on your plan or subscription. But if you're looking to automate your communications to a wide array of customers anyway, it shouldn't pose a problem.

Here's what you need to do: Open up your Excel sheet and insert your contacts as described above. Next, open Word and head to Ribbon " Design tab " Protect Document. Check Automatically apply Protection.... Switch it off and leave everything else alone for now.

Head back to Excel, insert your merged document, and activate Mailings Tab. Double-click the New Item button located beneath the title bar and select Blank Documents. Drag the newly inserted document over to the left sidebar, then expand Mailings Tab " Data Tools " Fill Handle Icon.

From the pop-up menu, select Mail Merge Wizard. Follow the prompts to complete setup, choosing whichever option suits your needs best. Once finished, save your project and close the program.

That's basically it. Take note of the fact that you can only utilize the mail merge function if you have Word installed. If not, you'll need to export your documents as DOCX files, rename them, and upload them to OneDrive. Then, download Word from desktop site, follow instructions listed on the help pages, and voilà.

Have something to add to this story? Share it in the comments.

If there's one thing that Office 365 excels at it’s creating and maintaining massive spreadsheets full of contacts. Contact groups, also known as distribution lists, are a fast way to send a message to multiple recipients, even if those people aren't all in the same department.

While most of us will never need to make our own mailing list for personal use, business users often have to maintain their own Excel-based contact management system (or worse). Luckily, Microsoft has put together some guides on how to build these contact lists using both VBA scripting and importing external CSV files, but what about simply building them directly within Outlook itself?

The good news is that while this isn't possible natively with standard tools like Data -& Create Mail Merge Contacts List, it can be done easily through a little bit of customization—and we'll show you exactly how to get started!

How do I create an email list from an Excel spreadsheet?

Before getting started, it makes sense to summarize the steps required to create a custom mail merge list in Outlook.

To begin, open up your desired destination file (.csv) containing all of your existing contact information. We're going to import this into a new blank contact list for Outlook so that we may then populate it with emails later. You should see something similar to the below image when opening your.csv file.

Select "Data" -& "From Text File..." under options. This step reads the text file contents and converts each row into individual columns. It does not automatically convert your first column, which contains email addresses, from plain text into actual email objects. If you'd prefer to skip this conversion process entirely, select the radio button next to "Skip First Row." Otherwise, click OK. Your file should now look something like this after converting your raw text file.

Next, double check your settings by clicking on the Edit Custom Lists... link in the upper right corner of your screen. Here you'll find two dropdown menus labeled Source Type & Destination Name. The source type menu determines where the names come from, whether they're imported manually or generated by another tool. Since we've already selected our converted.csv file, selecting "Excel Database" is safe here. Leave everything else defaulted unless otherwise specified. Click Save once again, and close out of the dialog box without closing any other tabs.

Now that your document is ready to go, it's time to actually fill it with email addresses. To start, head back over to the main Tools tab. From here, select Insert -& Field..." followed by Email Address. A window will pop up asking you to choose the field you want to add. Select the appropriate field based upon your needs. For example, if you plan to distribute an email invitation, you would likely include FullName rather than LastName. In general, you should try to avoid including Social Security Numbers and phone numbers until absolutely necessary. Once you've chosen your fields, hit Next.

Once finished, you should receive a confirmation stating that the email address was successfully added. Now, repeat this process for every single name listed in your.csv file. When adding more than 1 person per line, keep track of which fields correspond to which recipient(s), since each subsequent entry overwrites previous entries. Don't worry too much though, this won't cause problems down the road. After completing this step, you should end up with a table looking something like this.

It might take a few minutes to complete depending on how many records exist in your.csv file. While this method works great for smaller data sets, larger ones can require a slightly different approach. That said, you don't necessarily need to perform mass editing operations such as this on large datasets. Instead, consider exporting your data as a JSON file instead, then loading it into your preferred CRM solution.

How do I create a contact group distribution list from Excel data?

In order to better organize email invitations sent to several individuals who share common interests, you could try combining these separate distributions into a specific contact group. However, doing so requires creating additional rules for your merged list. Fortunately, because we've created our initial list via VBA automation, we can modify it further by modifying its code.

First things first, let's edit our existing Sub SendEmail function to accept additional parameters. Paste this piece of code somewhere inside VBA editor. Then, scroll down to the section marked // EDIT HERE comment and replace the entire block of code with this :

Sub SendMailWithCC() '//EDIT HERE

Dim xOutlook As Object

Set xOutlook = GetObject("outlook.Application")

xOutlook.SendUsingAccount="your_account@email.com"

'-- SET THE FROM ADDRESS AND SUBJECT --

xOutlook.From=Range("A1").Value

xOutlook.Subject="Your Subject Line"

End Sub

That's pretty simple stuff! All we did above is set the variables needed to properly configure the outgoing message. Before running this modified script however, you must save it as an application called MassEmails.exe. By default, Word documents cannot run macros, which prevents Visual Basic scripts from working correctly.

After saving your project as a standalone executable, launch Windows Task Scheduler. Under Actions, select Start a program.... Browse to wherever you saved your newly compiled exe file, and select Run as administrator. Hit OK, wait for completion, and you're done!

You can access your updated mass emailing utility by heading to My Computer -& Personal Folder -& Documents -& [My Document] folder -& Windows Scripts -& myMassEmailsScript.wsf. Inside this folder, rename the wscript.application file to whatever you'd like to call it. Place your original template file anywhere convenient. Finally, navigate to C:\Users\[Username]\AppData\Roaming\Microsoft\Office\15.0\Common\General\Dialer and remove the old template. Replace it with your renamed copy.

When launching your new automated system, you can change the number of simultaneous dialers by adjusting the variable DIALERS parameter. Simply adjust this value according to your preferences. Note that increasing this amount greatly increases processing times. Also note that you can only schedule tasks between certain hours, usually during nonbusiness hours.

To prevent errors due to incorrect formatting, ensure that you follow proper capitalization guidelines. Be sure to pay attention to punctuation and spacing as well. Remember that whenever you place quotation marks around strings, you must follow those quotes with white space. Spaces alone should suffice, although colons can sometimes confuse the macro. Lastly, remember that cell references used in formulas must always contain dollar signs ($$C2:$F5 refers to cells B3 to F7).

How do I Import multiple email addresses into Outlook?

One nice feature of the Exchange Web Services API is that it allows for bulk imports of email addresses. Although it doesn't support all languages, it supports English language characters. On top of this, it's compatible with older versions of Outlook, meaning you can still use it to import email addresses even if you upgraded long ago.

Before continuing, download EWS Explorer, an unofficial free alternative client for accessing Exchange services. Head over to http://ewsapi.codeplex.com/wikipage?title=Getting%20Started,%20section%2010.8.Importing%20Multiple%20Recipient%20Lists%20into%20OEMDIPOLES&referrer=http%3A%2F%2Felchemy.net%2Funofficial%2Febularized%2FEmail%2523Explorer%253Bv1.6%252CReleaseNotes.aspx#10.8.Importing%20multiple%20recipient%20lists%20into%20OEMDPoles%26rdquo%3Bid%255B%257D%253D2%253B4 to grab yourself a trial version.

Head to the bottom of the page and click View Code. Copy and paste the following into Notepad or wordpad.

Option Explicit

On Error Resume Next

Private Declare Function IEGetProperty _

Lib "USER32", Alias "InternetQueryStructW" () As Long

Public Const SPC_GETPROPSHEETINFOW As String = 0

Const WS_EXITWINDOWS As Integer = 16

Dim iCount As Integer

iCount = 6

Do Until IsEmpty(IEGetProperty())

Loop

sub test(){}

Next, load up the WSDL file provided earlier by EWS Explorer. Right-click on it and select Install ActiveX control. Navigate to your desktop and select Components -& References. Find and highlight System.WebExtensions.dll v9.0.0.0. Drag and drop it onto a clean sheet of paper. Open up the Imported Functions window. Scroll down until you see spc_getpropsheetcntowithindex(). Change the index part of the string to match the corresponding letter in your user account. Do likewise for the second and third indexes.

Microsoft's Office suite is powerful and packed with tools that make it easy for anyone to get work done on any device anywhere. However, there may be times when you need to use an older version of the software due to lack of access to newer programs (e.g., Word 2016). Thankfully, there are ways around this issue that don't involve using Google Docs! Mail Merge can help your team quickly produce professional-looking documents without having to worry about formatting issues. We'll show you how to use Mail Merge in Excel to send bulk messages via Outlook.

If you're new to Mail Merge, check out our guide here:

What Is Mail Merge?

Before we begin creating an email campaign, let us first explain what exactly Mail Merge is. This feature allows users to automate sending out letters, postcards, and other printed materials by importing data into a document and then automatically inserting names, addresses, phone numbers, etc. within specific fields. The result provides one single file containing all necessary information needed to print a letter or card. It also helps prevent errors that occur when manually entering large amounts of data.

Let's say you have a database full of customers' name, address, and telephone number. You want to write them a thank-you note each year during December as part of holiday season marketing efforts. Using the Mail Merge function will allow you to import that data directly into a preformatted template so that you won’t lose track of those important details while writing up individual notes.

Once imported, you simply type the body of the text inside the appropriate boxes before hitting “Send.” Then just select the mailing label which has been created specifically for the project. When you schedule delivery through USPS, they’ll take care of everything else.

We've outlined below some basic steps required if you'd like to try Mail Merge yourself. If you run into trouble, please consult official documentation from Microsoft.

How do I mail merge from Excel to email?

You should now understand why Mail Merge is such a useful tool. But how does it actually work? To start off, open up Excel and navigate to Data " From Text/From Spreadsheet… Click OK to continue.

In the next window, paste your contacts data into the spreadsheet field provided. Once complete, click Ok again. Now, look at the top row where you see Contact 1, 2, 3, 4, 5… These represent different labels used throughout the process. For example, these could be customer service reps, sales people, administrative assistants, interns, and more. Select whichever category best suits your needs.

Click Finish & Merge. A second later, you should receive a notification saying that the operation was completed successfully.

Now, return back to your original Excel sheet. You’ll notice that every cell under Label1 contains a hyperlink. Each time you refresh the page, it will update itself with whatever is entered in that particular column. In turn, whenever someone clicks on it, they’ll go to the relevant section on your main screen.

When ready, select the cells under Label2 that contain your content and copy them over to another worksheet. Right-click on the copied area and choose Paste Special... At the bottom left corner, scroll down until you find Pasting Options and select Plain text only. Hit enter, and you should end up with something similar to this:

This means that once pasted, the contents were stripped away from anything associated with visual elements. Next, head over to Gmail and sign in to your account. Go ahead and compose your email. After clicking Send, you should receive two separate alerts—one immediately after pressing Enter and another once you hit CTRL + S. That's because the latter activates the Mail Merge function.

After receiving both notifications, you can close the tab. Your message should appear on its own label.

But wait! There's still plenty of room for improvement. Let's tweak things further with the following tips.

Does Outlook support mail merge?

As previously mentioned, Mail Merge functions primarily rely on Excel. Therefore, many would assume that since Outlook doesn't come equipped with Excel, you cannot perform the same tasks. Fortunately, this isn't true. Here's how you can set up Mail Merge in Outlook.

First, log onto Outlook.com and launch the app. On the Ribbon, look for File menu and select Create New Email Message….

On the resulting pop-up window, switch to Home Tab followed by Layout View. Underneath Compose heading, click Insert Field....

Next, expand Labels Section and double-check whether Address Block appears. Once confirmed, drag and drop it onto the blank space above. Confirm that Name, Street Address, City, State, ZIP Code, Phone Number, Fax, and Website are present. Also ensure that Save As Draft option exists on the right side.

Lastly, locate the Labels Group located towards the center of the layout editor panel. Double-click on it. Inside, you should spot three sections labeled Header, Footer, and Content. Expand the header section and add your company logo underneath Customize Header Image.

To customize the footer, double-click on the corresponding box. Scroll down and delete the default text that reads Received [date], Subject line, and Dear Customer. Instead, replace it with your brand slogan, business hours, and legal disclosures.

Under Content, you’re able to include custom tabs based on your preferences. For instance, you might wish to insert additional links or images related to that particular client. Simply highlight the desired block and press Ctrl + C to cut it. Head over to Notepad or another suitable application and save the content separately. Finally, copy everything under Content and place it beneath Tabs -" General.

How do I send multiple emails from Excel to Outlook?

It’s possible to send multiple emails from Excel to Outlook but only if you export each message individually. While this approach comes with several benefits, including better organization, it takes longer than doing it all at once. So, let's walk through the procedure step-by-step.

Open Excel and navigate to Sheet1. Select entire range A1 to B5 and copy them to Clipboard. Then, head over to Outlook and open the respective tab. Press Alt+V to activate Visual Basic Editor. Type Dim MyMkEmail() = Array("") Line 1 and press Shift+Enter.

Press Ctrl + N to create a new module. Delete Line 6. Afterwards, paste the code snippet found under CurrentProject.VBProject.References---" References Manager.

Select AllItems from Project Explorer. Choose App.CommandBars(Application) CommandBars--" Controls. Find ToolbarControls command bar button and double-click it. Then, move your mouse pointer over Show / Hide Button Bar options. Look toward the far end of the list and you should spot MailMergeButton. Left-click on it. You should receive confirmation asking if you want to display it permanently. Click Yes.

Finally, double-click the button object to bring up properties menu. Change DisplayName property to Send Emails and change Caption to Multiple Messages. Apply changes and exit VBA editor. Return back to Excel and press ALT + V to execute newly coded macro. Notice that upon exiting Excel, the program will ask you whether you’d like to schedule execution or immediate processing. Select Schedule and enable Run task weekly.

The final product should resemble this image:

How do I do a mail merge in Microsoft Outlook?

For starters, if you ever encounter problems running the script, refer to troubleshooting tips for using macros in MS Access. Otherwise, here's a quick breakdown of the most common questions regarding performing mail merges. Feel free to reach out to us if you require assistance.



Are there limits to how much data I can put into my mail merge output?

Yes. Since the functionality relies heavily on Microsoft Access, the maximum limit is defined by the size of your system memory. Generally speaking, the bigger the capacity, the better results you’ll achieve.

Can I edit the mail merge output?

No. Changes made to the mail merge are limited solely to inputting new data. Any edits beyond that point will render the previous versions useless.

Is there anything special I need to keep in mind when working with mail merge files?

Yes. Always remember that the actual recipient's personal information must remain confidential. Never share your source material unless absolutely necessary. And lastly, always retain a backup copy of the finished product.

I'm not sure who all of my recipients will be. How do I build dynamic columns?

There are basically four approaches to achieving this goal:

Option One involves splitting your list according to their job title. Thus, you can assign each person a unique ID depending on his role.

Option Two requires assigning each entry in the table a sequential order. In this case, you can sort entries by their position and pull apart rows accordingly.

Option Three allows users to pick certain values from a given column instead of hardcoding them. By doing so, you can easily maintain consistency among recipients while providing flexibility for future updates.


Author

Anyleads

San Francisco

We are the leading marketing automation platform serving more than 100,000 businesses daily. We operate in 3 countries, based in San Francisco, New York, Paris & London.

Join Anyleads to generate leads

Error! Impossible to register please verify the fields or the account already exists.. Error, domain not allowed. Error, use a business email. Welcome to the Anyleads experience!
More than +200 features to generate leads
Register to start generating leads

Create your account and start your 7 day free trial!

Error! Impossible to register please verify the fields or the account already exists.. Error, domain not allowed. Error, use a business email. Welcome to the Anyleads experience! By registering you agree to the Terms and conditions agreement.
More than +200 features to generate leads

We offer multiple products for your lead generation, discover them below!

>> Unlimited access to all products with one single licensecheck our pricing.