FUNCky & SMTP E-mail from within dBASE
by John Staub, President, Staub & Associates, Inc.

FUNCky is an excellent set of “third-party” tools for dBASE.  It has so many different and widely varied routines that can be easily included in a dBL app that we consider FUNCky a necessary part of our developer tool set.  We use it for FTP processes, sending single and bulk e-mails, testing for the existence and/or availability of certain drives, provide system information to users, etc., all from within dBL applications.

What is required?

Of course, you'll need a registered copy of FUNCky, available from the dBASE on-line store at http://www.dBase.com. If you are using any of the FUNCky routines in a client's application, you will need to install FUNCky.dll and FUNCky.TLB on their computers and/or server(s), and register FUNCky.dll on each machine.  These install and registration processes can be included in your installation program, and are especially easy to do using Inno (our preferred install package) or similar packages that allow you to run automated processes during the install.

Our User's Environment

In this article, we will use a fairly complex user environment as an example.  This particular client has a dBL app that we built and which runs across a LAN and on a Terminal Server,  serving 9 sales locations in 3 different countries.  The application is installed on each workstation on the LAN, and we configured the Terminal Server environment to accommodate multiple users.  They also have a separate e-mail server as part of the domain.

During the building of this application, one requirement was to be able to send e-mails to particular clients, and to be able to send bulk e-mails to a wide variety of clients.  Each client record contains two fields for e-mail addresses, and the application provides for filtering client records in a multitude of methods.  Additionally, we can send an e-mail to either or both e-mail addresses in the client record.  Each sales agent has the ability to filter records based upon whether the client is a “sold” customer, a prospective customer, the make/model of car they are interested in, etc.  This provides each sales agent the ability to “target” their e-mails, especially when sending bulk e-mails.   Additionally, following the successful sending of an e-mail, a client follow-up record is created, documenting the contact with each client.

In the first use of the bulk e-mail send process, the company sent out announcements for the unveiling of a new series automobile.   They were hosting a “coming out” party at each of their sales locations.   While I'm not sure the bulk e-mail played a major role in the overwhelming success of the event, it provided them the means to send an announcement to each prospective customer.  The attendance at this event was significantly greater than at previous events and advertising costs for the event were similarly reduced.

FUNCky SMTP plays a major role in our ability to send e-mails from within our application.

The E-mail Form

  The forms we use are pretty simple and straight-forward.  We include the name of the client, their e-mail address, a CC and BCC field, text, and the ability to add an attachment.  Most of the information for the form is gathered before we ever open the form from values in entryfields on the currently open form.  We call the form in the following manner:
 

if not empty(form.cust_entryfield12.value) //first e-mail address field on the client form   set procedure to one_email_send.wfm additive // one of our e-mail forms   oEmail = new one_email_sendform()   oEmail.EmailAddress = ' '   // check which email address to use if two e-mail addresses exist   if not empty(form.eCust_Address1.value) AND ;     not empty(form.eCust_Address2.value)  // both e-mail address fields on the calling form     set procedure to whichemailaddress.wfm additive     oWhich = new which_email_addressform()     oWhich.MDI = false     oWhich.first_email  = false     oWhich.second_email = false     oWhich.both_email   = false     oWhich.first_email_address  = form.eCust_Address1.value     oWhich.second_email_address = form.eCust_Address2.value     // when the form for determining which e-mail address to use opens, it displays     // both e-mail addresses and pushbuttons for First, Second or Both e-mail addresses     oWhich.ReadModal()     if oWhich.first_email       oEmail.EmailAddress = trim(form.eCust_Address1.value)  // the first e-mail address on the form     elseif oWhich.second_email       oEmail.EmailAddress = trim(form.eCust_Address2.value)  // the second e-mail address on the form     elseif oWhich.both_email       oEmail.EmailAddress = trim(form.eCust_Address1.value) + ;       "; " + trim(form.eCust_Address2.value)  // both e-mail addresses separated by a semi-colon     endif  // which e-mail address to use     // since we use Boolean values from the which_email_address form, closeout     // the form now as they are no longer needed     oWhich.Release()     oWhich = null     close procedure which_email_address.wfm   else   // only one e-mail address exists     oEmail.EmailAddress = trim(form.eCust_Address1.value)  // first e-mail address on the calling form   endif  // not empty both e-mail addresses   oEmail.ClientNumber = form.eClient_Number.value  // clientnumber   oEmail.MDI = false   oEmail.ReadModal()   oEmail.Release()   oEmail = null   close procedure one_email_send.wfm else  // no e-mail address is on record   MSGBOX("There is not a valid e-mail address for this record","No valid e-mail address") endif return

