Link to home
Start Free TrialLog in
Avatar of woodwyn
woodwynFlag for United States of America

asked on

Send emails from VFP app without Outlook

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

Avatar of David Favor
David Favor
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of woodwyn

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial