Outlook Email Object

Sending Email in Outlook from VBA is relatively easy with the Outlook Email object, assuming the client machine has Outlook installed. Once the object has been created and a new Email Message opened, it is simply a case of passing the various parameters, to add email functionality to spreadsheets and other VB Applications.

To Send an Email using Outlook, Create an Object, and set its properties for the To box, the Cc & Bcc boxes, then the Subject, and either the Body or htmlBody text depending on if you want to send a Plain Text or Html Email. Once these have been set, you can use one of two methods, either Display to show the email message, this can then be customised with a signature or additional text before sending, or you can use the Send method to send the email directly without showing it first.

The best way to do this is to put the code in its own Sub-procedure, so you can re-use the code across different VBA projects, you could also combine this with other Functions to return the To & Cc text strings, in which case you would replace the variable names below, with the name of the Function, but here is a basic example of how create a blank email:

Public Sub CreateBlankEmail(strTo As String, strCC As String, strSubject As String)
   Dim OutApp As Object
   Dim OutMail As Object
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(0)
   On Error Resume Next
   With OutMail
     .To = strTo
     .CC = strCC
     .BCC = ""
     .Subject = strSubject
     .Body = ""
     .Display
   End With
   On Error GoTo 0
   Set OutMail = Nothing
   Set OutApp = Nothing
End Sub

At some point, in another procedure you would call it like this:

CreateBlankEmail "mail@jmvba.guru", "", "Email Enquiry"

In Excel, it is also possible to send a Range of Cells as a table in an email; this is done by creating a temporary xml file, and then importing that into an html message. More details on this can be found in the Excel section.