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

How do I send bulk emails from Excel?

How do I send bulk emails from Excel?

You may not be aware that there are several ways you can use Microsoft Office's built-in tools to create and manage your mailing lists.  For example, if you want to send out newsletters or promotional letters on behalf of your company, then you should consider using either Word (which has its own Mailings tool) or Excel for this task.  But what about sending an email directly via Excel?  Well, it turns out that with some simple workarounds, you can easily import email addresses from Excel and use them as "To" fields when creating new messages through the same program!

Let’s take a look at how you can accomplish these tasks one by one.

How do I send an email to a list of email addresses in Excel?

If you only have a small number of people whom you'd like to email en masse, you might prefer to simply type their names manually. But let's say you've got a spreadsheet full of contacts' personal information. You could use Excel formulas to extract all those contact details -- including first name, last name, phone numbers, email address, etc. -- so they'll appear automatically as To: fields within each message. This way, you won't have to worry about typing everyone's individual email address over and over again.

Here's how to get started. First off, make sure that you're working with columns containing email addresses instead of whole rows since we will be filtering our data later on. Then go ahead and highlight everything you would like to include in the actual text of the email message. Now, select any cell within this range and head up to Home & Formulas & AutoFill. Click Conditional Formatting and choose New Rule... under Fill. In the next window, check the box labeled Set Value based on another value. Choose Email Address for Cell Reference and enter something such as =$A2," ",B1 where A2 contains all of the email addresses you wish to add. Finally, click OK.

Now every time you run a formula against cells in column B, those values will also be included as part of the body of the email. The trick here is that you must set the formatting rule to apply to just certain cells rather than the entire row because otherwise it will apply to other entries you didn't intend. When you're finished adding all of the recipient emails, press Ctrl + S to save the file. Next, open up your existing MS Word document and navigate down until you find the Create Source button on the right side pane. From there, locate the File menu and scroll down until you see Export Data as RTFD. Select that option and then hit Save As.... Enter a filename and give the resulting.rtfd file whatever descriptive title you desire (e.g., Emails). Lastly, click Open.

Your newly created source file should now show up underneath the main ribbon tab. Simply double-click on it to load it up into your default application. Once inside, you can edit the contents however you please but don't forget to add the necessary recipient information to the end of each line. For instance, my sample worksheet contained 52 lines which corresponded to different employees whose job titles appeared across the top while each entry was separated by a comma. So, after copying all of the data into a single string variable named strEmails, I added two spaces between each group of employee names followed by a colon and a space before entering the subject line. Here's what the final code looked like.

"Subject:" ""First Name Last Name"" Department ID Number EmployeeID Number

Finally, I used VBA to loop through all of the recipients listed in a separate sheet and output each person individually onto a new line. Take note that you cannot use quotation marks within your variables unless you preface them with single quotes. Therefore, I had to surround the entire block of code with single quote characters whenever I needed to insert special symbols or spaces. If anyone knows why that limitation exists, feel free to correct me in the comments section below!

How do I send an email to a list of recipients in Excel?

The process of sending a standard email from Excel isn't too difficult once you know how to format the text properly. However, if you wanted to skip straight to the point and avoid having to mess around with conditional formats, here's what you could try. Instead of selecting entire ranges of cells, you can actually build a formula that extracts specific parts of data and outputs them elsewhere. This method requires less editing effort and allows you to move more quickly. Let's assume that you already have a table filled with the names of various departments along with corresponding department IDs. What you want to do is input these values into a series of blank cells located somewhere else in your spreadsheet.

Head back to your original spreadsheets and identify the appropriate headers for each column. With a little bit of trial and error, you should be able to figure out exactly which ones contain the department names and which ones house the ID numbers. After identifying your header labels, highlight the rest of the relevant data and drag your mouse pointer over to wherever you'd like to place your extracted results. Pressing Shift+Ctrl simultaneously enables the Paste Special function and forces Excel to convert the selected area into plaintext. That means that anything copied will no longer be formatted according to the current selection.

Once pasted, you can adjust the font size and color as well as change the location of the result by dragging anywhere on the screen. Since the goal is to isolate each department heading, don't bother placing them close together. Also, remember that you can customize the destination range by clicking Insert and choosing Named Range. Type in whatever label you want for reference and then paste the department names into the field provided.

