There are several ways to send e-mail from Excel using Microsoft Outlook.
It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments.
In this example we are going to use the COM library of Microsoft Outlook to get more control. Attachments are not covered in this example.
We start by checking if an instance of Outlook is already running. To be independent of the installed version of Outlook we use the GetObject to try to get an existing instance. Another option would have been to reference the Outlook library directly, but then we are required to update the reference every time a new version of Microsoft Office is installed. If no instance is running, we start Outlook by calling CreateObject. Please remember to close down Outlook when you are finished. Otherwise you will get multiple instance of the same application without the user knowing it.
We get a reference to the current users inbox by calling getDefaultFolder(6).The value 6 is a constant referring to the Inbox. If you used a reference to the COM library, you would have used the enum value olFolderInbox.
' Send E-mail ' strTo - Recipients. List of e-mails (separated by ';') Public Sub SendEmail(ByVal strTo As String) Dim objOutlookApp As Object Dim objEmail As Object Dim objMapi As Object Dim objInboxFolder As Object ' Valid e-mails? If (strTo = "") Then ' Error MsgBox "No e-mails have been set!", vbExclamation, "No e-mails" ' Finished Exit Sub End If ' Attach to outlook On Error Resume Next Set objOutlookApp = GetObject(, "Outlook.Application") On Error GoTo 0 ' Is Outlook running? If (objOutlookApp Is Nothing) Then ' Create new instance of outlook Set objOutlookApp = CreateObject("Outlook.Application") ' Get the MAPI namespace (e-mails) Set objMapi = objOutlookApp.GetNamespace("MAPI") ' Get the inbox folder Set objInboxFolder = objMapi.getDefaultFolder(6) ' Display the inbox folder (make outlook visible) Call objInboxFolder.Display End If ' Create the new e-mail Set objEmail = objOutlookApp.CreateItem(0) ' Set the properties of the new email With objEmail ' Set the recipients .To = strTo ' Show the message Call .Display ' Resolve all recipients (Same as pressing the "Check Names" button) Call .Recipients.ResolveAll End With ' Free memory Set objEmail = Nothing Set objOutlookApp = Nothing End Sub