by Peter Schellenbach and Joe Goldthwaite

We get a lot of requests for help with various projects involving AccuTerm and Microsoft Office. One of the more common requests is for techniques that you can use to automatically send email from your MultiValued database application. Let’s say you have a customer contact application and one of your customers calls with a request for some information. You’d like to be able to press a button and have your MV app automatically email the information to the customer. Well the good news is you can!

There are a number of ways to accomplish this task. Which one you choose depends on your requirements and what type of email client you’re using. In this article, we’ll explore four different methods: SMTP, Outlook, Collaborative Data Objects (CDO), and MAPI. Except for SMTP, you’ll need to have your Windows based email client configured and working first. This is because each of the above methods (except SMTP) use your current email client to perform the actual email send operation. If you don’t have email working on the client computer, these other methods will not work.

Outlook is the Email client that comes with Microsoft Office. It is the most common. It is also the simplest in that it uses the cleanest object model of the three methods. Of course to use this method, you must be using Outlook as your Email client. The disadvantage of the Outlook method is that it’s slower and requires lots of memory. CDO is another object model from Microsoft. It’s similar in some ways to the Outlook object model. It’s much smaller however and in our experience, it’s faster than Outlook and takes up less memory. The object model for CDO is more complex, and harder to install and get running. MAPI stands for Messaging Application Programming Interface. It’s Microsoft’s original attempt at standardizing the methods of sending Email. MAPI predates the other methods and it works at a much lower level. Because of this, it’s the hardest to work with and debug. It has an advantage in that it works with other Email clients besides Outlook, including Outlook Express and Eudora. It also requires the least amount of memory and because it works at a lower level, it is the fastest of the three methods.

In order to get the most from this article you should have some familiarity with Visual Basic or with the AccuTerm scripting language.  If you haven’t worked with VB, you might want to get one of the many books on the subject and study up.  It will be time well spent.  The samples here have been tested with AccuTerm 2000, AccuTerm 2K2 and AccuTerm 7.

The Outlook Method

This method takes advantage of the Outlook Object Model. What is an object model and how does it work? Ask some people and you will get a confusing discussion of encapsulation, polymorphism, and inheritance. Fortunately for our purposes, we don’t need to go into any of that. To use an object model, we only need a basic understanding of variables and syntax.

To interface to a particular object model, you first have to create an object variable. This might sound confusing but it really isn’t. An object variable is just another type of variable similar to the types you’re probably already been working with. Instead of a string or an integer variable, it’s a variable of type “Object”. How do we create one? Simple, we just declare the variable and then use the CreateObject function in an AccuTerm VBA script to create the object and assign its value to the variable. Here is an example:

Dim OutlookApp as Object
Set OutlookApp = CreateObject(“Outlook.Application”)

Looks pretty simple doesn’t it? A lot of things are going on here. This single CreateObject statement is actually initiating a number of things. It has to look in the system registry to find out what type of object an “Outlook.Application” is. Then it has to look up the executable program (EXE or DLL) that creates that type of object. It runs the executable and requests a reference to the object. The reference then gets assigned to the OutlookApp object variable. Whew.

There can only be one copy of Outlook running at a time so our CreateObject command will do one of two things. If Outlook is already running, we’ll get an object reference to the existing session. If it’s not running, it will be started in the background and then we’ll get a reference to the new session. (If your Email is set up to prompt the user for a profile, you’ll get the same prompt at this point and you’ll need to choose a profile.)

That takes care of getting our Outlook Application Object. Now what do we do with it? To use the object, we’ll make use of the object’s properties and methods. An object’s properties and methods are accessed with the dot (.) operator. The syntax for referencing a property or method is object.property or object.method. It’s hard to explain in print but easy to show in an example. Our Outlook object has a “Name” property. The syntax to reference it would be “OutlookApp.Name”. It also has a “Quit” method. To execute it, we would just use the line “OutlookApp.Quit”. The main difference between properties and methods are that methods do something whereas properties store values.

Now lets use the OutlookApp object’s methods and properties to do our bidding. The first thing we need is a mail item object. We don’t use the CreateObject function to do this because the code for creating a mail item object is part of the Outlook application code. To create it, we need to use a method of the OutlookApp object we created in the previous step:

Dim MailItem as Object
Set MailItem = OutlookApp.CreateItem(0)

In the above case, CreateItem is a method of the OutlookApp object. It takes one parameter. The parameter determines what type of object gets created.  It could be a journal item, a contact item, etc. In this case, we’re passing a zero which is tells the CreateItem method to create an Email item.

Let’s review. We first declared an object variable then used the CreateObject function to create an Outlook.Application object and assign it to our OutlookApp variable. We then used the CreateItem method to create our Email object – MailItem. To actually send an Email, we need to create some more objects and use their methods and properties to do the job. Here are the objects we’ll need:

Dim OutlookApp as Object ‘Outlook.Application
Dim MailItem as Object   ’Outlook.MailItem
Dim Recipient as Object  ’Outlook.Recipient
Dim Attachment as Object ‘Outlook.Attachment

The comment after each object declaration is the actual object type. Let’s create our recipient object. Here again, we don’t use the CreateObject function because the MailItem object has code to create a recipient. It uses the Add method to both create the object and return the reference to it. Let’s add a recipient:

Set recipient = MailItem.Recipients.Add(“somename@somewhere.com”)

That’s a whole bunch of dots but each section separated by the dot references a property or method of the object before it. “Recipients” is a collection object of “MailItem”. It holds entries for each recipient that the mail item is to be sent to–and there can be more than one. “Add” is a method of the “Recipients collection”. Each time we execute an Add method, a new recipient is added to the collection of recipients. The Add method returns a recipient object that we assign to the Recipient variable. We can now use this Recipient object to make sure we have a valid Email address. This is done with the Recipient’s Resolve method:

If Not Recipient.Resolve    Then

MsgBox “There is an invalid recipient”

End If

If we don’t need to check the address, we could use the Add method without saving the resulting Recipient object using this syntax:

MailItem.Recipients.Add “somename@somewhere.com”

