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

How do I turn an Excel spreadsheet into an email list?



How do I turn an Excel spreadsheet into an email list?


Microsoft Outlook has always been my go-to application for managing emails, but it's not particularly good at keeping track of multiple lists -- especially if your goal is to use one program to manage all those lists. Fortunately, there are many other excellent free programs that can help with this task, including several popular webmail clients like Gmail, Yahoo!Mail, AOL (AOL Web) and MSN Hotmail. The following article will provide some useful tips on how to export data from an excel spreadsheet in order to convert them into a mailing list.

For example, let’s say we have a sales report for our company that contains information about each of our customers' products purchased over the past year. We now want to send out regular updates regarding new product releases, special offers and promotions to only those specific customers who bought any of these items during the last month. This would mean creating two separate mailings – one for everyone except those few people who didn't purchase anything last month, and another for just those particular customers. The best way around this problem is to simply put both groups together into a single mailing list so you can easily update their profiles later without having to sort through hundreds of names every time. You could also consider using VB scripts to automate sending out mass emails by referencing certain cell values within a worksheet. For more details, see Matt Smith's helpful tutorial here.

In addition to exporting individual columns as described below, you might also wish to save entire workbooks containing large amounts of related data so they can be shared among team members. A common example of when this occurs involves sending monthly status reports to department managers, where various employees add their own personal comments, attachments and additional data. In general, it doesn’t take much effort to set up a simple database to store such files online and share them with colleagues via SharePoint, Google Docs or similar file sharing tools. Alternatively, you might even choose to build custom forms instead of spreadsheets to collect relevant info, which allows users to quickly fill out required fields rather than inputting thousands of rows manually. By doing this, you'll end up saving yourself hours of typing time while providing better functionality for the same amount of work.

Before getting started, however, you should know what kind of output format you need before proceeding further. Some popular formats include CSV (comma separated value), TXT (tabular text), HTML (hypertext markup language) and XML (extensible Markup Language). To find out whether the type of file you're building needs to follow any particular standard, try opening the.xlsx file directly in Notepad or WordPad after downloading it. If everything looks fine, then proceed onto the steps listed below.

How do I make an Excel spreadsheet into a mailing list?

Once you download and open the.xlsx file, you’ll notice that the document consists of three tabs labeled Sheet1 (or sometimes Worksheets!), Header 1 and Footer 1. These tabs contain formatting instructions and guidelines for the rest of the sheet(s). Each tab includes the name of the corresponding sheet along with its column headers and row numbers. So, for instance, the “Sheet1" header provides important information like sheet title, number of total lines, current line number, etc. Next, copy the contents of each "Header 1" section down until you reach the bottom of the page. Then paste them above the content of the current "Footer 1". Be sure to leave enough room between the footer and top of the screen since the final results won’t show up correctly otherwise. Now repeat the process for Tab 2, copying portions of each "header", and placing them above the "footer" area. Finally, scroll down to the very bottom of the sheet and paste the remaining parts of the "footer" under the previous footer. Repeat this process once again for Tab 3. Once finished, close the XLSX file and rename the resulting.csv file accordingly.

You should receive a message saying that changes were saved successfully. At this point, you’re left with a.csv file filled with customer records formatted according to the desired specifications. It’s time to get rid of unnecessary elements like empty spaces, duplicate entries and extra characters. To remove unwanted blank space and commas, head back to Windows Explorer and select Tools -" Text Import Wizard... From the dropdown menu underneath File Type:, change Delimited (.txt,.csv,.unix) Options… to Comma Separated Value (.csv). Click OK to continue. On the next window titled Choose Source Editor…. Select Advanced Editing Mode. Check Delete Row Spaces box, uncheck Ignore Line Feed Characters checkbox and click Finish. Lastly, open the newly created.CSV file in the Text Edit editor and edit wherever needed. Save the modified file and quit the app. That’s it. Your converted.csv file should look something like this:

First Name Last Name Email Address Phone Number Comments CustomerName1 FirstLastName@emailaddress.com 123456789CustomerEmailAddress CustomerPhoneNumber

Now that you have a working conversion method, you can start importing data from existing Excel spreadsheets. But first, let's discuss how to export Excel data to avoid errors.

How do I Export a contact list from a spreadsheet?

