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