It accomplishes the same thing–the recipient is added to the list. We’re just throwing away the returned object because we don’t need it. There is another point here. The recipient object returned by the Add method is a copy. There is another copy saved in the MailItem.Recipients collection. BOTH objects are referencing the SAME recipient object. That’s why we can change properties in our copy of Recipient without having to put it back in the message. Changing the properties of our recipient object also changes it in the Recipients collection because they are the SAME.

We mentioned that we could send the Email to more than one person. How? We could just use the Add method of the Recipients object to add another recipient:

MailItem.Recipients.Add “name1@somewhere.com”
MailItem.Recipients.Add “name2@somewhere.com”

This would send the mail item to both addresses. Each time we execute the Add method, a new recipient object is created and added to the Recipients collection. We could test this by using property of the Recipients object–the Count.  If we put this line in our code:

Debug.Print MailItem.Recipients.Count

We’d get a count of the number of recipients that this Email has in it. By the same token, we can look at each of the recipient’s names like this:

Debug.Print Mail.Item.Recipients(1).Name
Debug.Print Mail.Item.Recipients(2).Name

which would show us the names of the two recipients.

Now let’s put in the subject and a message body using the Subject and Text properties respectively:

MailItem.Subject = “This text should show up on the subject line”
MailItem.Body = “This text should be in the message body”

Just for fun, let’s also add an attachment:

Set Attachment = MailItem.Attachments.Add “c:\testfile.txt”

Now we can just send the mail item with the Send method:

MailItem.Send

That’s it! We just accessed the Email server, created a new mail message and sent it off to our recipient. We just have one thing left to do–cleanup. Although these items will get cleaned up by themselves eventually, it may not always do it correctly so it’s good practice to do it manually:

‘Clean up
Set Attachment = Nothing
Set Recipient = Nothing
Set MailItem = Nothing
Set OutlookApp = Nothing

Putting it all together, we get this subroutine complete with parameters to specify the Email address, subject, body, and file names to attach:

Sub Main()

‘Create our object variables
Dim OutlookApp as Object ‘Outlook.Application
Dim MailItem as Object   ’Outlook.MailItem
Dim Recipient as Object  ’Outlook.Recipient
Dim Attachment as Object ‘Outlook.Attachment
‘Create the Outlook application object
Set OutLookApp = CreateObject(“Outlook.Application”)
‘Use the Application object to create our mail object
Set MailItem = OutLookApp.CreateItem(0)

‘Add a recipient to the mail item
Set Recipient = MailItem.Recipients.Add(“joe@asent.com”)
‘Check the email address
If Not Recipient.Resolve Then

MsgBox “The recipient did not check out!”

Exit Sub
End If 

‘Add the subject and message body
MailItem.Subject = “This is the subject”
MailItem.Body = “This is the message body”
‘Lets add a test file as an attachment.
MailItem.Attachments.add “c:\Test.txt”
‘Finally, use the Send method to send the email
MailItem.Send 

‘Now lets clean up our object references
Set Attachment = Nothing
Set Recipient = Nothing
Set OutLookApp = Nothing 

End Sub

We can test the above code by pasting it into the AccuTerm scripting window and stepping through the code. Be sure to change the Email addresses and the attached file name to something appropriate to your system. From AccuTerm’s menu select Tools->Script to open up the scripting window. Replace the default Sub Main…End Sub lines with the above script and select Run. If everything is working right, you should send out an Email.

To finish this off, we need a way to invoke this script from Pick/BASIC passing the appropriate arguments. Since host-initiated scripts do not support arguments in AccuTerm’s scripting implementation, we’ll take a brute-force approach and initialize the contents of any variables which would normally be arguments. Here is a sample Pick/BASIC subroutine which constructs the desired VBA script as a string and uses AccuTerm’s ESC STX “P” command to execute it:

SUBROUTINE AT.SEND.EMAIL.OUTLOOK(ADDRESS, SUBJECT, MESSAGE, ATTACH)
* ADDRESS: recipient’s email address
* SUBJECT: one-line email subject
* MESSAGE: multi-line email message (lines separated by AM)
* ATTACH: optional attachment file name
EQU AM TO CHAR(254), VM TO CHAR(253), SVM TO CHAR(252)
EQU ESC TO CHAR(27), STX TO CHAR(2), CR TO CHAR(13)
EQU EM TO CHAR(25)
SCRIPT = ”
*Create our object variables
SCRIPT = SCRIPT : ‘Dim OutlookApp as Object’ : EM
SCRIPT = SCRIPT : ‘Dim MailItem as Object’ : EM
SCRIPT = SCRIPT : ‘Dim Recipient as Object’ : EM
SCRIPT = SCRIPT : ‘Dim Attachment as Object’ : EM
*Create the Outlook application object
SCRIPT = SCRIPT : ‘Set OutLookApp = CreateObject(“Outlook.Application”)’ : EM
*Use the Application object to create our mail object
SCRIPT = SCRIPT : ‘Set MailItem = OutLookApp.CreateItem(0)’ : EM
*Add recipients to the mail item
ARG = ADDRESS; GOSUB 100
N = DCOUNT(ARG, AM)
FOR I=1 TO N
SCRIPT = SCRIPT:’Set Recipient = Mailitem.Recipients.Add(“‘:ARG<I>:’”)’:EM
SCRIPT = SCRIPT:’If Not Recipient.Resolve Then’: EM
SCRIPT = SCRIPT : ‘ MsgBox “The recipient did not check out!”‘ : EM
SCRIPT = SCRIPT : ‘ Exit Sub’ : EM
SCRIPT = SCRIPT : ‘End If’ : EM
Next I
* Add the subject
ARG = SUBJECT; GOSUB 100
SCRIPT = SCRIPT : ‘MailItem.Subject = “‘ : ARG : ‘”‘ : EM
* Add the message – lines are separated by attribute marks
ARG = MESSAGE; GOSUB 100
SCRIPT = SCRIPT : ‘Body = “‘ : ARG<1> : ‘”‘ : EM
N = DCOUNT(ARG, AM)
FOR I = 2 TO N

