How to Send Bulk Emails From Excel VBA in One Click in Excel Office 365?
Tags: tosendbulkemails, vbamacros
Published: 2021-09-13 05:47:54

---
title: How to Send Bulk Emails From Excel VBA in One Click in Excel Office 365?
published: true
date: 2021-09-09 16:38:20 UTC
tags: ToSendBulkEmails,VBAMacros
canonical_url: https://geekexcel.com/how-to-send-bulk-emails-from-excel-vba-in-one-click-in-excel-office-365/
---

In this tutorial, we will guide you on  **how to send Bulk Emails from Excel VBA in one click in Excel Office 365**. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## How to send mail from Excel?

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to **add a reference to outlook object** library.
- For that, you need to go to **Tools** in the menu, and click on **References**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Reference-1.png)<figcaption>Click on Reference</figcaption>

- After that, you have to find **Microsoft 16.0 Object Library** and **checkmark** it and then click **OK**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Microsoft-16.0.png)<figcaption>Click on Microsoft 16.0 library</figcaption>

- You need to create **References of Outlook Application and Mail** and **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.

```

- Then, you have to **Initialize the references**.

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

```

- Now, you need to **send mail using outMail object**.

```
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

```

## Send emails to each email id

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to **add a reference to outlook object** library.
- For that, you need to go to **Tools** in the menu, and click on **References**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Reference-2.png)<figcaption>Click on Reference</figcaption>

- Then, in a **new module** , you have to 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

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-26-1024x897.png)<figcaption>Save the Code</figcaption>

- Now, the above code will **send emails to each email id** in a row of send mail to column.
- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-13.png)<figcaption>Run the Code</figcaption>

- Finally, you will receive **output** in Microsoft Excel.

![](https://geekexcel.com/wp-content/uploads/2021/09/Output-1-1024x209.png)<figcaption>Output</figcaption>

## Add a figure or button

- You can **add a figure or button** in your workbook and then **assign this macro** to that object.
- For that, you need to go to **Insert tab**.
- Then, in the **Illustration group** , you can find **shapes** , and click on your **favorite shape**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Shape.png)<figcaption>Click on Shape</figcaption>

- Now, you have to **drag and drop** it on your sheet.
- After that, you need to **beautify** if you want.
- You have to **right-click** on it and click on **Assign Macro**.
- Now, you need to choose **BulkMail macro** from the list.
- Finally, you have to **Exit from edit mode**.

## End of the Era

In the above article, you can learn the simple steps on **how to send Bulk Emails from Excel VBA in one click in Excel Office 365**. Kindly, share your  **feedback**  in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

**Read Next:**

- **[How To Use VBA Class Modules in Excel office 365?](https://geekexcel.com/how-to-use-vba-class-modules-in-excel-office-365/)**
- **[How to Delete Column in Microsoft Excel 365?](https://geekexcel.com/how-to-delete-column-in-microsoft-excel-365/)**
- **[How to Hide Sheet in Microsoft Excel 2007?](https://geekexcel.com/how-to-hide-sheet-in-microsoft-excel-2007/)**
- **[Options in Quick Access Toolbar in Microsoft Excel 365](https://geekexcel.com/options-in-quick-access-toolbar-in-microsoft-excel-365/)**
- **[How to Create an Array of Numbers in Excel Office 365?](https://geekexcel.com/how-to-create-an-array-of-numbers-in-excel-office-365/)**