Send emails from VFP app without Outlook

woodwyn
woodwyn used Ask the Experts™
on
We need to send emails from our VFP app. Currently we are calling outlook directly to do so using the following code. Ideally it would not depend on any external application and submit an email directly to a configured smtp server. If that is not an option using a generic interface that does not directly depend on outlook would still be useful. We do not need to receive email on these workstations, the mailing function should be as transparent to the user as possible, and not depend on outlook.   It will need to run on Windows Server 2012.

lnArea=SELECT()

lcBody = 'REQUEST DESCRIPTION: '+ALLTRIM(vSQLITRequests.Description)+CHR(13)+CHR(10)+CHR(13)+CHR(10)+;
			IIF(EMPTY(vSQLITRequests.Details),'','DETAILS: '+ALLTRIM(vSQLITRequests.Details)+CHR(13)+CHR(10)+CHR(13)+CHR(10))+;
			'CREATED AT: '+TTOC(vSQLITRequests.dtCreated)+CHR(13)+CHR(10)+;
			'CREATED BY: '+ALLTRIM(vSQLITRequests.CreatedBy)+CHR(13)+CHR(10)+;
			IIF(EMPTY(vSQLITRequests.ModifiedBy),'','MODIFIED AT: '+TTOC(vSQLITRequests.dtModified)+CHR(13)+CHR(10)+;
			'MODIFIED BY: '+ALLTRIM(vSQLITRequests.ModifiedBy)+CHR(13)+CHR(10))+;
			'REQUESTING EMPLOYEE: '+ALLTRIM(vSQLITRequests.RequestedBy)+CHR(13)+CHR(10)+;
			'REQUESTED COMPLETION ON OR BY DATE: '+DTOC(vSQLITRequests.dtRequestedComp)+CHR(13)+CHR(10)+;
			'PRIORITY: '+ALLTRIM(vSQLITRequests.Priority)+CHR(13)+CHR(10)+;
			'STATUS: '+ALLTRIM(vSQLITRequests.Status)+CHR(13)+CHR(10)+CHR(13)+CHR(10)+CHR(13)+CHR(10)
			
			
prmKeyITRequests=vSQLITRequests.keyITRequests
=REQUERY('vSQLITTasks')
SELECT vSQLITTasks

LOCATE
IF EOF()
	lcBody = lcBody + 'TO DATE NO TASKS HAVE BEEN COMPLETED FOR THIS REQUEST.'
ELSE
	lcBody = lcBody + 'TASKS COMPLETED FOR THIS REQUEST TO DATE:'+CHR(10)+CHR(13)+CHR(10)
	SCAN
		lcBody = lcBody +;
					'CREATED AT: '+TTOC(vSQLITTasks.dtCreated)+CHR(13)+CHR(10)+;
					'IT STAFF: '+ALLTRIM(vSQLITTasks.Staff)+CHR(13)+CHR(10)+;
					'TASK DESCRIPTION: '+ALLTRIM(vSQLITTasks.Description)+CHR(13)+CHR(10)+CHR(13)+CHR(10)
	ENDSCAN
ENDIF

SELECT (lnArea)

lcEmail=ALLTRIM(oLib.GetEmployeeEmail(vSQLITRequests.keyRequestedBy)) && Get the requestors email

loApp = CREATEOBJECT("Outlook.application")
loEmailItem = loApp.CreateItem(0)    && MAILITEM
loEmailItem.Recipients.Add("name1@domain.com")      &&Uses the Recipients collection
IF !EMPTY(lcEmail)
	loEmailItem.Recipients.Add(lcEmail)      &&Uses the Recipients collection
ENDIF
loEmailItem.Recipients.Add("name2@domain.com")      &&Uses the Recipients collection
loEmailItem.Subject = IIF(FinalEmail,"IT Request Final Notification","IT Request Notification")
loEmailItem.Importance = 1      && IMPORTANCENORMAL

loEmailItem.Body = lcBody

loEmailItem.Send

release loApp, loEmailItem

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
You asked, "Ideally it would not depend on any external application and submit an email directly to a configured smtp server."

Best to just use some simple server.

http://brianstafford.info/libesmtp/ provides excellent plumbing for this type of work.

Keep in mind, you'll likely always use a relay service like MailGun else you'll have to plumb your own MTA.

Which includes complex items like... running IP blocks to keep Mailbox Providers from throttling + DKIM signing (a bear to get working).

Tip: Sending email is simple. Just craft your header + \n + message body. Having high email deliverability... that's an entirely different matter.
Several ways of e-mail sending from VFP exist:

1) The most complex is to use WinSocks as you may see in the sample code: http://fox.wikis.com/wc.dll?Wiki~SendSmtpEmail
2) Much easier is to use CDO Mail which should be in all Windows versions: http://fox.wikis.com/wc.dll?Wiki~CdoEmail~VB
3) Chilkat offers paid component: https://www.example-code.com/foxpro/smtp_simpleSend.asp
4) This VFP forum post contains code for CDO Mail and also for jmail.dll which is still available on the net for download (but I did not test it yet) https://social.msdn.microsoft.com/Forums/en-US/2fc5efce-8a88-4100-bb8c-69c15f9c4e3a/sending-email-from-vfp?forum=visualfoxprogeneral
5) Many people are using Blat. Look here: http://fox.wikis.com/wc.dll?Wiki~BlatEmail  or here: http://www.sweetpotatosoftware.com/spsblog/CommentView.aspx