SCRIPT = SCRIPT : ‘Body = Body & Chr$(13) & Chr$(10) & “‘ : ARG<I> : ‘”‘ : EM

NEXT I
SCRIPT = SCRIPT : ‘MailItem.Body = Body’ : EM
* Add the attachment
IF ATTACH <> ” THEN

ARG = ATTACH; GOSUB 100
N = DCOUNT(ARG, AM)
FOR I = 1 TO N

SCRIPT = SCRIPT : ‘MailItem.Attachments.Add “‘ :ARG<I> : ‘”‘ : EM

NEXT

END

SCRIPT = SCRIPT : ‘MailItem.Send’ : EM
SCRIPT = SCRIPT : ‘Set Attachment = Nothing’ : EM
SCRIPT = SCRIPT : ‘Set Recipient = Nothing’ : EM
SCRIPT = SCRIPT : ‘Set MailItem = Nothing’ : EM
SCRIPT = SCRIPT : ‘Set MailItem = Nothing’
PRINT ESC : STX : ‘P’ : SCRIPT : CR :
RETURN
100 * Local subroutine to fixup embedded double-quote marks
K = 1
LOOP

J = INDEX(ARG, ‘”‘, K)

WHILE J DO

ARG = ARG[1, J] : ARG[J, 99999]
K = K + 2

REPEAT
RETURN

This subroutine has arguments for the Address, Subject, Body and attached file name. It then builds a custom script with the parameters hardcoded into it and downloads it to AccuTerm. The local subroutine (100) is included to handle the VB syntax for strings with embedded double-quote marks. Before any arguments (ADDRESS, SUBJECT, MESSAGE, ATTACH) are used in the VBA script, the double-quote fixup routine is called to repeat all embedded double-quote marks. All of the arguments except for SUBJECT can contain multiple attributes. This allows you to send the email message to more than one Address or attach more than one file at a time. The message body argument – MESSAGE should have attribute marks where you want the paragraph breaks to be.

Collaborative Data Objects – CDO

CDO is also an object model and in many ways, it’s similar to Outlook. It’s more difficult to work with than Outlook and as mentioned above, there are a number of different versions that ship with various Microsoft products. It’s more difficult to get the software installed and configured than Outlook. Microsoft has a knowledge base article on the subject at http://support.microsoft.com/support/kb/articles/Q171/4/40.asp. You’ll need to read it before you get started.

Given the complexity why use it? First of all, it takes less memory and runs faster. In this day and age of the Gigahertz processors and 256 megabyte memory chips, that reason is not very compelling. The main reason to use it is that you can do things with CDO that are impossible with Outlook. One of the limitations of Outlook is that you can only run one session. To understand why this is a limitation, lets take another example.

One of our clients has a large weekly payroll. Almost four hundred of their employees were paid by direct deposit where the money is deposited directly into their bank account instead of issuing a check. The employees still needed to know how much they were being paid so every week the client would print out and mail 400 deposit notices. Since all of the employees had a company Email account, it would be much more efficient to just Email the deposit notices instead of printing them.

The first rev of the software used the Outlook object model. The problem was, they wanted the employees to receive the Email from the “AutoDeposit” system, not from the payroll clerk who happened to be processing the notices. With the Outlook model, if the payroll administrator (let’s call her Jane Doe) processed the notices, the employees would receive their notice from “Jane Doe”. If next week, her assistant took over (Janet Doe), the notice would look like it was coming from her. It would be nice if at the time the deposit notice program runs, it could logon with the AutoDeposit credentials so that no matter who ran the payroll, the deposit notice would always be coming from the right person.

This is where CDO comes in. With CDO, your application can connect to a mail server and log in with any set of credentials. Since CDO can run multiple sessions at the same time, you can have one application running that’s logged on as AutoDeposit and another logged on as Jane Doe. Each app will be independent of the others. This is currently impossible with the Outlook object model.

The script to send an Email with CDO is similar to Outlook except that instead of an Outlook Application, we create a MailSession object. In addition, we need to tell the MailSession object to logon to a specific mail server with some credentials. Here’s the code:

Dim MailSession As Object ‘MAPI.Session
Dim MailItem As Object ‘MAPI.Message
Dim Recipient As Object ‘MAPI.Recipient
Dim Attachment As Object ‘MAPI.Attachment 

‘Create the mail session
Set MailSession = CreateObject(“MAPI.Session”)

‘Connect to our mail server and a mailbox for this example
‘we’re hard coding both
MailSession.Logon ShowDialog:=False, NewSession:=True, _

Profileinfo:=”ExchangeServer” & Chr$(10) & “AutoSend”

‘Create the actual email item
Set MailItem = MailSession.Outbox.Messages.Add

‘Add a recipient
Set Recipient = MailItem.Recipients.Add

‘Put in the email address we passed in
Recipient.Name = “joe@asent.com”

‘Do some initial checking of the address
On Error Resume Next
Recipient.Resolve False
If Err.Number <> 0 Then

MsgBox “The recipient didn’t check out!”

Exit Sub

End If

‘Put in the subject and message body
MailItem.Subject = “This is the subject”
MailItem.Text = “This is the message body”

‘Add an attachment
Set Attachment = MailItem.Attachments.Add
Attachment.Type = 1 ‘ActMsgFileData
Attachment.Source = “c:\test.txt”
Attachment.ReadFromFile “c:\test.txt”

‘Send it
MailItem.Send

‘Clean up
Set Attachment = Nothing
Set Recipient = Nothing
Set MailItem = Nothing
‘Be sure to logoff before we delete our session object
MailSession.Logoff
Set MailItem = Nothing

If you’ve been following along, you’ll see a few differences between this script and the Outlook version. The main one is the logon line:

MailSession.Logon ShowDialog:=False, NewSession:=True, _

Profileinfo:=”ExchangeServer” & Chr(10) & “AutoSend”

