What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleSend email from VBA code by using MAPI
DescriptionThis example shows how to send email from VBA code by using MAPI.
KeywordsMAPI, email, Excel, VBA, send mail
The ShellExecute API function is a very easy way to send short email messages but the total length of the values you pass to ShellExecute can be no longer than 255 characters so you are limited to very short messages. For an example, see Use ShellExecute to send mail using the default mail program. You can use MAPI (Mail Application Programming Interface) to send much longer messages.

Before you start, add a reference to the MAPI controls library. This is probably somewhere such as C:\Windows\System\MSMAPI32.OCX.

The SendEmail subroutine in following code sends an email to one "To" recipient and one "Cc" recipient. It create an MAPISession and signs on to MAPI. It then composes a message. It uses RecipIndex to select the first recipient and sets that person's name, email address, and recipient type (To).

Next the code uses RecipIndex again to select a second recipient. It sets that person's name, email address, and recipient type (Cc).

The code sets the mail message's subject and body, and sends the message. It finishes by signing off from MAPI.

' Send an email message.
Public Sub SendEmail(ByVal to_name As String, ByVal _
    to_address As String, ByVal cc_name As String, ByVal _
    cc_address As String, ByVal subject As String, ByVal _
    body As String)
Dim mapi_session As MSMAPI.MAPISession
Dim mapi_messages As MSMAPI.MAPIMessages

    'Debug.Print "To: " & to_name & "<" & to_address & ">"
    'Debug.Print "Cc: " & cc_name & "<" & cc_address & ">"
    'Debug.Print "Subject: " & subject
    'Debug.Print "Body: " & body
    ' "=================================================="

    On Error GoTo MailError

    Set mapi_session = New MSMAPI.MAPISession
    With mapi_session
        .LogonUI = False
        ' Fill in username and password
        ' if necessary on this mail server.
        '.username = "username"
        '.password = "password"
    End With

    Set mapi_messages = New MSMAPI.MAPIMessages
    With mapi_messages
        .SessionID = mapi_session.SessionID

        .RecipIndex = 0
        .RecipDisplayName = to_name
        .RecipAddress = to_address
        .RecipType = mapToList

        .RecipIndex = 1
        .RecipDisplayName = cc_name
        .RecipAddress = cc_address
        .RecipType = mapCcList

        .AddressResolveUI = False
        .MsgSubject = subject
        .MsgNoteText = body
        .Send False
    End With

    Exit Sub

    MsgBox Err.Description
    Exit Sub
End Sub
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.