How to Send Mail through Outlook Using VBA?

Suppose we have thousands of Emails ID and we have to send mail on each Email ID if we send email manually it will take more time so we use VBA code for sending an email. We use outlook application with VBA for sending emails.

There are two techniques to send mail through outlook in VBA.

(A) Early binding technique

When we send mail from Early Binding then we have to add Outlook Library into Excel VBA project. We have to take some step for Adding Outlook Library.

Tools -> References -> Microsoft Outlook 16.0 Object Library -> OK

How to Send Mail Through Outlook Using VBA

How to Send Mail Through Outlook Using VBA with attachment

(B) Late Binding technique

We don’t need to add Outlook Library into Excel VBA Project for Late binding technique.

Here are given some Example

How to Send Mail through Outlook Using VBA?

Sub Send_Mail_1()

    ‘This is an Early binding technique so we have to add Outlook Library

    Dim OL_App As Outlook.Application

    Dim Mail As Outlook.MailItem

    Set OL_App = New Outlook.Application ‘Create a new instance of Outlook Application

    Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

    With Mail

        .BodyFormat = olFormatHTML ‘Choose Body Format

        .To = “someone@someto.com”

        .CC = “someone@somecc.com”

        .BCC = “someone@somebcc.com”

        .Subject = “This is AVIT Guru”

        .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

        .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

        .Display ‘For display mail dialog box

        .Send ‘For Sending Email

    End With

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

How to Send Mail through Outlook Using VBA?

Sub Send_Mail_2()

    ‘This is Late binding technique

    Dim OL_App As Object

    Dim Mail As Object

    Set OL_App = CreateObject(“Outlook.Application”) ‘Create new instance of Outlook Application

    Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

    With Mail

        .BodyFormat = olFormatHTML ‘Choose Body Format

        .To = “someone@someto.com” ‘Write Email ID

        .CC = “someone@somecc.com”

        .BCC = “someone@somebcc.com”

        .Subject = “This is AVIT Guru”

        .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

        .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

        .Display ‘For display mail dialog box

        .Send ‘For Sending Email

    End With

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

How to Send Mail through Outlook Using VBA?

Sub Send_Mail_3()

    ‘This is an Early binding technique so we have to add Outlook Library

    Dim OL_App As Outlook.Application

    Dim Mail As Outlook.MailItem

    Set OL_App = New Outlook.Application ‘Create a new instance of Outlook Application

    Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

    With Mail

        .BodyFormat = olFormatHTML ‘Choose Body Format

        .To = Range(“B2”).Value

        .CC = Range(“C2”).Value

        .BCC = Range(“D2”).Value

        .Subject = “This is AVIT Guru”

        .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

        .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

        .Display ‘For display mail dialog box

        .Send ‘For Sending Email

    End With

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

How to Send Mail through Outlook Using VBA?

Sub Send_Mail_4()

    ‘This is Late binding technique

    Dim OL_App As Object

    Dim Mail As Object

    Set OL_App = CreateObject(“Outlook.Application”) ‘Create new instance of Outlook Application

    Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

    With Mail

        .BodyFormat = olFormatHTML ‘Choose Body Format

        .To = Range(“B2”).Value

        .CC = Range(“C2”).Value

        .BCC = Range(“D2”).Value

        .Subject = “This is AVIT Guru”

        .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

        .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

        .Display ‘For display mail dialog box

        .Send ‘For Sending Email

    End With

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

How to Send Mail through Outlook Using VBA?

Sub Send_Mail_5()

    ‘Using For Loop

    ‘This is an Early binding technique so we have to add Outlook Library

    Dim OL_App As Outlook.Application

    Dim Mail As Outlook.MailItem

    Dim i As Integer

    Set OL_App = New Outlook.Application ‘Create a new instance of Outlook Application

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row ‘Loop Execute as per Record No.

        Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

        With Mail

            .BodyFormat = olFormatHTML ‘Choose Body Format

            .To = Range(“B” & i).Value

            .CC = Range(“C” & i).Value

            .BCC = Range(“D” & i).Value

            .Subject = “This is AVIT Guru”

            .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

            .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

            .Display ‘For display mail dialog box

            .Send ‘For Sending Email

        End With

    Next i

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

How to Send Mail through Outlook Using VBA? 

Sub Send_Mail_6()

    ‘Using Loop

    ‘This is Late binding technique

    Dim OL_App As Object

    Dim Mail As Object

    Dim i As Integer

    Set OL_App = CreateObject(“Outlook.Application”) ‘Create new instance of Outlook Application

    Set Mail = OL_App.CreateItem(olMailItem) ‘Create Mail Object

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row ‘Loop Execute as Per Record No.

        With Mail

            .BodyFormat = olFormatHTML ‘Choose Body Format

            .To = Range(“B” & i).Value

            .CC = Range(“C” & i).Value

            .BCC = Range(“D” & i).Value

            .Subject = “This is AVIT Guru”

            .Attachments.Add “E:\AVIT Tutorial\OutlookMail\My Adds Projects.xlsm”

            .HTMLBody = “This is Outlook Class” ‘The HTMLBody support HTML Format

            .Display ‘For display mail dialog box

            .Send ‘For Sending Email

        End With

    Next i

    Set OL_App = Nothing ‘Reset the Object Variable

    Set Mail = Nothing ‘Reset the Object Variable

End Sub

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *