Excel macro to send emails using template in Ms Word

In our earlier posts, we explained how to recreate mail merge functionality using vba in word to equip with few additional features.

In this post, we are developing same logic in excel. Here's a sample code. You can tweak it as required. And feel free to write to me in case you face any trouble while tweaking.


Considering the word document template as:

And data in excel as:

Sub Word()

Dim wd As Word.Application

Dim olApp As Outlook.Application

Dim Omail As Outlook.MailItem

Dim wddoc As Document

Set wd = New Word.Application

Set wddoc = wd.Documents.Open("C:\ABC.docx")

For i = 2 To 5

Set olApp = New Outlook.Application

Set Omail = olApp.CreateItem(olMailItem)

 With Omail

     .To = "abc.def@xxx.com"

     .Subject = "I love XtremeExcel's Youtube Channel"

     .Body = wddoc.Content

 For x = 1 To 2

 .Body = Replace(.Body, "_" & x & "_", Sheets(1).Cells(i, x).Value)

 Next

 .Send

 End With

Next

End Sub

How to send bulk emails using Excel Macro?

If you want to send bulk emails using a simple excel program then you are at right post. We can send emails using Outlook, SMTP and infact any of the mail serverice providers like gmail, yahoo etc. 

In this post you will learn how to use outlook for sending bulk emails using a simple excel macro.


1. Open VB editor in excel.

2. Put email ids, subject and message in a sheet for which you wish to trigger emails.

3. Create a module and paste this code.

Sub SendMail()

Dim olApp As Outlook.Application

Dim olMail As Outlook.MailItem

Set olApp = New Outlook.Application

For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    Set olMail = olApp.CreateItem(olMailItem)

   

    With olMail

        .To = Cells(i, 1).Value

        .Subject = Cells(i, 2).Value

        .Body = Cells(i, 3).Value

        .Display

        ''.Send

    End With

   

    Set olMail = Nothing

Next

Set olApp = Nothing

End Sub

And that's All...

You can watch this video for complete tutorial.