After completing both steps, we still have yet another problem to solve: How do we ensure that our extraction doesn't accidentally grab extra non-department related info? Well, the solution lies in building a custom function called ExtractDepartmentName(). We start by defining three local constants: One represents the starting index of the department names, one stands for the ending index, and the third signifies how many elements are stored per chunk of data. Right now, we're going to ignore the second parameter entirely.

Next, we declare a couple of temporary arrays and populate them with zeros: Dim arrStartIndex(0 To 1), arrEndIndex(0 To 0) and Dim lngChunkSize As Long. These arrays store the offset and length of each department name respectively. Our loop begins by checking whether the array locations are empty. If not, we initialize the count to zero and begin extracting chunks of data to fill in the gaps. Afterwards, we incrementally increase the counter by one. And finally, we repeat the procedure until the offsets reach the limits defined earlier.

When running ExtractDepartmentName(), make sure to modify the constant parameters accordingly depending upon how much data you plan to pull. In addition, you can tweak the function itself by changing the following line:



strOutput=Split(ActiveCell.Value, """")(intRowPos)"", 10)(LB_Offset)"",""&[10000]^

This piece of code essentially splits up the department name portion of the active cell's content into an array of strings. It does so by taking advantage of the fact that ActiveCell always refers to the currently selected cell regardless of what range you're trying to manipulate. Using Split() lets us break apart the long department names into smaller segments, making things easier to parse. Finally, the LB_Offset parameter takes care of moving forward ten positions whenever the string begins with a letter. Otherwise, it assumes that there's nothing beyond the very beginning of the string.

Can I Import a list of email addresses from Excel into Outlook?

As mentioned previously, importing an email address list from Excel to Outlook is quite straightforward thanks to the ability to export data as a Rich Text Document (.RTFD). All you need to do is launch the command prompt and navigate to C:/Documents and Settings/Username/Application Data/Microsoft/Outlook/Backup Files/. On Windows 7 machines, the Backup Files folder changes to Users\\AppData\Local\Packages\Microsoft.Office12.0_4EC8E9AE6AF94FFC8145622C73CD806982BE9345\LocalState\Cache\.

On older systems, you could just connect to \\ComputerName\c$\Users\Username\AppData\Local\Packages\Microsoft.OfficeXX.YY_XXXXXXX\LocalState\Cache where XX and YY represent the version numbers of whichever office suite you happen to be using. Anyway, once you're inside the cache directory, browse through the available files and folders until you spot a file titled rtfmwiz.bin. Double-click on it to bring up the Properties panel. Under Custom Views, tick Show Filter Field Options. Then, expand the Advanced category and scroll down until you come across the section labelled Add Other Fields. Hit the dropdown menu and select Unformatted Text Files. Browse through the options until you find the desired file and click Ok.

At this stage, Outlook should recognize the file as a rich text document and allow you to proceed with loading it up.

If you’re running your own business or if you work for someone who does, there are times when you have to send out hundreds of emails. You may be working with sales leads that require follow-up and maybe even sending marketing material such as newsletters. If you want to speed up this process for yourself, Microsoft offers an excellent tool in Excel called Mail Merge.

Mail merge allows users to create personalized letters, cards, postcards, flyers, brochures, etc., through data entry via a spreadsheet template. When using this feature, it's important to keep several things in mind including how many people will receive each letter/card, what information needs to go on them (name, address, phone number), and whether they should get a specific response. For example, perhaps you're trying to contact potential clients about a new product offering and would like them to sign up to learn more. This is where mail merge comes into play.

Here we'll show you how to use Excel to send bulk emails from within the program itself so no third party application is required. However, before getting started, make sure you've got all the necessary documents ready, which include a document containing names, addresses, and other details of those recipients, along with any related correspondence. We also encourage you to read our tips below before beginning.

How do I send multiple emails from Excel 365?

Before starting, ensure you have everything you need already prepared, i.e., a list of contacts' name, email address, and other relevant info such as their current job title, company, department, mailing address, etc. Also, remember to take note of the recipient's preferred method of communication—email, telephone, text message, etc.—and enter these into the appropriate fields in your spreadsheet.

Now let's get down to creating your master file. First, open up Excel and select Data " From Text. Then click OK to begin building your first row of data. Next, type in your column headers and then continue by typing in your data values. Finally, press Enter to complete your rows. Once finished, save your document as a.csv file and close it. Now, back at the main screen in Excel, choose File " Options " Customize Ribbon. Under Main Tabs, scroll down until you see Mailings & Mass Emails. Click Edit Rows... in the right pane and then proceed to add blank lines between existing ones. After doing this, highlight all of the cells under the New Row button and then press Ctrl + C to copy them over. Lastly, head back to the previous menu item and click Edit All Rows.... In the dropdown box next to Select Rows, pick All Cells. Press OK. The changes made above allow us to insert additional empty rows and columns, making room for our mail merge later.

Next, navigate to Home " Insert " Field...", located directly beneath Fields section heading. In here, find AutoFields. Scroll down to Column Information and expand the field titled Email Address. Right click its left side and go to Format Control Panel... " Formulas tab. Choose Send E-mail Message. A window will pop up asking for your subject line and body content. To set both, simply input your desired text and then hit Continue. Proceed with selecting your Recipients List (.csv) and double clicking the cell containing the email addresses of everyone you wish to target. Make sure to check off Include Delimiters and Set Default Value boxes. Hit Finish and return to the previous window. Here, input your email account settings (i.e., SMTP Server Name and Account Number). Upon successful connection, a green bar will appear underneath said settings.

Finally, let's say you only want certain individuals to receive certain messages. To accomplish this task, open your Master Document (.xlsx) and navigate to your chosen person's profile page. On the ribbon toolbar, look for Design Tab and then drag Selection Pane onto it. At the top of this new panel, locate the arrow icon and drag it downward to lower the selection area. Go ahead and place a dot in front of the selected individual(s) whose correspondence you intend to modify. With this done, go to View " Layout Inspector. Beneath Horizontal Scroll Bar, change Content Width to 100%. Do likewise for Vertical Scroll Bar.

Once completed, head back to the original Master Spreadsheet and repeat steps 1–4 but instead of dragging the arrow icon downwards, drag it upwards towards the uppermost part of the design area. Afterwards, just leave a space for the selection pane and move the horizontal scroll bar upwards as well. Repeat this step for every person whose correspondence you'd like to edit.

Lastly, to actually initiate the mail merge, go back to the Design Window and select Tools. Find More Commands and double click Create Lettersheet... In here, you can customize the layout of your lettersheets depending on your intended purpose, e.g., greeting card, invitation, newsletter, flyer, etc. It's best practice to start with basic options and adjust accordingly. Once satisfied, right click anywhere inside the design window and choose Save As Template. Give it a meaningful name, e.g., Invoices, and then save it to OneDrive for Business. Remember to always try and limit the amount of templates saved locally on your computer because they tend to slow performance.

How do I send bulk emails from Office 365?

To avoid confusion, if you don't have access to Exchange Online or Outlook 365, please skip sections 2 and 3. But if you are able to connect to either service, feel free to explore the following guide.

For starters, in order to run our mail merge, you must have two things in place: Access Database Files and Forms Services. Since most businesses likely won't use Access Database files anymore due to security reasons, now's a good time to transition away from these older formats. Instead, why not consider switching to newer alternatives such as SQL Server Analysis Services or Power BI Desktop? These platforms offer enhanced functionality while maintaining compatibility with various apps. If anything goes wrong during conversion, you can always revert back to old versions without losing progress.

Next, launch SharePoint Designer. Head to Lists | Library " My Documents " Lists " YourListName. There, you should see a folder labeled Templates. Double click one named CorrespondenceLetterTemplate. Inside, you'll notice three icons: Blank Letter, Letter, and PostCard. Drag the latter option onto the desktop, rename it something useful, and then delete the others. Next, go to File " Open " Browse and search for your converted template. Select the corresponding file and then double click it. By default, Word will detect whether or not the document contains form elements and automatically populate the correct labels for them. Otherwise, turn off this setting by going to File " Options " General. Uncheck Show Form Labels. Return to the previous menu item and click Preview. This way, whenever anyone opens up the newly created letter, they'll immediately know how to fill it out.

How do I send a mass email attachment from Excel?

When sending attachments, you might come across different error codes depending on the device you attempt to attach it too. Therefore, it's recommended to test your mass email first on another machine to prevent further complications. Also, it's worth noting that some devices might block images from being sent via email. Thus, it's advisable to convert your image to PDF format beforehand.

Let's start with testing your email. Launch Gmail Studio, log into your Google account, and then write out your email. Below, you should see a small preview of your email. Simply tap the blue arrows to enlarge the contents. Should you encounter issues, try again on a separate laptop or mobile.

Alright, now that we've tested our email, let's put together our actual letter. Start by opening up Excel and navigating to Data " From Text. Input your header information and then build your rows of data, exactly as described earlier in this article. Once built, save the document as a.csv and close it.

Heading back to Gmail Studio, navigate to Settings " Labs. Look for Enable Attachment Blocker and switch it on. Next, go to Settings " Filters and Blocked Addresses and disable Images. Now, let's bring our document together. Head back to Excel and navigate to Home " Insert " Form...". Within the subsequent window, browse to your previously uploaded template. Pick Embed Into Another Application and select Other Programs... You should now be presented with a variety of tools, namely Internet Explorer, Windows Media Player, QuickTime, Adobe Flash Professional CS5, and Shockwave Flash Object. Locate the last option listed, Shockwave Flash Object, and then double click it. A dialogue box will pop up asking you to confirm installation. Agree to proceed and wait for completion.

Microsoft Outlook makes it easy for anyone with access to their data to compose nice looking newsletters. And while there are many great applications that allow users to send customized emails, they often require a paid subscription or some degree of technical expertise.  If you run your own newsletter then this may not be feasible but if you have a large mailing list (or even just one person on that list) then running a mail-merge from Excel will make sending out those mass emails much easier.

In this article we’ll explore how to use Excel to create professional-looking newsletters that people actually want to read -- or at least open! We'll also look into using VBA scripts to automate our process further so that you don't have to worry about manually adding each recipient's address. Let's get started...

How do I send a personalized mass email to a list from Excel to Gmail?

There are two ways to go about creating a mailing list in Excel. In my example, I am going to walk through using the second method which involves copying all the values from cells A2 - E9 in Sheet1 (which contains names and email addresses), pasting them into sheet2 (the "Mass Mail" sheet), sorting them by name, and finally writing a macro that sends emails to each entry in turn.

The first step is to import your desired contacts into Sheet 2. Open up the spreadsheet you wish to fill with recipients. Use Ctrl+C to select everything within the range A2:E9. Head over to Home > Paste Special > Pasting Range as Values instead of Text. You should see something similar to below.

Next, head back to Sheet 1 where you just imported your contact information. Select all entries within the same range again, this time choosing Insert> Table. This command creates an object called table1 which allows us to easily reference specific rows from within our worksheet.

Now take note of what column number your "Name" fields start at. For instance, mine starts at Column B. Now, let’s add another column next to it containing these columns' numerical location. To do this, right click on the Name column header and choose Format Cells. Then, under Category, change the format type from General to Number. Finally, enter 0 in the Custom category field.

You now have a row for every name that appears in your spreadsheet. Next, head over to Sheet 2 and insert a new tab. Right click on the newly created Tab heading and chose Rename Sheets. Give your new tab the title “Recipients".

Head back to Sheet1 and insert a third column between the previous ones. Choose Formulas > Defined Names and rename the cell H4 as “StartRow", meaning the starting point for your recipient numbers.

Finally, insert a fourth column after Startrow and before End Row. Enter “EndColumn” here since end column refers to the last numeric digit used when referencing recipients. So in my case, because the final recipient has a 9 following his/her unique ID, I would set the value of EndColumn to 8.

With this done, we're ready to write the code to send emails to everyone on our list. The script will automatically generate individualized messages based on the information contained in our MassMailingsSheet.xlsm file. It uses variables defined earlier in the code to populate the message body and subject line.

This part gets pretty tricky, so you might find yourself needing to refer to Google Drive frequently during coding sessions. Once you've got the hang of it though, it shouldn't take too long to master. Here's the basic structure:

Dim i As Integer

For Each Cell In.Range("A3","B8") 'Change ranges accordingly depending on your actual spreadsheets.

Set oApp = CreateObject("WScript.Shell").Run """" & Chr(34) & Wscript.Arguments.Item(0) & Chr(34) & """"""

Do While True : With oExecute : Set fso=CreateObject("wscript.filesystemobject") On Error Resume Next Set tsfile=fso.GetFile(ActiveWorkbook.Path + ActiveWorkbook.Worksheets("Summary").Range("H7")) If Not tsfile Is Nothing Then DoWhile tsfile.AtomIsOpen() Loop Until tsfile.AtomClosed(True): Sleep 100 Set tsfile=Nothing End With : Dim strBody As String Dim colItems As Variant Dim intCount As Integer Dim sKeyword As String Dim dtTemp As Scripting.Dictionary Dim arrSubjLine As Variant Dim x As Long Dim y As Byte intIndex = 3 intCount = UBound(colItems, 1) If intCount = 0 Then Exit Sub MsgBox ("No items found!") Else : ReDim arrSubjLine(intCount) arrSubjLine(0) = Cells(i, 11).Value arrSubjLine(1) = Cells(i, 12).Value arrSubjLine(2) = Cells(i, 13).Value arrSubjLine(3) = Cells(i, 14).Value arrSubjLine(4) = Cells(i, 15).Value For j = 1 To intCount Step 1 : If Cells(i, 4) Like "[^"]*@[^"]*$" Or _

Not Cells(i, 5) Like "[^"]*@[^"]*$" Then strBody=""Else : strBody="Subject:"& Cells(i, 10).Value& vbCrLF& Subject_line& vbTab& Cells(i, 7).Value& vbCrLF& Message_body" : SendKeys strBody, False : Next : Next : intLoopCounter = intLoopCounter + 1 Wend : Application.Quit SaveChanges = True : End With : Set oWSManage = GetObject("winmgmts:{impersonationLevel=immediate}");Set collItems = oWSManage.ExecQuery _ "(select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE)" Set wshNetAdptColItems = collItems Set objWMIService = GetObject("winmgmts:\\" & "_oot\cimv2") Set colSites = objWMIService.getArrayOf(ByRef wshNetAdptColItems) For Each Site In colSites Set cmdSite = objWMIService.execCommand("AddPortMapping", Int16(63692)) cmdSite.WaitUntilComplete(-1) Next : wshNetAdptColItems.RemoveAll Items objWMIService.Close Set objWMIService = Nothing Set oWSManage = Nothing Set collItems = Nothing : End Function

Here's a breakdown of what most things mean:

We begin defining our loop variable in quotes. Since our code references other sheets rather than active workbooks, we need to concatenate them together using Chr(34) along with arguments array item zero. We then tell Windows Shell to Run our program using Chr(34) & argument(0). Doing this lets our computer know that whatever follows must be executed inside its internal scripting environment.

Then we declare a few objects including: Workbook Object, FileSystem Object, WMI Service Object, etc. These are necessary to perform certain tasks like getting files off of disk, finding network adapters, accessing windows services, and more.

Since our recipient information comes directly from the worksheet, we never really need to explicitly call them. Instead, we simply reference the entire range of cells containing recipients. All of this information goes into arrays called ColItems.

Lastly, we define a couple functions. One function takes care of generating the message body, and the other generates the subheading that precedes said body. The important thing to remember is that both functions rely on variables that were previously declared. At no point does our macro ask user input. This means that once you save your document, any changes made to the code won't cause errors. There are plenty of places where you could modify the code however that aren't dependent upon user inputs.

Let's say you wanted to include additional information into the subheadings such as company name, phone number, or date. Your code already knows how to pull this info down, but if you didn't originally plan on doing so, you'd have to update your macros manually. That wouldn't happen if you saved your original document as well.

After saving, press Alt-F11 simultaneously. This opens up Visual Basic editor. Type the code shown above into a module and hit F5 to test. If nothing happens, try removing your sleep commands. They probably caused errors somewhere else in the script. Also, check to ensure that you closed all instances of WSHShell correctly.

Finally, double check for syntax errors. Press Shift-Enter to auto indent text. Make sure to keep tabs properly aligned. Go ahead and delete the rest of the code in the main routine and replace it with the code above. Hit F5 again. Hopefully, it worked.



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.