It’s possible to transfer data from Excel sheets to comma delimited text files (also known as csv) format using either manual methods or automated software solutions. Here are quick ways to do this:

Using Copy & Paste: Simply highlight the cells you want to move and right-click. Go to " Format Cells.... Under Category, click the Alignment button. Scroll down and pick Custom Size. Enter width dimensions for each field you want to preserve and press OK. Left justify all fields except phone number. Right justify the latter.

Using AutoFill: Highlight the range of cells you want to extract. Head over to Home / Styles / Conditional Formating and tick Use relative references (recommended). Press Ok. Then, select Formulas option from the right side panel and locate Send to Spreadsheet icon. Drag it across the highlighted range.

Using Command Button: Create a command button inside the selected range. Hold down Alt key and drag it across the highlighted range. After releasing the mouse, release the Shift key. When inserted, the button will automatically populate the selected cells with formulas based on conditional formatting rules. Double clicking the button will execute those commands.

By pressing Ctrl + C and selecting All Commands..., you can access previously created command buttons. Open the properties dialog box for the command button. There, switch to General category. Change Action property to Macro. Hit OK.

This macro inserts the copied formula into the appropriate cells. Do note that you must enable Developer mode prior to running macros. Otherwise, you’d encounter runtime error 438.

Alternatively, you can opt to run this script via the VBA programming tool included in Office 2010 Professional edition. Refer to David Giesbrecht's blog post here for detailed instructions.

To summarize, the main advantages offered by converting your Excel data into a CSV file is that it’s easy to read and search. Also, thanks to the inclusion of proper spacing, sorting options and optional comment sections, you’ll no longer have to worry about accidentally deleting critical information because of careless editing. And unlike plain texts, CSV files don’t require heavy processing power from computers such as servers. Instead, they load faster because they utilize local storage instead of network bandwidth. Another advantage of CSV is that you can manipulate the data using different softwares like Word, Access and others. One caveat though - although CSV files are great for transferring bulk text data, they aren't suitable for images unless you perform image modifications beforehand.



Can I import contacts from Excel?

Yes, you can. Just keep in mind that it requires installing third party software. As far as I know, there are several reliable apps available that offer this feature. One notable solution is ContactManager Pro [Broken URL Removed] ($49.95 USD), which lets you import/export contacts from/to a variety of sources. The app can also generate professional looking newsletters with ease.

Another potential choice is Smartr Contacts Manager Free Edition ($19.99 USD). Unfortunately, I wasn't able to test this one due to technical issues involving installation and compatibility issues. Regardless, I'm confident it does a good job given its popularity.

If none of these satisfied you, refer to this guide detailing how to merge and consolidate Excel contacts. It takes less than five minutes to complete. Don't forget to backup your original.xlsx file somewhere safe.

How do I create an email group from Excel?

­If you're like many small business owners, your biggest challenge is coming up with new ways of communicating with customers. Email marketing can help boost sales and strengthen relationships between businesses and their clients. But how do you take that one-to-one communication to thousands at once? You need a database -- but how do you get it without spending hundreds on software?

Capturing and organizing customer or prospect email addresses is the first basic step toward compiling a mailing list. The best way to accomplish this task is by using a program such as Microsoft Word for Mac OS X (or Windows) or Google Contacts. Both programs are free and provide easy access to multiple contacts via various methods. They also have extensive formatting options and tools that make them perfect for creating lists. In addition, they both offer integration with popular e-mail providers including Yahoo! Mail and Gmail.

Once you have all those names stored in a Word document or another type of data storage system, move on to the section below where we'll discuss what steps to take when converting that information into an email list. This will give you a great start toward building out your own personalized e-newsletter.

But let's say you want to use Excel instead of either Word or Google Contacts. How would you go about turning a simple list into an email newsletter? Keep reading to find out.

How do I convert an Excel list to an email list?

There are several things you'll need to consider before starting any conversion project. First off, which version of Excel should you work with? While older versions were built for spreadsheets only, newer ones include more features for analyzing and manipulating large amounts of data. For example, if you choose Excel 2007, you can store your contacts within the same application rather than having to download each individual address separately [source].

In general, stick with Excel 2003 or better. It doesn't matter whether you're working alone or with other people who might not be familiar with Office programming languages. As long as everyone has Excel installed, there shouldn't be much trouble getting the job done.