Instantiating and opening the form is a pretty simple and straight-forward process.  We check to see if there is an e-mail address for this client record, then we give the user the option of using the first, second or both addresses if two addresses exist. The ClientNumber is a unique record identifier (AutoIncrement field) and is used in the actual e-mail form so we can create a follow-up record for this client if the e-mail is sent.  One thing you may want to note is how we use variables from two different forms to handle specific information and to determine which e-mail address to use.  If only one e-mail address is on record, we just grab that value from the client form's entryfield and use it.  If no e-mail address is in this client's record, we notify the user and close out the process

In the on_Open() event of the e-mail form, we insert values into several  fields on the e-mail form.  For a variety of reasons, we use disabled entryfields on the actual e-mail form to store the To, From and BCC addresses. Additionally, we create a form.variable to hold the information for any attachments a user may want to include with the line form.attachments = ' '

If the users want to include an attachment, when they click the “Attach” button, the following code is run:
 

	form.attachments = iif(empty(form.attachments), null, form.attachments + ", ") ;    + getfile("*.doc;*.xls;*.bmp;*.jpg;*.jpeg;*.gif") form.editor2.enabled = true form.editor2.value = ' ' form.editor2.value = form.attachments return

The real FUNCky work starts when the user clicks the Send button:
 

set procedure to Funcky.cc additive local Smtp Smtp = New FUNCkySmtp() Smtp.Server = "mymail_server.com" // IP addresses can also be used Smtp.FromName = form.eAgent_Name.value Smtp.FromAddress = form.eFrom_Address.value Smtp.Subject = form.eSubject.value Smtp.ToName = form.eToName.value Smtp.ToAddress = form.eTo_Address.value Smtp.BCC = form.eBCC.value Smtp.Message = form.editor_message.value if not empty(form.attachments)   Smtp.Attachments = form.attachments endif if( Smtp.Send(True))  // e-mail sent successfully   MSGBOX("e-mail Sent","Success")   // call the routine to create the client followup record   Class::ClientFollowup() else  // first attempt failed using the mail server IP address   Smtp.Server = "myothermailserver.com"  // perhaps use the internal LAN address of the mail server   if( Smtp.Send(True))     MSGBOX("e-mail Sent","Success")     // call the routine to create the followup record     Class::ClientFollowup()   else     MSGBOX("e-mail Send Failed...Status Message is " + Smtp.StatusMessage," ")     MSGBOX("Error is " + Smtp.Error," ")     MSGBOX("Error Message is " + Smtp.ErrorMessage)   endif  // second send attempt endif    // first send attempt close procedure Funcky.cc form.close()  // close the form and get back to business return

In the code example above, we populate several properties of the FUNCky SMTP methods, i.e., Server, the various addresses, and the text message, and we identify any attachments the user may want to include. Additionally, we try sending the e-mail twice, using two different mail server addresses.  Note that when identifying the SMTP server, you can use IP addresses or the actual server name.  If your users are on a LAN, you can use the local IP address of the mail server.  If the send is successful, we notify the user, and call the Class to create the client followup record.  If both sends fail, we notify the users and provide them the error codes and messages.  Fortunately we are blessed with fairly competent users and if they encounter an error message like the above, they write them down and notify our tech support so we can troubleshoot the problem.

If you were in an environment where the IP addresses of the mail servers change, you could store the values for the servers in a configuration table and just grab the values from there.

Bulk E-mail

As mentioned earlier, we also use FUNCky and dBASE to handle sending bulk e-mail.  First we loop through the client records, copying each e-mail address and client name to a temporary table.  Then we open a form used for bulk e-mail which allows the user to type in the text message.  When the user clicks the send button, the following code is run:
 

