How to Send Bulk EMails From Excel VBA in One Click in Excel

Ever got a need to send email from excel VBA to multiple email ids, provided in an excel sheet? Want to learn how to send mails from Excel without opening outlook? This article covers, how you can automate outlook and send multiple emails with attachments from Excel in one click, keeping people in Cc and Bcc too.

In the end, you can download the file too, for sending males instantly to hundreds of ids. The file contains macro to send email from excel.

How to send mail from Excel?
To send mail from Excel, you need to understand these VBA snippets.

Important: You must have an email configured on Outlook Application.

Press F11 to open VB Editor.

Add a reference to outlook object library:

- - Go to Tools in the menu, and click on References.

- Find Microsoft 16.0 Object Library. The version can be different. It's 16.0 in Excel 2016. Checkmark it. And Click OK.

- Create References of Outlook Application and Mail: Now to be able to access features of Microsoft Outlook, we need to create its object.

Dim outApp As Outlook.Application 'This will create a reference to outlook object.
Dim outApp As Outlook.MailItem  'This will create a reference to MailItem.

- Initialize the references: The above references need to initialize:

Set outApp = New Outlook.Application
Set outMail = outApp.CreateItem(0)

- Sending Mail using outMail object: Below lines will define the target mail id, cc, bcc, subject, body, attachment and send command.

With outMail
.To = "abcd.mail.com" 'Mandatory. Here you define the destination mail id.
.cc = "cc.mail.com"  'optional. Cc mail id if you want.
.BCC = "bcc.mail.com" 'optional. Bcc mail id if you want.
.Subject = subj 'should have. The massage on mail body.
.Body = msg  'optional. The massage on mail body.
.Attachments.Add "C:/exceltip.com\test.xlsx" 'optional. Fully qualified name of attachment.
.Send 'Mandatory if you want to send mail from excel. You you want to see the mail in outlook, use .Display method.
End With

That's it. Its all you need to send mail from excel using VBA.

Now let's see an example that sends mail to each email id in an Excel column separately.

Scenario:
Imagine, you are provided with an excel sheet that contains 100 email ids, and you need to send an email to each email id separately. Not only this, you need to attach items too. This would take you hours if you do it manually. So let's automate this mailing task in Excel VBA.

Here I have this data. In column C, titled "Send Mail To", there are several email ids. I need to send an email to each email id in this column.
For this, I have created columns for Subject, Massage body, CC and BCC.

The "Send Mail To" should not have any blank cell in between. All other columns can have blank cells. If you want
multiple email ids in to then write those email ids with commas in this column. Make sure email ids are valid.

If you want to send attachments. Write addresses of those files in the attachment column.

Write the subject in the Subject column.

Massage in Massage Body column.

If you want to keep someone in cc, write his/her email id in the CC column. Same for BCC. use comma for multiple email ids.

Now to send emails to each email id in column follow these steps.

Press Alt+F11 to open VBA Editor in Excel.
Add Reference to outlook object by following steps told earlier.
In a new module, copy the below code.

'*** You must have a Outlook email configured in outlook application on your system ***
'*** add reference to outook object library from references in tools ***

Sub BulkMail()
Application.ScreenUpdating = False

ThisWorkbook.Activate
'Creating references to Application and MailItem Objects of Outlook
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem

'Creating variable to hold values of different items of mail
Dim sendTo, subj, atchmnt, msg, ccTo, bccTo As String

Dim lstRow As Long

'My data is on sheet "Exceltip.com" you can have any sheet name.
 
ThisWorkbook.Sheets("Exceltip.com").Activate
'Getting last row of containing email id in column 3.
lstRow = Cells(Rows.Count, 3).End(xlUp).Row

'Variable to hold all email ids

Dim rng As Range
Set rng = Range("C2:C" & lstRow)

'initializing outlook object to access its features
Set outApp = New Outlook.Application
On Error GoTo cleanup 'to handle any error during creation of object.

'Loop to iterate through each row, hold data in of email in variables and send 
'mail to each email id.

For Each cell In rng
    sendTo = Range(cell.Address).Offset(0, 0).Value2
    subj = Range(cell.Address).Offset(0, 1).Value2 & "-MS"
    msg = Range(cell.Address).Offset(0, 2).Value2
    atchmnt = Range(cell.Address).Offset(0, -1).Value2
    ccTo = Range(cell.Address).Offset(0, 3).Value2
    bccTo = Range(cell.Address).Offset(0, 4).Value2

    On Error Resume Next 'to hand any error during creation of below object
    Set outMail = outApp.CreateItem(0)
    
    'Writing and sending mail in new mail
    With outMail
        .To = sendTo
        .cc = ccTo
        .BCC = bccTo
        .Body = msg
        .Subject = subj
        .Attachments.Add atchmnt
        .Send 'this send mail without any notification. If you want see mail
              'before send, use .Display method.
    End With
    On Error GoTo 0 'To clean any error captured earlier
    Set outMail = Nothing 'nullifying outmail object for next mail
 Next cell 'loop ends

cleanup: 'freeing all objects created
        Set outApp = Nothing
        Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub

The above code will send emails to each email id in a row of “send mail to” column. Now to run this code, you can directly run it from here. Or you can add a figure or button in your workbook and then assign this macro to that object. I use shapes because of they customizable. To do so, follow the below steps.

- Go to Insert tab.
- In the Illustration group, you can find shapes, click on your favorite shape.

- Drag and drop it on your sheet.
- Beautify if you want.
- Right-click on it. Click on Assign Macro.

- Choose BulkMail macro from the list.

Exit from edit mode.

Now whenever you’ll click on this object, your macro will run and send emails. Check sent mails to ensure.
You can download Excel Mail Sender File here.

Send Bulk EMails From Excel VBA in One Click

Related Articles:

Split Excel Sheet Into Multiple Files Based On Column Using VBA | To split a worksheet into multiple files, based on a column's values, you'll need to filter it copy-paste it into a new file.

How to Filter the Data in Excel using VBA | Filtering data using VBA is easy. These simple lines of codes filter the data on the given criteria.

Turn Off Warning Messages Using VBA In Microsoft Excel 2016 | While performing multiple tasks, like opening and closing files, excel shows warning messages so that you don't lose data. They interrupt the running code. To avoid them

How To Loop Through Sheets In Excel Using VBA | To back and forth on two or more sheets, we need to loop through them. To loop through multiple sheets, we use...

7 Examples of For Loops in Microsoft Excel VBA | The for loop is the most used looping technique in any language. Excel VBA is no exception.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.