Next, decide exactly what you plan to do with the converted data. Do you just want to send bulk emails through Outlook Express? Or perhaps you'd prefer to add it to your existing mailing list so you can keep track of future orders? Whatever you end up doing, follow these guidelines to ensure success:

Keep everything organized. Use tabs to separate similar items, such as "contacts" and "orders." This makes it easier to locate particular groups later. Also, don't forget to name columns and rows properly. Naming one row "Joe Smith" won't cut it. Be sure to label every field appropriately so users know exactly what kind of item they're looking at.

Use consistent formats. Avoid different fonts or colors unless absolutely necessary. Make sure dates and times are formatted consistently.

Don't overdo graphics. Simple charts and icons are usually sufficient. A single image per page will save time during printing. Just remember that images must be less than 1 MB in size.

Avoid complicated formulas. Unless you really understand Excel functions, avoid complex calculations. Instead, look for simpler alternatives.

Make notes. Before beginning any conversions, write down important details regarding the source documents, such as column headers, sheet titles and table numbers.

Before proceeding with the actual conversion process, check out the next page for some tips that will help speed along the entire process.

The fastest method for saving an Excel file as text is to right click on the desired cell and select Text Import Wizard. Alternatively, press Ctrl + T on the keyboard. Select Create Separate Contact Record(s), then browse to the appropriate folder location. Click Next until you reach the final screen, where you can rename the fields according to the correct format. From here, simply click Finish & Close.

Now that you're ready to begin converting your Excel file to a text file, read on to learn how to convert an Excel file to a contact list.



How do I convert an Excel file to a contact list?

After downloading your converted Excel file, open it in MS Word. Now, head back to Excel and use the Find function to search for certain letters. Highlight the letter you wish to extract, then highlight the adjacent cells containing the remaining contents. Press F3 on your keyboard while holding CTRL. Your highlighted area now contains the selected letter. Release F3 and hold SHIFT. Repeat this action across the bottom of the selection. When finished, release Shift. At this point, the rest of the words in the range will contain nothing except for spaces. Delete the extra characters by selecting the word and pressing Backspace.

You can repeat this action throughout the document to pull specific entries from larger tables. Once again, highlight the relevant areas in Word and press F3 on your keyboard. Hold SHIFT and repeat the above actions. After copying the last part of the entry, delete the unwanted portion of the string with Backspace. Continue this procedure until the whole thing is extracted. Then, copy and paste the information into a new tab in Word. Rename that tab according to your preference.

With the number of contacts saved in a Word file decreasing rapidly, you probably don't want to print out huge sheets full of names. Luckily, there are plenty of online resources available for sending bulk emails. Read on to discover how to convert an Excel file to a mail merge list.

To convert a CSV file to a vCard, install Apple iWork Pages 8.5.1. Open the resulting menu bar icon and select Tools " Export To Other Apps... Choose Address Book. Follow the prompts to complete the export. Note that not all of the fields included in your original CSV will appear due to limitations imposed by Apple.

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

One of the easiest ways to compile a mass email list is to import the addresses directly into Outlook. By default, however, there isn't a convenient button located on the ribbon to perform this action. Fortunately, there are two solutions. One involves adding a macro to the VBA editor. Another requires installing additional software. We recommend trying Microsoft AutoHotkey first. Once downloaded, double-click Autohotkey.ahk. Within its window, scroll down to the line labeled #InputSender_Start() and insert the following code :

^q::Send {Print}

This command sends whatever the user types immediately after ^q to the Print statement. The Send keyword ensures that anything entered gets printed. To test it, try typing something unrelated followed by Enter. Notice how the cursor automatically jumps to the next line. This works similarly to auto responders found in Internet chat rooms.

Microsoft AutoHotKey is a powerful tool capable of performing a variety of tasks related to computer interaction. Download instructions vary depending upon operating systems, so be sure to consult the installation guide. Once you run the script, you'll see a message pop up asking whether you want to enable macros. Confirm yes and restart Outlook.

Head back to Excel and navigate to Home " Insert " Quick Part Gallery. Here, drag the AutoHotkey Script Toolbox onto the Ribbon. Double-click on the box and select Run Macro.