local d,db,r,s,t,x,Smtp set procedure to Funcky.cc additive set procedure to message.wfm additive fMessage = new MessageForm() fMessage.text = "PCS Information System"  // form text (titlebar) fMessage.title.text   = " "  // large text at top fMessage.message.text = "Now sending your e-mails " fMessage.Open() fMessage.Mousepointer = 11 Smtp = New FUNCkySmtp() Smtp.Server      = "myfirstmailserver.com" Smtp.FromName    = trim(form.eClient_Number.value) Smtp.FromAddress = trim(form.eFrom_Address.value) Smtp.Subject     = form.eSubject.value Smtp.BCC         = trim(_app.AgentEmail) Smtp.Message     = form.editor_message.value // now setup the necessary loop and functions to complete the // address process for all the identified records d = new database("PCSLOCAL") // BDE Alias used for local, temporary tables d.emptyTable('bademail.dbf') // empty this table so we can use it db = new database("PCS")    // BDE Alias used for networked tables r = new query() r.database = d r.sql = 'select * from bulkemail.dbf' // table we stored the names and e-mail addresses in r.active = true s = new query() s.database = d s.sql = 'select * from bademail.dbf' // used to store failed e-mail information s.active = true t = new query() t.database = db t.sql = "select * from clientfollowup.dbf" // client followup record table t.active = true t.IndexName = "FOLLOWUP" //index based upon the unique client number r.rowset.first()  // go to the first record in the rowset // now validate the names and e-mail addresses to // ensure we do not have any invalid or blank names do   if empty(r.rowset.fields["name"].value) and ;     empty(r.rowset.fields["address"].value)     r.rowset.delete()   endif until not r.rowset.next() // now get ready to send out the e-mail r.rowset.first() do   // Set the rest of the message properties based upon   // data in the local table   Smtp.ToName = trim(r.rowset.fields["name"].value)   Smtp.ToAddress = trim(r.rowset.fields["address"].value)   if not(Smtp.Send())    // if the e-mail did not get sent     // change the e-mail server IP and try again     Smtp.Server = "mymailserver.com"     if not (Smtp.Send()) // failed again - dump the records into                          // a table for analysis       s.rowset.BeginAppend()       s.rowset.fields["name"].value = trim(r.rowset.fields["name"].value)       s.rowset.fields["address"].value = trim(r.rowset.fields["address"].value)       s.rowset.Save()     else  // e-mail was sent - create a clientfollowup record       Class::ClientFollowup()     endif // second send attempt   else // e-mail was sent first time - create a followup record     Class::ClientFollowup()   endif  // first send attempt until not r.rowset.next() /* now check for the existence of bad e-mails if there are records, show a report so the user can fix the problem */ if s.rowset.first()   do preview.wfm with "bademail.rep" // preview.wfm is available in the dUFLP endif // end of bad e-mails check code t.active  := false s.active  := false r.active  := false d.active  := false db.active := false fMessage.mousePointer = 0 fMessage.Close() close procedure message.wfm close procedure Funcky.cc // close the form and return to the calling program form.close() return

While much of the above code is specific to our form for sending bulk e-mail, I believe it demonstrates an approach that you can easily adapt to your circumstances.

This particular client also sends a variety of “form” e-mails to customers throughout the process of building and delivering the customers' cars.  These e-mails are built and sent using a .cc file we developed specifically for this purpose.   While we use the same .cfm e-mail form as in the above examples, the populating of the various fields, and especially the formatting the e-mail text is handled through our .cc file.

Sending e-mails using either of the above examples is extremely fast.

Get FUNCky?

FUNCky provides a wide variety of tools for the dBASE user.  The help file is complete and includes well-documented examples for a variety of programming languages.  Much of the above code was “derived” (okay, “borrowed”) directly from the FUNCky examples.  Technical support is excellent and available from the FUNCky web site at http://www.FUNCky.com. There is a new FUNCky demo for dBASE available at (watch for word wrap)
http://www.funcky.com/downloadfile.asp?Filename=FUNCkyVDB.Exe&Section=Demos&Version=VDB

We use FUNCky for a variety of purposes and have found it to be very easy to integrate into a dBL app.  Perhaps it's time for you to get FUNCky.