Enabling your Database Application to Send E-mail
It's time for a new challenge!
We recently upgraded a complex database system to send e-mails in addition to
generating letters. Up until then, the system would generate comprehensive
letters using Visual Basic for Applications (VBA), which controls Microsoft Word
from within the database application. With this enhancement, the process
would go a few steps further by generating an e-mail that's sent to the same letter
recipients and then would have the generated letter and all its attachments
converted into e-mail attachments. Here's an overview of all the steps we went
through. Planning and Feasibility
Issues:
- Is the system owner / end-user ready for a complex
project which may not produce a 100% result?
- Are all documents in a format suitable for e-mail?
- Text and HTML documents are fine;
- Word processor documents (such as MS Word's .DOC) may or may not be OK
for everyone, depending on the presence of Internet Explorer or MS
Office on the recipient's machine;
- Database reports are usually NOT suitable as e-mail attachments.
- Is the recipient community ready to receive what you're sending them solely via
e-mail or are there legal or technology issues that would necessitate a
hard-copy to be mailed or faxed?
- Are you planning to develop a system that would function in a less-than-perfect mode?
For example ...
- When all recipients are not willing to switch to an e-mail only mode,
will your system behave in a user-preference mode?
- What if all
e-mail addresses aren't available? Will your system send the e-mail to
some and generate an alternative output for the others?
Development and Testing:
- Collect and enter the e-mail addresses
- Create some TEST e-mail addresses, in addition to the real
recipients (you can’t just
send e-mail to actual recipients while testing)
- Convert the documents into a format suitable for
attachment to an e-mail (e.g. Word, HTML, PDF)
- Verify that all recipients of the e-mail (including those on the CC list) have their e-mail address
on the database
- Verify that the sender has his/her e-mail address available
- Create the e-mail framework, with the following pieces:
- TO: every recipient
- CC: the sender
- SUBJECT: a brief phrase that's easily recognizable by recipients
(otherwise they may just delete it without opening it)
- CONTENT: a single line describing what the e-mail is about (e.g. to
confirm an upcoming appointment)
- ATTACHMENT: Full path, file name and extension of the attached documents
- After running your unit tests (by sending the e-mail to yourself), make arrangements
with a couple of actual recipients to help you with acceptance tests
- Send the e-mail
- Programmatically create a historical log of which e-mail was sent to
whom on what date -- a critical step in the
absence of a paper-trail
- Create a lookup / print process for the above historical log file
Logistical Arrangements:
- E-mail interface software that bridges the gap between your
database application and the e-mail world (We used SEE4FP from www.marshallsoft.com)
- Attachment conversion tools (We used the PDF converter from Amyuni
Technologies at www.amyuni.com)
- E-mail arrangements with the client's IT department, for two important
reasons:
- Authorization to send e-mail programmatically
- Name of the SMTP server (needed by the interface software)
And last but not least, if you face some initial resistance to the idea,
here's a simple way to justify the new process: With an automated e-mail like
this, when you have multiple recipients, each one will receive an e-mail that's
exclusively addressed to them and they will NOT know who the other recipients
are.
|
Ben
Aminnia, president of Pointer Corporation, is a database
architect with over 20 years of experience. He's also the president of
Los Angeles SQL Server Professionals Group www.sql.la
and a board member of Los Angeles .NET Developers Group
www.ladotnet.org. |
|
 |
|
Pointer
Corporation