This is the line that lets us connect and send mail from any mailbox on any Exchange Server that we have permission to access. In this case, the computer actually running the Exchange Server mail software is called “ExchangeServer”. It has a mailbox called “AutoSend” to which we happen to have access. In your case, you’ll need to change these names. One source of problems is making sure the user has access to the AutoSend mailbox. How this is done varies depending on which version of Exchange you’re using and it gets much too complicated to go into here.

Another difference is in the attachment code:

Set Attachment = MailItem.Attachments.Add
Attachment.Type = 1 ‘ActMsgFileData
Attachment.Source = “c:\test.txt”
Attachment.ReadFromFile “c:\test.txt”

With Outlook, we just had to add the attachment and give it a filename as part of the Add method. With CDO, we have to first create the attachment then specify the Type, Source, and ReadFromFile properties. They are all required. This is a good example of how CDO is more complicated than outlook. Another important difference is the requirement to Logoff at the end.

Ok, here’s the PICK subroutine for CDO:

SUBROUTINE AT.SEND.EMAIL.CDO(MAILSERVER, MAILBOX, ADDRESS, SUBJECT, MESSAGE, ATTACH)
*Send email from host using AccuTerm and CDO
* ADDRESS: recipient’s email address
* SUBJECT: one-line email subject
* MESSAGE: multi-line email message (lines separated by AM)
* ATTACH: optional attachment file name
EQU AM TO CHAR(254), VM TO CHAR(253), SVM TO CHAR(252)
EQU ESC TO CHAR(27), STX TO CHAR(2), CR TO CHAR(13)
EQU EM TO CHAR(25)
SCRIPT = ”
SCRIPT = SCRIPT : ‘Dim MailSession As Object’ : EM
SCRIPT = SCRIPT : ‘Dim MailItem As Object’ : EM
SCRIPT = SCRIPT : ‘Dim Recipient As Object’ : EM
SCRIPT = SCRIPT : ‘Dim Attachment As Object’ : EM
SCRIPT = SCRIPT : ‘Dim Body As String’ : EM
SCRIPT = SCRIPT : ‘Set MailSession = CreateObject(“MAPI.Session”)’ : EM
SCRIPT = SCRIPT : ‘MailSession.Logon ShowDialog:=False, NewSession:=True,’
SCRIPT = SCRIPT : ‘ Profileinfo:=”‘ : MAILSERVER : ‘” & Chr$(10) & “‘ : MAILBOX : ‘”‘ : EM
SCRIPT = SCRIPT : ‘Set MailItem = MailSession.Outbox.Messages.Add’ : EM
*ADD THE RECIPIENTS
ARG = ADDRESS; GOSUB 100
N = DCOUNT(ARG, AM)
FOR I = 1 TO N

SCRIPT = SCRIPT : ‘Set Recipient = MailItem.Recipients.Add’ : EM

SCRIPT = SCRIPT : ‘Recipient.Name = “‘ : ARG<I> : ‘”‘ : EM

SCRIPT = SCRIPT : ‘On Error Resume Next’ : EM

SCRIPT = SCRIPT : ‘Recipient.Resolve False’ : EM

SCRIPT = SCRIPT : ‘If Err.Number <> 0 Then’ : EM

SCRIPT = SCRIPT : ‘ MsgBox “The recipient did not check out!”‘ : EM

SCRIPT = SCRIPT : ‘ Exit Sub’ : EM

SCRIPT = SCRIPT : ‘End If’ : EM

NEXT I
ARG = SUBJECT; GOSUB 100
SCRIPT = SCRIPT : ‘MailItem.Subject = “‘ : ARG : ‘”‘ : EM
ARG = MESSAGE; GOSUB 100
SCRIPT = SCRIPT : ‘Body = “‘ : ARG<1> : ‘”‘ : EM
N = DCOUNT(ARG, AM)
FOR I = 2 TO N

SCRIPT = SCRIPT : ‘Body = Body & Chr$(13) & Chr$(10) & “‘ : ARG<I> : ‘”‘ : EM

NEXT I
SCRIPT = SCRIPT : ‘MailItem.Text = Body’ : EM
IF ATTACH <> ” THEN

ARG = ATTACH; GOSUB 100
N = DCOUNT(ARG, AM)
FOR I = 1 TO N

SCRIPT = SCRIPT : ‘Set Attachment = MailItem.Attachments.Add’ : EM
SCRIPT = SCRIPT : ‘Attachment.Type = 1′ : EM
SCRIPT = SCRIPT : ‘Attachment.Source = “‘ : ARG<I> : ‘”‘ : EM
SCRIPT = SCRIPT : ‘Attachment.ReadFromFile “‘ : ARG<I> : ‘”‘ :EM

NEXT I

END
SCRIPT = SCRIPT : ‘MailItem.Send’ : EM
SCRIPT = SCRIPT : ‘Set Attachment = Nothing’ : EM
SCRIPT = SCRIPT : ‘Set Recipient = Nothing’ : EM
SCRIPT = SCRIPT : ‘Set MailItem = Nothing’ : EM
SCRIPT = SCRIPT : ‘MailSession.Logoff’ : EM
SCRIPT = SCRIPT : ‘Set MailItem = Nothing’
PRINT ESC : STX : ‘P’ : SCRIPT : CR :
RETURN
100 * Local subroutine to fixup embedded double-quote marks
K = 1
LOOP

J = INDEX(ARG, ‘”‘, K)

WHILE J DO

ARG = ARG[1, J] : ARG[J, 99999]
K = K + 2

REPEAT

RETURN

It works the same as the Outlook routine except that it has two additional parameters – MAILSERVER and MAILBOX.

There are many other differences that make CDO harder to work with. If you need to use CDO, this is how you do it. My advice is to avoid it if you can.

The MAPI method

MAPI is one of the oldest technologies available to send Email in Windows. While not as robust as Outlook or CDO, MAPI services are available when using alternative Email clients such as Outlook Express and Eudora.

