Automating E-Mail With AccuTerm

AccuSoft Support

Automating Email with AccuTerm

By Peter Schellenbach and Joe Goldthwaite

page < 1 - 2 >

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 customer's calls with a request fopress a button and have your MV app automatically Email the information to the customerr some information. You'd like to be able to . 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 three different methods: Outlook, Collaborative Data Objects (CDO), and MAPI. Whichever method you choose, you'll need to have your Windows based Email client configured and working first. This is because each of the above methods use your current Email client to perform the actual email send operation. If you don't have Email working on the client computer, none of these methods will 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 and AccuTerm 2K2.

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 MailItem.Recipients(1).Name

Debug.Print MailItem.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 2K2 (or 2000) 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

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 hard coded 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 mesasge 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 printout 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 here 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

page < 1 - 2 >