Send Email From Excel VBA Macro

We can integrate Excel with other Microsoft Office applications using VBA. In this post we are going to see how to send an email from Excel (via Outlook) using VBA macros. You need to have Microsoft Outlook installed and configured with a mail account for the macro to work.  

 

Macro/VBA code:

 

  Sub SendMailFromExcel()

    Dim OutApp As Object, OutMail As Object

     

    Set OutApp = CreateObject("Outlook.Application")

    Set OutMail = OutApp.CreateItem(0)

    

    On Error Resume Next

    With OutMail

      .To = Range("B1").Value

      .Subject = Range("B2").Value

      .Body = Range("B3").Value

      .Display 'optional

      '.Send 'uncomment to send

    End With

    On Error GoTo 0

    

     Set OutMail = Nothing

    Set OutApp = Nothing

  End Sub

 

Macro explained:

• First we declare two object variables to assign the Outlook application and Outlook item (mail). Object variables are a special type of variable used to store VBA objects, ActiveX objects, and other types of objects. 

• Then we use the CreateObject function to initialize Outlook, and we assign the Outlook Application object to the variable OutApp. Now we can use the CreateItem method of the Outlook Application object to assign the first item to the variable OutMail (0 is the index for a mail item type as per OlItemType enumeration).
• Alternatively, we could use early binding having the Microsoft Outlook Object library added to the references and declare/define the objects with less code.
 Dim OutApp As New Outlook.Application
 Dim OutMail As MailItem

• Using a With statement, we set properties of the mail item (OutMail) for recipient email address (To), email subject, and email body. You can add as many recipients as needed separating the email addressing with a semi-colon (;). You can also add attachments with Attachments.Add followed by the drive path and file name of the attachment file.
• The Display method shows the email in Outlook before sending. If we want to fully automate it, we have to use the Send method too (and no need for Display).
• Finally, we clear the object variables or set both to nothing – this is good practice when coding VBA.

IMPORTANT: If you do not have Outlook installed and setup in your machine, the macro will not work.

This is how we send mail from Excel using VBA macros.

Other examples:

• How to Add Unique Entries Validation List with Excel VBA macros
  
• How to Separate Sheets Into Workbooks with Excel VBA macros
  
• How to Add Web Query to Worksheet with Excel VBA macros