Unlike Outlook and CDO, which supply a rich object model, Simple MAPI is an API–not an object model. What is an API? API stands for “Application Programming Interface”, and is simply a set of subroutine and data structure definitions that can be called from a programming language. API’s are usually implemented in DLL’s and in order to use API functions in a DLL, the functions must be DECLAREd. Once a function is declared, it becomes available to your routine as if it was a built into the language. The syntax for declaring functions is the subject of an entire book. It’s too big to address in this article. For more information on accessing the Windows API functions, you might want to check out Dan Appleman’s book, “The Visual Programmer’s Guide to the Win32 API” ISBN 0-672-31590-4. For now, just take the declaration of the MAPISendMail function below on faith.

While it is possible to declare DLL functions in AccuTerm’s scripting language (when invoked from the host), it is not straightforward. The problem is that before AccuTerm can execute a script which is transmitted from the host, it must enclose the script between Sub Main() and End Sub statements. But all DECLARE statements are “global” and need to reside outside of any Subs or Functions. They normally occur before the first Sub or Function.

When executing the script from the host you have no access to code before the Sub Main() statement, so where do you place the DECLARE statements? The answer relies on knowing that AccuTerm will add Sub Main() to the beginning of the code you send and End Sub to the end. There is nothing that prevents you from adding your own End Sub and Sub xxxx statements into the code. For example, look at the following script:

Sub Main ()

Call AnotherSub

End Sub
Sub AnotherSub ()

‘ Do something

End Sub

If you want AccuTerm to execute the preceding script from the host, you need to send all lines between (but not including) the first and last lines.

Using this information, we now know that we can insert any DECLARE statements between our own End Sub / Sub xxxx statements.

Sending Email using Simple MAPI requires only one function: MAPISendMail, which has the following definition:

Declare Function MAPISendMail Lib “MAPI32.DLL” (ByVal Session&, ByVal UIParam&, Message As MapiMessage, ByVal Flags&, ByVal Reserved&) As Long

Reviewing the MAPI documentation reveals that for automated Email messages only the Message parameter is required. All other parameters can be left as zero.  However, the Message parameter is a structure, which in VBA, is defined using a “User Defined Type”, or UDT. A user defined type is just a fancy way of saying “there’s a block of memory and it breaks down into individual pieces like this”. As an example, the definition of MapiMessage looks like this:

Type MapiMessage

Reserved As Long
Subject As String
NoteText As String
MessageType As String
DateReceived As String
ConversationID As String
Flags As Long
Originator As MapiRecip ‘address of originator structure
RecipCount As Long
Recipients As MapiRecip ‘address of first element of array of recipients
FileCount As Long
Files As MapiFile ‘address of first element of array of attached files

End Type

To use the UDT, we declare a variable of that type. For example, we can use the MapiMessage structure above to create a variable that holds all the information with this statement:

Dim msg As MapiMessage

This creates a variable “msg” of type MapiMessage. We can reference the different elements of this type by using the dot operator. To change the Subject of the msg variable, we’d do this:

Msg.Subject = “This is the subject”

There are some other structures we’ll need: MapiRecip and MapiFile. They’re defined as:

Type MapiRecip

Reserved As Long
RecipClass As Long
Name As String
Address As String
EIDSize As Long
EntryID As String

End Type

Type MapiFile

Reserved As Long
Flags As Long
Position As Long
PathName As String
FileName As String
FileType As String

End Type

Some of the things passed to the MapiSendMail function are passed by reference. That means that the memory address of the variable is passed to the routine, not the value of the variable itself. So the next “trick” we need to employ is how to get the address of a variable or array? The answer is the undocumented VarPtr() function, which returns the address of its argument. For this to work, we need to change the data type in the MapiMessage definition for the Originator, Recipients and Files members from MapiRecip or MapiFile to Long, the data type returned by the VarPtr() function.

Finally, we need to translate any strings in MapiRecip or MapiFile structures from Unicode to ANSI before calling MAPISendMail. The reason for this is very complex and stems from the fact that AccuTerm’s scripting environment uses Unicode to represent strings, and most API’s (including Simple MAPI) expect strings in ANSI. Conversion of strings in the MapiMessage structure is handled automatically. But as far as our scripting environment is concerned, the Originator, Recipients and Files elements are simply Long integers and no conversion of their members will take place automatically.

