Using Arrays with Workgroup Extensions for Microsoft Excel

The information in this article applies to:

• Microsoft Excel for Windows, version 4.0

SUMMARY

The following macro demonstrates the steps necessary to use an array of recipient names to send electronic mail from a Microsoft Excel macro using Workgroup Extensions for Microsoft Excel. The purpose of this macro is to send a mail message to each person listed in the array A2:C4. The address should include the message type preceding the email name. Class types are 1=to:, 2=cc:, and 3=Bcc:.

NOTE: Before you run this macro, place the file XLMAPI.XLL in the XLSTART directory to load this file into memory.

Sample Macro Code

 A1  NAME                 B1  ADDRESS          C1 CLASS
 A2  John Smith           B2  ipm:johnsm       C2 1
 A3  Jane Doe             B3  ipm:janedo       C3 2
 A4  Fred Wong            B4  ipm:fredwo       C4 3
 A5  =MAIL.LOGON("MY LOGON NAME","MY PASSWORD",TRUE)
 A6  =MAIL.NEW.MESSAGE()
 A7  =SET.MAIL.MESSAGE(7,A2:C4)
 A8  =SET.MAIL.MESSAGE(1,"This is a Microsoft Excel MAPI test")
 A9  =MAIL.SEND.MESSAGE()
 A10 =MAIL.LOGOFF()
 A11 =RETURN()

Explanation of Macro

  A5:  Logs onto email for this instance of Microsoft Excel. This step is
      necessary even if you are currently logged into your mail host.

  A6:  Creates a new mail message.

  A7:  Sets Recipients to the recipient array.

  A8:  Sets Mail message title.

  A9:  Sends the current mail message.

 A10: Logs you off of mail currently used for this instance of Microsoft
      Excel. If you were logged into your mail host prior to running this
      macro, MAIL.LOGOFF() will not log you off of that previous instance
      of mail.

 A11: Ends the macro.

Creating the Above Macro

To create the above macro, follow the steps below:

1. Type the above macro commands using the correct email name and password.

2. Create your recipient array similar to A2:C4, using the correct email names (the correct message type "IPM,IPC,MTP" and so on, can be found by referring to the technical reference manual for your mail system).

   NOTE: This same array can be created by the array formula

         ={"John Smith","ipm:johnsm",1;"Jane Doe","ipm:janedo",2;"Fred
         Wong","ipm:fredwo",3}
   

   and referenced by the SET.MAIL.MESSAGE function.

MORE INFORMATION

If you have a modem, you can get more information about Workgroup Extensions for Microsoft Excel or get a copy of the XLMAPI.XLL file by downloading the MAPIDEMO.EXE file from the Microsoft Download Service (MSDL). To connect to the MSDL, call (425) 936-MSDL.