Right-click anywhere on your desktop and select New " Shortcut. Type cmd/cntm/path in the Location box. Replace cntm with Control Panel and path with outlook.exe. Finally, hit OK. Right-click on the shortcut and select Properties. Head to Target, click Edit, change Path to C:\Program Files\Common Files\microsoft shared\OFFICE12\OUTLOOK.EXE /run.bat, then hit OK.

Copy and paste the following lines into the Command Line input box:

@Run @="C:\Program Files\Common Files\microsoft shared\OFFICE12\WPSOutlookTools.dll,-2095″

send,t

pause %A_Hour%h –f "[FNAME]" '[DATE]\'[TIME]' '{GOTO}'

When prompted, replace [FNAME], [DATE] and [TIME] with your preferred values. Leave the Goto variable blank. Hit Save, close the registry editor, reopen Outlook and open the Immediate Window. There, type!autoreponse -r DSNAME and press RETURN.

Press Return again to execute autoresponders.com and confirm the settings. Wait five minutes and close Outlook. Paste the commands into a batch file titled Batchfile.cmd. Change the extension to.BAT. Copy and paste the output from Notepad (.log) into the body of the log file. Place the completed file under C:/Users/Your User Name/AppData/Roaming/.Autotext/AutoText/Scripts/.

By clicking the Autoresponder link in Outlook, you can customize the subject header, recipient messages and even add attachments. With this option, you can easily set up automated responses that can be scheduled by day, week or month.

For more ideas on automating your inbox, explore our article on how to automate email replies.

­How often have you received a series of emails about something that were all sent by one person on your contact list? How would it feel if you could send out those same messages without having to sift through hundreds of names yourself?

It's possible with Microsoft Excel, which has the ability to convert data into multiple formats -- including HTML for sending via e-mail. This article will show how to use this feature to convert data into an email list format. We'll also show you how to take advantage of it to automatically distribute information such as newsletters and product updates.

First we need to set up our data. For this example, let's say we're working at a company called "Company X." Our goal is to get every employee in Company X to receive a newsletter once per month but not necessarily each day. The employees are already listed alphabetically so we know who should go where. For instance, Bill Smith belongs under A because his full name starts with B. With this knowledge, we can easily put together a table to make sure everyone gets what they want.

Here's how we'd do it using Excel (we'll explain more later):

1) Create two worksheets: One named "MailingList" and another titled "Employees." On the Employees sheet, enter the names of all current employees below their corresponding letters. You might even consider putting them into separate columns instead of rows. It doesn't really matter since we only care about getting the order correct when creating the final document.

2) Select all cells within the range of names you just entered on the Employees sheet. Make sure there aren't any empty rows above these people on the list. Now right-click anywhere inside the selection area and select Copy. Go back over to the Employees sheet and click somewhere else on the sheet. Then select Paste. All of the selected cells should now appear beneath the letter column.

3) Right-click again on any cell in the row containing your new employees' names. Choose Format Cells... From the menu options. In the dialog box that opens, change the Type field to Text. Click OK. Repeat this process for any other cell ranges containing additional employees' names. When done, ensure that everything looks good in terms of spacing between names, etc., before continuing onto Step 4).

4) Open up Accessibility Options by clicking the down arrow at the bottom left corner of the screen. Once open, drag the rectangle icon near the middle of the window to expand the section labeled General Controls. Scroll down until you see Table Tools and then double-click it. In the resulting pop-up window, choose Insert - Tables.

5) Enter a title for your new table in the top row labeled Table 1. Change the width settings of both the Name and Index fields to AutoFit Contents. Under Rows, input 3 and leave RowSpacing at 0.00%. Highlight the entire Table 1 header row and then press Ctrl + C to copy it. Head back to the previous steps and repeat Steps 2 & 3 for three more tables. Each time, label them Table 1, Table 2, and Table 3 respectively.

6) Return to Accessibility Settings. Drag the checkbox next to WordArt Styles to enable it. Next, head to Borders and Background Effects. Here you can either draw borders around individual tables or apply effects like Fill Color, Line Style, Transparency Level, Shadow Depth, and Drop Cap Height.

7) To finish off the design, return to Accessibility Settings. Near the center of the page, scroll down to Cell Link Behavior and change it to No Follow Hyperlink. At the bottom of the dialog box, uncheck Enable Web Parts. Finally, change Page Breaks to After Specified Number of Rows. Hit Close and save changes.