So how do you use MAPISendMail to send an Email message to a recipient? Basically, all you need to do is fill in the MapiMessage structure and call MAPISendMail. The MapiMessage structure has elements (fields) for Subject, NoteText (message body), Originator, Recipients and Files (among others). It turns out that we usually don’t need to specify the Originator (this will be filled in by your MAPI Email client program (Outlook Express, Eudora, etc.) The Recipients element is the address of an array of MapiRecip structures; RecipientCount is the number of items in the Recipients array. The Files element is the address of an array of MapiFile structures; FileCount is the number of items in the Files array.

In our sample Email routine we will assume a single recipient and optionally one attachment, which simplifies the Recipients and Files arrays to be a single variable each. It is trivial to fill in the Subject and NoteText fields. As described above, we use the VarPtr() function to fill in the address of the Recipients and Files fields. Which leaves only the task of filling in the one MapiRecip and possibly the MapiFile structures.

To initialize the MapiRecip structure, we need to fill in three fields: Name, Address and RecipClass. The Name field is the “display” name, and is required for some versions of Outlook. In this sample, we simply use the first part of the Email address for the name. The Address field is the recipient’s Email address, preceded by the string “smtp:”. Remember that both Name and Address need to be converted to ANSI. Finally, the RecipClass field is set to 1, designating the recipient as the “To recipient” (as opposed to CC, BCC and Sender recipient classes).

If an attachment is to be included with the Email message, a MapiFile structure is required. The MapiFile structure requires only two fields: Position and PathName. Set Position to –1 and PathName to the full path of the attachment file (converted to ANSI).

Based on the preceding discussion, here is a sample VBA script to send an Email message using Simple MAPI:

Sub Main()

SendMessage

End Sub

 

Type MapiMessage

Reserved As Long
Subject As String
NoteText As String
MessageType As String
DateReceived As String
ConversationID As String
Flags As Long
Originator As Long ‘address of Originator
RecipCount As Long
Recipients As Long ‘address of Recipients array
FileCount As Long
Files As Long ‘address of Files array

End Type

 

Type MapiRecip

Reserved As Long
RecipClass As Long
Name As String
Address As String
EIDSize As Long
EntryID As String

End Type

 

Type MapiFile

Reserved As Long
Flags As Long
Position As Long
PathName As String
FileName As String
FileType As String

End Type

 

Declare Function MAPISendMail Lib “MAPI32.DLL” (ByVal Session&, ByVal UIParam&, Message As MapiMessage, ByVal Flags&, ByVal Reserved&) As Long

 

Sub SendMessage()

 

Dim rc As Long
Dim msg As MapiMessage
Dim recip(1) As MapiRecip
Dim attach(1) As MapiFile

 

recip(1).Name = StrConv(“pjs”,vbFromUnicode)
recip(1).Address = StrConv(“smtp:pjs@asent.com”, vbFromUnicode)
recip(1).RecipClass = 1 ‘To

 

attach(1).Position = -1 ‘end?
attach(1).Pathname = StrConv(“c:\temp\test.txt”,vbFromUnicode)

 

msg.Subject = “test subject”
msg.NoteText = “this is the message body”
msg.RecipCount = 1
msg.Recipients = VarPtr(recip(1))
msg.FileCount = 1
msg.Files = VarPtr(attach(1))

  

rc = MAPISendMail(0, 0, msg, 0, 0)

 

If rc <> 0 Then

MsgBox “Email failed; code = ” & CStr(rc)

End If

 

End Sub

We can now test the above code by pasting it into the AccuTerm scripting window and stepping through the code. Be sure to change the Email addresses and the attached file name to something appropriate to your system.

Here’s the Pick subroutine for the MAPI version:

SUBROUTINE AT.SEND.EMAIL.MAPI(ADDRESS, SUBJECT, MESSAGE, ATTACH)
* 05/14/01 02:00PM
* ADDRESS: recipient’s email address
* SUBJECT: one-line email subject
* MESSAGE: multi-line email message (lines separated by AM)
* ATTACH: optional attachment file name
EQU AM TO CHAR(254), VM TO CHAR(253), SVM TO CHAR(252)
EQU ESC TO CHAR(27), STX TO CHAR(2), CR TO CHAR(13)
EQU EM TO CHAR(25)
RECIP.CNT = DCOUNT(ADDRESS, AM)
ATTACH.CNT = DCOUNT(ATTACH, AM)
*
SCRIPT = ”
SCRIPT = SCRIPT : ‘SendMessage’ : EM
SCRIPT = SCRIPT : ‘End Sub’ : EM
SCRIPT = SCRIPT : ‘Type MapiMessage’ : EM
SCRIPT = SCRIPT : ‘Reserved As Long’ : EM
SCRIPT = SCRIPT : ‘Subject As String’ : EM
SCRIPT = SCRIPT : ‘NoteText As String’ : EM
SCRIPT = SCRIPT : ‘MessageType As String’ : EM
SCRIPT = SCRIPT : ‘DateReceived As String’ : EM
SCRIPT = SCRIPT : ‘ConversationID As String’ : EM
SCRIPT = SCRIPT : ‘Flags As Long’ : EM
SCRIPT = SCRIPT : ‘Originator As Long’ : EM
SCRIPT = SCRIPT : ‘RecipCount As Long’ : EM
SCRIPT = SCRIPT : ‘Recipients As Long’ : EM
SCRIPT = SCRIPT : ‘FileCount As Long’ : EM
SCRIPT = SCRIPT : ‘Files As Long’ : EM
SCRIPT = SCRIPT : ‘End Type’ : EM
SCRIPT = SCRIPT : ‘Type MapiRecip’ : EM
SCRIPT = SCRIPT : ‘Reserved As Long’ : EM
SCRIPT = SCRIPT : ‘RecipClass As Long’ : EM
SCRIPT = SCRIPT : ‘Name As String’ : EM
SCRIPT = SCRIPT : ‘Address As String’ : EM
SCRIPT = SCRIPT : ‘EIDSize As Long’ : EM
SCRIPT = SCRIPT : ‘EntryID As String’ : EM
SCRIPT = SCRIPT : ‘End Type’ : EM
SCRIPT = SCRIPT : ‘Type MapiFile’ : EM
SCRIPT = SCRIPT : ‘Reserved As Long’ : EM
SCRIPT = SCRIPT : ‘Flags As Long’ : EM
SCRIPT = SCRIPT : ‘Position As Long’ : EM
SCRIPT = SCRIPT : ‘PathName As String’ : EM
SCRIPT = SCRIPT : ‘FileName As String’ : EM
SCRIPT = SCRIPT : ‘FileType As String’ : EM
SCRIPT = SCRIPT : ‘End Type’ : EM
SCRIPT = SCRIPT : ‘Declare Function MAPISendMail Lib “MAPI32.DLL”‘
SCRIPT = SCRIPT : ‘ (ByVal Session&, ByVal UIParam&,’
SCRIPT = SCRIPT : ‘ Message As MapiMessage, ByVal Flags&,’
SCRIPT = SCRIPT : ‘ ByVal Reserved&) As Long’ : EM
SCRIPT = SCRIPT : ‘Sub SendMessage()’ : EM
SCRIPT = SCRIPT : ‘Dim rc As Long’ : EM
SCRIPT = SCRIPT : ‘Dim msg As MapiMessage’ : EM
IF RECIP.CNT = 1 THEN

SCRIPT = SCRIPT : ‘Dim recip As MapiRecip’ : EM

END ELSE

SCRIPT = SCRIPT : ‘Dim recip(‘:RECIP.CNT – 1:’) as MapiRecip’ :EM

END
IF ATTACH <> “” THEN

IF ATTACH.CNT = 1 THEN

SCRIPT = SCRIPT : ‘Dim attach As MapiFile’ : EM

END ELSE

SCRIPT = SCRIPT : ‘Dim attach(‘: ATTACH.CNT – 1:’) as MapiFile’: EM

END

END
SCRIPT = SCRIPT : ‘Dim body As String’ : EM
IF RECIP.CNT = 1 THEN

SCRIPT = SCRIPT : ‘recip.Name = StrConv(“‘ : FIELD(ADDRESS,’@’,1) : ‘”,vbFromUnicode)’ : EM
SCRIPT = SCRIPT : ‘recip.Address = StrConv(“smtp:’ : ADDRESS : ‘”, vbFromUnicode)’ : EM
SCRIPT = SCRIPT : ‘recip.RecipClass = 1′ : EM

END ELSE

FOR I=1 TO RECIP.CNT

SCRIPT = SCRIPT : ‘recip(‘:I-1:’).Name = StrConv(“‘ : ADDRESS<I> : ‘”,vbFromUnicode)’ : EM
SCRIPT = SCRIPT : ‘recip(‘:I-1:’).Address = StrConv(“smtp:’ : ADDRESS<I> : ‘”, vbFromUnicode)’ : EM
SCRIPT = SCRIPT : ‘recip(‘:I-1:’).RecipClass = 1′ : EM

NEXT I

END
IF ATTACH <> ” THEN

IF ATTACH.CNT = 1 THEN

SCRIPT = SCRIPT : ‘attach.Position = -1′ : EM
SCRIPT = SCRIPT : ‘attach.Pathname = StrConv(“‘ : ATTACH : ‘”,vbFromUnicode)’ : EM

END ELSE

FOR I = 1 TO ATTACH.CNT

SCRIPT = SCRIPT : ‘attach(‘:I-1:’).Position = -1′ : EM
SCRIPT = SCRIPT : ‘attach(‘:I-1:’).Pathname = StrConv(“‘ : ATTACH<I> : ‘”,vbFromUnicode)’ : EM

NEXT I

END

END
ARG = SUBJECT; GOSUB 100
SCRIPT = SCRIPT : ‘msg.Subject = “‘ : ARG : ‘”‘ : EM
ARG = MESSAGE; GOSUB 100
SCRIPT = SCRIPT : ‘Body = “‘ : ARG<1> : ‘”‘ : EM
N = DCOUNT(ARG, AM)
FOR I = 2 TO N

SCRIPT = SCRIPT : ‘Body = Body & Chr$(13) & Chr$(10) & “‘ : ARG<I> : ‘”‘ : EM

NEXT I
SCRIPT = SCRIPT : ‘msg.NoteText = Body’ : EM
SCRIPT = SCRIPT : ‘msg.RecipCount = ‘:RECIP.CNT : EM
IF RECIP.CNT = 1 THEN

SCRIPT = SCRIPT : ‘msg.Recipients = VarPtr(recip)’ : EM

END ELSE

SCRIPT = SCRIPT : ‘msg.Recipients = VarPtr(recip(0))’ : EM

END
IF ATTACH <> ” THEN

SCRIPT = SCRIPT : ‘msg.FileCount = ‘:ATTACH.CNT : EM
If ATTACH.CNT = 1 THEN

SCRIPT = SCRIPT : ‘msg.Files = VarPtr(attach)’ : EM

END ELSE

SCRIPT = SCRIPT : ‘msg.Files = VarPtr(attach(0))’ : EM

END

END
SCRIPT = SCRIPT : ‘rc = MAPISendMail(0, 0, msg, 0, 0)’ : EM
SCRIPT = SCRIPT : ‘If rc <> 0 Then’ : EM
SCRIPT = SCRIPT : ‘MsgBox “Email failed; code = ” & CStr(rc)’ : EM
SCRIPT = SCRIPT : ‘End If’
*
PRINT ESC : STX : ‘P’ : SCRIPT : CR :
RETURN
*

100 * Local subroutine to fixup embedded double-quote marks

K = 1
LOOP

J = INDEX(ARG, ‘”‘, K)

WHILE J DO

ARG = ARG[1, J] : ARG[J, 99999]
K = K + 2

REPEAT
RETURN

This script has been tested with Outlook Express, Outlook 98, Outlook 2000 and Eudora Pro.  You may need to adjust you Email client configuration to get things working the way you want.

The SMTP method

SMTP is the protocol used by email clients to send email. By using the SMTP protocol directly, no email client is required, however, your network and ISP must permit sending email using this protocol. Many SMTP servers (usually specified by your ISP, but alternatives such as smtp.gmail.com are also possible) require authentication and/or security (TLS/SSL) before they will accept an outgoing message.

In this example, we are going to use a command-line SMTP email utility called “mailsend”. Mailsend is similar to another popular command-line emailer called “blat”, however, mailsend supports TLS & SSL security, which is required for some SMTP mail servers (gmail for example). Mailsend is a free, open source utility written by Muhammad A. Muquit. You can download the program and view the documentation from this Google Code link.

The AT.SEND.EMAIL.SMTP subroutine shown below builds a command line, with appropriate arguments for the mailsend program. Then it calls the DOSSVC subroutine (part of AccuTerm’s FTBP host program library) to download the message text, execute the command line and upload a status file indicating whether the operation was successful or not.

The AT.SEND.EMAIL.SMTP subroutine requires arguments for the recipient address(es), the email subject, the message text (plaintext or html), attachments and an SMTP configuration record. The layout of the configuration record is documented in the code comments. If you are using gmail as the SMTP server, security and authentication are required. You must supply a valid gmail user name and password on lines 8 & 9, and set lines 3 & 4 to 1. Use “smtp.gmail.com” as the mail server on line 1 and port 465 (SMTP over SSL) on line 2.

Here’s the code:

SUBROUTINE AT.SEND.EMAIL.SMTP(ADDRESS, SUBJECT, MESSAGE, ATTACH, CONFIG, STAT)
*
EQU AM TO CHAR(254), VM TO CHAR(253), SVM TO CHAR(252)
*
* Send email message using SMTP mail server
*
* This routine uses the free open-source command-line email sender ‘mailsend.exe’ created,
* by Muhammad Muquit, hosted on Google Code at http://code.google.com/p/mailsend.
* To use this routine, you need to pass a congiruation record (dynamic array) with your
* SMTP server configuration (name or IP address, port, user name, etc.) Other arguments
* specify the recipient address list, subject, message body and attachment list. The
* result of the operation is returned in the STAT argument.
*
* This routine uses AccuTerm’s DOSSVC subroutine to download the email message body,
* invoke the mailsend.exe program, and upload any errors returned by mailsend.exe.
* A command window is displayed while the message is being sent.
*
* ADDRESS: recipient’s email address (multiple addresses separated by AM)
* SUBJECT: one-line email subject (optional)
* MESSAGE: multi-line email message (plain text or html, lines separated by AM)
* ATTACH: optional attachment file path(s)
* CONFIG: dynamic array containing mail server configuration
* STAT: retuned status – NULL on success, otherwise error message from mailsend.exe
*
*
* CONFIG dynamic array:
*
SMTP = CONFIG<1> ;* smtp server name or IP address (smtp.gmail.com)
PORT = CONFIG<2> ;* 25 (no security), 465 (SSL) or 587 (STARTTLS)
AUTH = CONFIG<3> ;* 0 = no authentication, 1 = use authentication
SECURE = CONFIG<4> ;* 0 = none, 1 = SSL, 2 = STARTTLS
SENDER = CONFIG<5> ;* sender’s email address
NAME = CONFIG<6> ;* sender’s name (optional)
REPLY = CONFIG<7> ;* reply-to email address (optional)
USER = CONFIG<8> ;* user ID for authentication
PSWD = CONFIG<9> ;* password for authentication
PATH = CONFIG<10> ;* path for mailsend directory
*
PROG = ‘mailsend.exe’ ;* command-line program to send email (http://www.muquit.com/muquit/software/mailsend/mailsend.html)
*
* set up paths for message & result files
*
USER.NO = FIELD(OCONV(”, ‘U50BB’),’ ‘,1) ;* we need a unique filename
IF PATH <> ” THEN IF PATH[LEN(PATH),1] <> ‘\’ THEN PATH=PATH:’\’
MSGFILE = ‘MSG’ : USER.NO : ‘.TXT’
RSLTFILE = ‘RSLT’ : USER.NO : ‘.TXT’
*
* is message body text or html?
*
FIRSTLINE = OCONV(MESSAGE<1>, ‘MCU’)
IF FIRSTLINE[1, 5] = ‘<HTML’ OR FIRSTLINE[1, 14] = ‘<!DOCTYPE HTML’ THEN\

MIME = ‘text/html’

END ELSE

MIME = ‘text/plain’

END
*
* build the mailsend command line
*
COMMAND=’CMD.EXE /s /c “”‘:PATH:PROG:'”‘
COMMAND=COMMAND:’ -from ‘:SENDER
IF NAME <> ” THEN

COMMAND=COMMAND:’ -name “‘:NAME:'”‘

END
N = DCOUNT(ADDRESS,AM)
COMMAND=COMMAND:’ -to ‘
FOR I=1 TO N

COMMAND=COMMAND:ADDRESS<I>
IF I < N THEN COMMAND=COMMAND:’,’

NEXT I
IF REPLY <> ” THEN

COMMAND=COMMAND:’ -rt ‘:REPLY

END
COMMAND=COMMAND:’ -smtp ‘:SMTP
IF PORT <> ” THEN

COMMAND=COMMAND:’ -port ‘:PORT

END
IF SUBJECT <> ” THEN

ARG=SUBJECT; GOSUB 100
COMMAND=COMMAND : ‘ -sub “‘:ARG:'”‘

END
IF MESSAGE <> ” THEN

COMMAND=COMMAND:’ -attach “‘:PATH:MSGFILE:’,’:MIME:’,i”‘ ;* first inline attachment is message body

END
N = DCOUNT(ATTACH,AM)
FOR I = 1 TO N

COMMAND=COMMAND : ‘ -attach “‘:ATTACH<I>:'”‘

NEXT I
IF AUTH THEN

COMMAND=COMMAND:’ -auth’

END
IF SECURE=1 THEN

COMMAND=COMMAND:’ -ssl’

END
IF SECURE=2 THEN

COMMAND=COMMAND:’ -starttls’

END
IF USER <> ” THEN

COMMAND=COMMAND:’ -user “‘:USER:'”‘

END
IF PSWD <> ” THEN

COMMAND=COMMAND:’ -pass “‘:PSWD:'”‘

END
COMMAND=COMMAND:’ +cc +bc > “‘:PATH:RSLTFILE:'” 2>&1″‘
*
* call DOSSVC to download the message, invoke the mailsend command, and upload the result
*
CALL DOSSVC(COMMAND, PATH, MSGFILE, MESSAGE, PATH, RSLTFILE, RESULT)
*
* check result
*
FOR I=LEN(RESULT) TO 1 STEP -1

IF RESULT[I,1] = AM THEN RESULT=RESULT[1,I-1] ELSE I=0

NEXT I
IF RESULT = ” THEN

STAT=” ;* NULL indicates success

END ELSE

STAT=RESULT ;* return error message

END
*
RETURN
*
100 * Local subroutine to remove embedded double-quote marks
LOOP J=INDEX(ARG,'”‘,1) WHILE J DO ARG=ARG[1,J-1]:ARG[J+1,99999] REPEAT
RETURN
*
END

Summary

That’s it, four different ways to accomplish the same exact thing. Of course it’s never that simple. There are lots of little quirks. In writing this article we found differences between the way Outlook Express and Outlook 98/2000 handle Email addresses using the MAPI routine. There were also differences between the way Eudora Express and Pro handled them. Development these days seems to require a lot of trial and error but with a little experimentation and a little luck you should be Emailing from you MultiValue application in no time!

Since the original publication of this article, Microsoft has increased security in Outlook, which causes a security warning whenever an external application uses the Outlook object model to send an email. One possible solution to this annoying enhancement is a product called Advanced Security for Outlook from MapiLab (www.mapilab.com/outlook/security).

If you have questions or comments on this article, please send them to Peter Schellenbach, accuterm@zumasys.com. The sample Pick/BASIC routines may be downloaded from the Code Samples page.

Return to Technical Articles