|
Summer 2000
|
With most terminal emulators and SB+ now supporting desktop integration through dynamic data exchange (DDE), it should be easy to send email from a MV application. Sadly, if you use Microsoft Outlook, it isn’t quite as straightforward as it ought to be!
Outlook doesn’t support DDE – as Microsoft puts it, “From a solution developer’s perspective, Outlook does not support the use of Dynamic Data Exchange (DDE).” So we need to bring the Outlook object model into a DDE-friendly environment if we want to continue to use DDE with a fairly standard MV toolkit.
An effective route is to use Visual Basic to broker message content from MV and to control Outlook. If all you have to hand are Microsoft Office products you can still do the job using DDE from MV to an Office application – Excel, for instance – and use VBA to manage the Outlook session. It’s almost more complicated to explain what you need to do than it is to actually do it!
Taking advantage of VBA in Excel, the MV routine needs to address five requirements:
These steps are shown as five subroutines below, using ViaDuct for Windows syntax as an example.
The data can, quite obviously, be entered by a user or read from file. Ideally they should be validated before being presented to Outlook. Recipient addresses should be in the familiar mailbox_name@mailserver_url format. Here the INITIALISE subroutine places explicit strings into the three variables.
INITIALISE:
* ========
EQU VM TO CHAR(254)
PROMPT “”
PCCMD = CHAR(27) : CHAR(8)
ENDCMD = CHAR(0) ; *NB MAY BE CHAR(1)
RECIPIENT = “angus.mcnutter@hohoho.com”
SUBJECT = “This is the subject line”
BODY.TEXT = “This text forms the body of my email message”
RETURN
START.DDE:
* =======
CRT PCCMD :
CRT “DDE /R INITIATE EXCEL EMAIL.XLS” :
CRT ENDCMD :
INPUT DDE.REPLY :
IF DDE.REPLY[01,01] = “0” THEN
* OK TO CONTINUE WITH DATA TRANSFER
END ELSE
* HANDLE FAILURE
END
RETURN
TRANSFER.DATA:
* ===========
*
* SEND RECIPIENT
*
CRT PCCMD :
CRT “DDE /R POKE R2C1” : ; * R2 C1 = CELL A2 IN EXCEL WORKSHEET
CRT RECIPIENT :
CRT ENDCMD :
INPUT DDE.REPLY :
*
* SEND SUBJECT LINE
*
CRT PCCMD :
CRT “DDE /R POKE R2C2” :; * R2 C2 = CELL B2
CRT SUBJECT :
CRT ENDCMD :
INPUT DDE.REPLY :
*
* SEND MESSAGE TEXT
*
CRT PCCMD :
CRT “DDE /R POKE R2C3” :; * R2 C3 = CELL C2
CRT BODY.TEXT :
CRT ENDCMD :
INPUT DDE.REPLY :
*
RETURN
RUN.MACRO:
* =======
CRT PCCMD :
CRT “DDE /R EXECUTE [Run(“ :
CRT QUOTE : “EMAIL.XLS!Send_Mail_Macro” : QUOTE :
|
The VBA routine (Send_Mail_Macro) has to read the passed data from cells A2, B2 and C2, place them into the Outlook object model and to send the email, achieved with the following code. Note that multiple recipients can be placed into column A of the worksheet, one email address per row, allowing batch mailings to be managed (the MV code above places only one recipient address on row two).
Sub Send_Mail_Macro()
‘
‘ Chris Pearson
‘ February 2000
‘
‘
Dim MailMessageText As String
Dim mailmessageSubject As String
Dim ThisRow As Integer
Dim AllDone As Integer
Dim Dummy As Variant
‘
‘ This sets up Outlook
‘
Set ol = CreateObject(“outlook.application”)
‘
‘ This reads the subject and message of the mail from
‘ the worksheet called Mail
‘
mailmessageSubject = Sheets(“Mail”).Cells(2, 2)
MailMessageText = Sheets(“Mail”).Cells(2, 3)
‘
‘ This reads each recipient from column A on the worksheet
‘ and carries on until a blank cell is reached
‘
AllDone = False
ThisRow = 2
Do Until AllDone
ThisRecipient = Cells(ThisRow, 1)
If ThisRecipient = “” Then ‘ End of list reached?
AllDone = True
Else
GoSub SendMessage
ThisRow = ThisRow + 1
End If
Loop
‘
‘ Email session complete - Wrap up
‘ (Probably remove this message box from a production version)
‘
Dummy = MsgBox(ThisRow - 2 & “ mail messages have been sent”, _
vbOKOnly, _
“Automatic email session completed”)
Set ol = Nothing
Exit Sub
‘
‘ Subroutine to mail one message
‘
SendMessage:
‘ ==========
‘
Set NewMail = ol.CreateItem(olMailItem)
Set receiverOfMyMail = NewMail.Recipients.Add(ThisRecipient)
NewMail.Subject = mailmessageSubject
NewMail.body = MailMessageText
NewMail.Send
Return
End Sub
|
Further information can be found on the Microsoft web site (http://www.microsoft.com). Look specifically for Q253989 - OL97: Outlook Does Not Support DDE as well as Q168095 – OL97: How to Automate Outlook from Another Program, Q166368 – OL97: How to Get Help Programming with Outlook and Q170783 – OL97: Q&A: Questions About Customising or Programming Outlook.
Chris Pearson
Last Updated: 28 Sep 2000