Microsoft KB Archive/125853

{|
 * width="100%"|

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

 * }

Q125853

-

The information in this article applies to:


 * 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 is available for download from the Microsoft Download Center:

"Vbamapi.exe" For additional information about how to download Microsoft Support files, click the article number below to view the article in the Microsoft Knowledge Base:

"Q119591 How to Obtain Microsoft Support Files from Online Services" Microsoft used the most current virus detection software available on the date of posting to scan this file for viruses. Once posted, the file is housed on secure servers that prevent any unauthorized changes to the file.

Note that if you use VBAMAPI.DLL with Microsoft Excel, use the built-in MailLogon and MailLogoff methods.

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:

Q125854 Sending Mail from MS Excel Using VBAMAPI.DLL (Part 1)

Q123185 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 professionals 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 '-

Function CheckLogon '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) End Function

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", Title:="VBAMAPI Example", default:="joeuser") '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 Additional query words: 5.00a 5.00c mail mapi vbamapi

Keywords : kbinterop kbprogramming kbofficeprog

Issue type :

Technology : kbExcelSearch kbExcel500 kbExcel500c