Outlook object in VBA Excel

The excel VBA can be used to do advanced tasks such as automation of Emails through Outlook program.

Most of the company data are in excel format.So, a program to automate the sharing of these data through Email will be of much use.

The VBA can be used to send the whole Workbook, a Worksheet or part of its data or any other file from the drive.

The VBA is best suited with Outlook application only, though mail automation can be done with other applications such as Gmail,Yahoo, it involves much complexity.

Create Outlook Object

To Use Outlook application, a reference has to be added to it through "Add Reference"

After that, an instance of the Outlook application has to be created as shown below:

  1. Dim outlook_app As Object
  2. Set outlook_app=CreateObject("Outlook.Application")

Now that an Outlook application object has been created.

The next step is to create a Mail object.

  1. Dim objmail As Object
  2. Set objmail=outlook_app.CreateItem(0)

This property is used to specify the destination email address.

To send to multiple destination email address, the email addresses has to be separated by a ;


  1. .To


This property is used to set the subjet of the mail.

This simply takes a string and adds it to the subject


  1. .Subject

This is used to send a copy of the mail to a recepient.


  1. .CC


This property, as the name implies is used to set the body of the mail being sent.

This too takes a string argument and makes it the body of the mail document.


  1. .Body


This property is used to add file attachments along with the mail.

The attachments takes a file location from the local disk and sends with the mail

The size constraints of the attachments are same as any other mail application.


  1. .Attachments


As the name suggests, this property is used to send the mail to the recepients.

This does not take any arguments, though as everything is specified already.


  1. .Send


You can find similar Excel Questions and Answer hereunder

1) How can I hide Formula Bar and Headings using VBA?

2) How to do workbook protection with VBA in Excel

3) How can I set FreezePanes in a certain range using VBA?

4) How can I list all files in a folder using VBA?

5) How can worksheet functions be accessed in VBA?

6) How to read a value from a cell in vba in Excel

7) How can I add a Timestamp after macro execution?

8) How to add email and emailing function in Excel VBA

9) How to use the trace error function in VBA

10) How can I export a chart as a gif file?


Here the previous and next chapter