I am using CDO Mail for years w/o problems. (I just have to look at one installation where one application sends e-mails but another one doesn't...)
Olaf DoschkeSoftware Developer
Commented:
http://fox.wikis.com/wc.dll?Wiki~AutomatedEmail once had even more entries and Pavel also lists some more concrete.

Craig Boyd once did a series: https://www.sweetpotatosoftware.com/blog/?s=email+and+vfp

Even just stay in the VFP help: Send Mail Sample
File: ...\Samples\Solution\OLE\Sendmail.scx
Which makes use of the Msmapi32.ocx, which in turn makes use of... OK, stop. Before I go into detail about that one:

Please answer this major question:
Do you want to act as a mail client yourself, Allowing to configure an SMTP server?
Or do you want to use the already system configured SMTP server?

Or more concrete about the consequences: Do you care the mail goes out and isn't seen in a users outbox and sent messages? Or are you okay with sending mails in silence?

In many applications, I did automated notification messages were wanted, but I had to bring it to the attention of the customer while such automatic notifications may help with a workflow especially if some employees don't work with that application and need to be notified about
an important step, like passing a form, it's bad if that is not visible to the user triggering that message. He get's a reply to a mail he himself never sent, but it was sent in his name.

Besides it's a problem of mail getting rated as spam or otherwise untrustworthy and you won't want that.

It's viable to use libraries like blat in a server environment, where an automated (perhaps regularly scheduled) task does something and informs an administrator about success with a short message or with failures with a report in the mail body.

Your code seems about such message and in such cases I never had an outlook installed serverside and configured for some system account.

Well, in short: if this is some internal messaging, there also are other ways than mail anyways and if this is going to end customers you should rather have it go an official way through the companies exchange server (or similar - and that is not just the mail server), so it's better using Outlook or at least MAPI - making use of the standard mail client sent items and in/outbox.

Bye,  Olaf.

Author

Commented:
Hello.  I attempted multiple suggestions from above, but I ran into multiple issues with utilities no longer being available for downloading.  In the end I am sticking with Outlook.  When a user needs to send an email I insert the details of the email to the backend SQL table.  I created a monitor form with a timer that checks the SQL table for unsent emails.  The monitor form runs on a system that I was comfortable having Outlook on.  I have my own Kerio mail server.  Is there any error trapping that you can help me put into this block of code.  I don't want to stop the monitor forms timer as no one will be monitoring the monitor.  

* Get work order from queue
lcCmd="SELECT TOP 1 * FROM EmailQueue WHERE Status='Q'"
lnRtVal=SQLEXEC(oLib.Handle,lcCmd,'Tmp2')
IF !USED('Tmp2')
	This.enabled=.T.
	RETURN
ENDIF
IF RECCOUNT('Tmp2')=0
	SELECT Tmp2
	USE
	This.enabled=.T.
	RETURN
ELSE

	loApp = CREATEOBJECT("Outlook.application")
	loEmailItem = loApp.CreateItem(0)    && MAILITEM
	loEmailItem.To = ALLTRIM(Tmp2.SentTo)
	loEmailItem.Subject = ALLTRIM(Tmp2.Subject)
	loEmailItem.Body = ALLTRIM(Tmp2.Body)
	loEmailItem.Importance = 1      && IMPORTANCENORMAL
	lcContentType = loEmailItem.Attachments.Add(ALLTRIM(Tmp2.Attachment))

	loEmailItem.Send

	release loApp, loEmailItem

	* Update queue
	lcCmd="UPDATE EmailQueue SET dtProcessed=GETDATE(),Status='S' WHERE keyEmailQueue='"+Tmp2.keyEmailQueue+"'"
	lnRtVal=SQLEXEC(oLib.Handle,lcCmd)

	SELECT Tmp2
	USE

ENDIF

Open in new window

You may use the simplest TRY  CATCH block:
* Get work order from queue
LOCAL loEx AS Exception
LOCAL lcError, lcStatus

lcCmd="SELECT TOP 1 * FROM EmailQueue WHERE Status='Q'"
lnRtVal=SQLEXEC(oLib.Handle,lcCmd,'Tmp2')
IF !USED('Tmp2')
	This.enabled=.T.
	RETURN
ENDIF
IF RECCOUNT('Tmp2')=0
	SELECT Tmp2
	USE
	This.enabled=.T.
	RETURN
ELSE
	
	lcError = ''
	TRY
	  loApp = CREATEOBJECT("Outlook.application")
	  loEmailItem = loApp.CreateItem(0)    && MAILITEM
	  loEmailItem.To = ALLTRIM(Tmp2.SentTo)
	  loEmailItem.Subject = ALLTRIM(Tmp2.Subject)
	  loEmailItem.Body = ALLTRIM(Tmp2.Body)
	  loEmailItem.Importance = 1      && IMPORTANCENORMAL
	  lcContentType = loEmailItem.Attachments.Add(ALLTRIM(Tmp2.Attachment))

	  loEmailItem.Send
	CATCH TO loEx
      lcError = 'Error ' + TRANSFORM(loEx.ErrorNo) + ' ' + loEx.Message + ', Line: ' + TRANSFORM(loEx.LineNo)
	ENDTRY
	
	release loApp, loEmailItem

	* Update queue
	IF EMPTY(m.lcError)
	  lcStatus = 'S'
	ELSE
	  lcStatus = 'E'
	ENDIF
	lcCmd="UPDATE EmailQueue SET dtProcessed=GETDATE(),Status=?lcStatus WHERE keyEmailQueue=?Tmp2.keyEmailQueue"
	lnRtVal=SQLEXEC(oLib.Handle,lcCmd)
	
	SELECT Tmp2
	USE

ENDIF

Open in new window

Of course, you may create the error info more descriptive but you should post the lcError variable value to the SQL backend and save it for further analysis. Also note the ? notation in the UPDATE command which is easier than to build the whole command as a string.

BTW, CDO mail does not need any download because it is included in all current operating systems. It just needs correct mail settings in the code which is difficult sometimes but many code samples are available. Once you set it then it works.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial