Monday, October 10, 2011

Excel VBA: Send E-mail from Excel

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
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment