Send an Email from Excel - VBA

Introduction:

Excel VBA allows us to send emails from Excel. In this article i am going to explain about this.

We can use a VBA macro to create/send a new message and preset any of the fields, including To/CC/BCC, the subject, flags, voting options and more.

Steps:

1. Open Excel, and press Alt+F11 (this will open a new window Visual Basic Editor)

2. Select Tool option in the main menubar >> and then select References

3. Select Microsoft Outlook 14.0 Object Library or higher version reference

4. Click on Insert >> Module and then paste the below code

Code:

Sub Sendmail()

    Dim olApp As Outlook.Application

    Dim olMail As Outlook.MailItem

    

    Set olApp = CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    

    With olMail

        .To = "xyz@abc.com"

        .CC = "wxy@abc.com"

        .Subject = "Automated email from Excel"

        .Body = "Hi, This is test Email"

        .Display

        '.Send

    End With

    

    Set olApp = Nothing

    Set olMail = Nothing

End Sub

You can change .Display to .Send if you want to send mail automatically (Use .Display when testing)

To add attachments in the mail. use the below line of code

                                                 !********************* Try this J ***************************!