XL5: Sending Mail from MS Excel Using VBAMAPI.DLL (Part 2)

• Microsoft Excel for Windows, versions 5.0, 5.0c

SUMMARY

In Microsoft Excel, you can use the SendMail method in a Visual Basic macro for electronic mail (E-mail) functionality. However, for more complete control of mail, including message content, subject, return receipt, and recipients, you must use the VBAMAPI.DLL file, which currently ships with Microsoft Project version 4.0 for Windows (this file is located on Setup Disk 2).

The following file(s) are available for download from the Microsoft Software Library:

 ~ VBAMAPI.EXE (size: 69050 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

Once you've downloaded the file, copy it to your Windows SYSTEM directory.

The information in the "More Information" section of this article contains the Declare functions and data type definitions necessary to use VBAMAPI.DLL with Microsoft Excel.

For more information about using MAPI with Microsoft Excel for Windows, please see the following articles:

   ARTICLE-ID: Q125854
   TITLE     : XL: Sending Mail from MS Excel Using VBAMAPI.DLL (Part 1)

   ARTICLE-ID: Q123185
   TITLE     : GP Fault Using MAPI.DLL Calls in Visual Basic Procedure

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following code examples demonstrate how to:

• Check for an existing mail session.
• Ask for an E-mail name, verify that the E-mail name is valid, and display a mail message with the Address, Subject Line, and Body of the mail message filled in.

Checking for an Existing Mail Session

The following function checks if there is a current mail session running. If there is no current session of mail, the function starts a new mail session and returns the current session number.

Option Explicit

'---------------------------------------------------------------------
' Function:     CheckLogon
' Arguments:    None
' Returns:      MAPI session handle
' Comments:     Forces a Logon if necessary, then converts the MS Excel
'               MS Excel hex-based session handle to a MAPI-usable Long
'---------------------------------------------------------------------

'Application.MailSession will return the current mail session number
   If IsNull(Application.MailSession) Then
'If this is NULL Application.MailLogon will establish a mail session
        Application.MailLogon
    End If
    CheckLogon = CLng("&h" & Application.MailSession)

Prompting the User for an E-mail Name,

Verifying the Name, and Displaying a Mail Message

The following code prompts the user for an E-mail name, checks for or starts an instance of mail, verifies the E-mail, and brings up a mail message with the To, Subject, and Body text fields filled in and ready to send.

Sub Mail_Test()
    Dim lresult As Long
    Dim msg As MapiMessage
    Dim lSess As Long
    Dim r As MapiRecip
    Dim sName As String
    Dim test As MapiFile
    'Calls CheckLogon to return the number of the mail session
    lSess = CheckLogon()
    'Prompts the user for an E-mail Name
    sName = InputBox(prompt:="Your email name please",

    'Ends the macro if Cancel or no name is entered
    If sName = "" Then End
    'Verifies the E-mail name is a valid name
    If MAPIResolveName(lSess, 0, sName, 0, 0, r) <> SUCCESS_SUCCESS Then
        MsgBox "Could not resolve name '" & sName & "'"
        Exit Sub
    End If
    'msg.Subject is the text which will show up on the Subject line
    'of the Mail Message
    msg.Subject = "This is the Subject Line"
    'msg.NoteText is the text which will show up in the Body
    'of the Mail Message
    msg.NoteText = "This is Sample Text."
    'msg.RecipCount is the total number of Mail Messages to send
    msg.RecipCount = 1
    lresult = MAPISendMail(lSess, 0, msg, r, test, MAPI_DIALOG, 0)
End Sub