Now here comes the tricky part. We still have a lot of work ahead of us. First we must build a template that tells Office 365 exactly what kind of file we wish to produce. Let's start by building a simple table using the Employee Sheet. Take note of the formatting -- we'll use it throughout the rest of the project.

On the main screen of the Home tab, switch to Design Mode. Double-click on the table on the left side of the application window. Put your cursor in the upper left hand portion of the text box and type =Sheet("Employee"). Press Enter and keep typing =Sheet("..."), adding each subsequent sheet after the last. Don't forget to replace the ".xlsm" extension with whatever yours is.

Next, place your cursor in the lower right corner of the text box and hit F9. Your newly created table should populate the blank space on the form. Before closing out of this view, rename your table to Newsletter_Template.

To prepare our completed version, close out of Design View and go to File - Save As.... Browse to your desktop folder and navigate to My Documents\Office 2010\Templates. Use New Document to continue to create a new file. Give it any descriptive name you desire.

In the Ribbon, locate the Quick Part Gallery button. Expand the category labeled Forms. Within the subcategory, find Form Control Groups. Find the control group labelled Email Lists and double-click its entry.

This will bring up a new window. Click on the dropdown menu beside Build Action and select Macro-Enabled Workbook. Ensure that Include macro security warning is checked. Set Run command to Application. Then, in the Properties dialogue box that appears, click Add Code.

Enter the following code into the VBA editor pane: Sub SendEmail() Dim myMail As Object Dim iRowCount As Integer Dim iColourRange As Range Dim oWordApp As Word.Application Dim oDoc As MSForms.DataObject Dim strBody As String Dim intSubjectLenght As Integer 'Set colour range Dim rng As Range Dim objbody As MSForms.TextStream dim subjectlenght=20 dim wordapp objword=application.run('microsoftword',,true)'set object variable strbody="Dear [firstname], thank you for subscribing to our monthly newsletter! Keep reading for details!"'set colour range Set rng = Nothing Set ColourRange = WorksheetFunction.Match(Cells(iRow, 5), Columns("A"), 0) intSubjLength = Len(rng) Do While intSubjLength > 0 'get length of subject line while loop continues Until intSubjLength = 0 'while loop ends intSubjectLenght = 20 'define lenght of subject line Loop intSubjectLenght = intSubjectLenght Mod 60 'calculate hours intSubjLength = intSubjectLenght / 36 'divide number of characters by 24 'convert characters to minutes intMinutes = intSubjectLenght \ 24'multiply minues by 1440'substract 12 weeks from total email count 'oWordApp.Visible='True' 'open Word doc'strBody="" Dear [firstname]",""""'subjectlenght,"""&[coloumnB arrray(0)"','"""""""intMinute,'""") """[content]"",'"""""""strBody,'"""""""""subjectlenght,"""""""""colorrange),"""""""""colorrange,'"""""""""rowcount,"""""""""arraysubjectlength,"""""""""emailaddress."""""""'. "'""""""End If ElseIf ColoursAreNotDividedThen Exit Sub End If end if statement End Sub

Press Alt+F11 to run the script. The program will ask whether you want to run the script immediately or add it to the QuickPart gallery. Since we don't yet own access to the mail system, we need to wait for the moment. Upon running, you'll probably receive error message saying "Compile Error: Expected Statement" referring to the "end if" statements. Simply modify the code accordingly. Try to avoid making too many modifications, however.

Once finished, delete the original VBA code and insert your modified code directly underneath the existing comments. Then, remove the comment marks at the beginning of the code and replace them with #REMARKS. Lastly, select the whole thing and press F8 until the Visual Basic Editor pops up. Finish editing the properties, changing the references, and saving the workbook.

When finished, save your document to the Templates directory and give it a memorable name. Next, we'll import the Excel files containing the lists of employees. By doing so, we can automate the export/import process.

Go to Start | Account Setup | Import and Export Wizard.  Click on SharePoint and follow the prompts to complete setup.

Back to the previous task, we'll begin importing. Switch to Design Mode and click on the large blue circle next to the imported table. This represents a link to the source workbook. Click on Source Path. Navigate to your Desktop and find your recently saved Newsletter Template document.


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.