Automating Outlook 365 from VFP

[Mike Lewis]

Has anyone here got any experience of automating Outlook 365 from VFP?

I have an application that uses Automation to send emails via Outlook. It has been in use for about 14 years, and is running with various versions of Outlook from 2007 to 2016. The main user is about to go over to Office 365, and I wanted to check whether there are likely to be any incompatibilities or other issues arising from the move.

(I would normally simply test it for myself, but I don't have Office 365, and have no plans to get it.)

The application just uses the basic PEMs of the MailItem object: setting recipients, attaching files, sending and saving, etc. Nothing fancy.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Hi Mike

I don't think you can automate Office365 unless your client goes for the locally installed option, the web based/clod based version probably has an API
but I've never looked into it.

I would suggest your best bet (cost wise) would be to transition to ChilKat for your email requirements. It's good, reliable and not expensive.

I've moved three or four apps over to it, so that is a good few users, and once set up they don't have any idea... it talks to the M$ gateway
and supports HTML messages if you want.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

clod/cloud you say tomato... B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Nigel Gomm]

Mike,

two answers

1. there's a rest API.
2. customers can continue using the desktop outlook to interface with office365 and you can continue automating it.

in the past i had recommended redemption RDO but would no longer do so. Problem there is it reqires desktop outlook installed because the MAPI components are no longer downloadable separately; and if you have desktop outlook installed... you can automate as you always have.

hth

n


p.s. i have some code for sending emails by restAPI if you are interested but it uses the chilkat http components.

 

[Mike Lewis]

Nigel and Griff,

Thanks for those two helpful replies. I'm encouraged by Nigel's remark that my code will continue to work if they have the desktop version installed. I'll need to check that with the client. If so, it would be the simplest solution.

That said, I've just spent half an hour perusing the ChilKat docs. I was aware of ChilKat, but never took the time to investigate it before. It is impressive. It certainly looks like sending simple emails will be no problem, and it has so much more as well. I'm tempted to get a licence, if not for this project, but for general use.

I'll consider it all further and let you know how things work out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Nigel Gomm]

Mike,

i may be remembering this wrong but i think you have to configure each Office365 mailbox to allow SMTP/POP3.

n

update: hmmm.... perhaps only if the organization has disabled it

 

[GriffMG]

Hi Mike

I encapsulate the mail feature like this (M.CRLF is probably obvious at CHR(13)+CHR(10))
I keep the chilkat dll inside a blob within the app and write it out each time the app is loaded and regsrvr it from within
the app each time too.

It is a little fussy about 'good' email addresses.

FUNCTION SECUREEMAIL
	PARAMETERS m.SMTPSERVER,m.SMTPNAME,m.SMTPUSERNAME,m.SMTPPASSWORD,m.RECIPIENT,m.SUBJECT,m.BODY,m.SMTPPORT,m.ATTFILE,m.ATTFILE2
	PRIVATE m.SMTPSERVER,m.SMTPNAME,m.SMTPUSERNAME,m.SMTPPASSWORD,m.RECIPIENT,m.SUBJECT,m.BODY,M.SMTPPORT, m.ATTFILE,m.ATTFILE2,I
	LOCAL LOMAILMAN, LOEMAIL,LNSUCCESS

	IF PCOUNT() < 10
		m.ATTFILE2 = ""
	ENDIF
	IF PCOUNT() < 9
		m.ATTFILE = ""
	ENDIF
	m.SMTPNAME = STRTRAN(m.SMTPNAME,";",",")
	m.RECIPIENT = STRTRAN(m.RECIPIENT,";",",")

	*  The mailman object is used for sending and receiving email.
	LOMAILMAN = CREATEOBJECT('Chilkat_9_5_0.MailMan')

	*  Any string argument automatically begins the 30-day trial.
	LNSUCCESS = LOMAILMAN.UNLOCKCOMPONENT("*** None of Your Business ***")
	IF (LNSUCCESS <> 1) THEN
		MESSAGEBOX(LOMAILMAN.LASTERRORTEXT,48,"Problem")
	ELSE

		LOMAILMAN.SMTPHOST	= m.SMTPSERVER
		LOMAILMAN.SMTPUSERNAME = m.SMTPUSERNAME
		LOMAILMAN.SMTPPASSWORD = m.SMTPPASSWORD
		LOMAILMAN.SMTPSSL = 0
		LOMAILMAN.STARTTLS = 1
		LOMAILMAN.SMTPPORT = m.SMTPPORT
		*VAL(M.SMTPPORT) && 25

		LOEMAIL = CREATEOBJECT("Chilkat_9_5_0.Email")

		loemail.Charset = "utf-8"
		LOEMAIL.FROM = m.SMTPNAME
		IF !","$m.RECIPIENT .and. !m.CRLF$m.RECIPIENT .and. !";"$m.RECIPIENT
			LOEMAIL.ADDTO(m.RECIPIENT,m.RECIPIENT)
		ELSE
			M.RECIPIENT = STRTRAN(STRTRAN(M.RECIPIENT, ',', M.CRLF),";",m.CRLF)
			FOR I=1 to MEMLINES(M.RECIPIENT)
				IF "@"$MLINE(M.RECIPIENT,I)
					LOEMAIL.ADDTO(MLINE(m.RECIPIENT,I),MLINE(m.RECIPIENT,I))
				ENDIF
			ENDFOR
		ENDIF
		*LOEMAIL.ADDCC(m.SMTPNAME,M.SMTPNAME)
		*LOEMAIL.BODY = m.BODY
		loEmail.AddPlainTextAlternativeBody(m.Body)
		
		LOEMAIL.SUBJECT = m.SUBJECT
		IF !EMPTY(m.AttFile)
			loEmail.AddFileAttachment(m.AttFile)		
		ENDIF
		IF !EMPTY(m.AttFile2)
			loEmail.AddFileAttachment(m.AttFile2)		
		ENDIF
		WAIT "Sending... Please Wait" WINDOW NOWAIT TIMEOUT 10
		LOMAILMAN.OPENSMTPCONNECTION()
		LLRESULT = LOMAILMAN.SENDEMAIL(LOEMAIL)
		WAIT CLEAR
		IF LLRESULT <> 1
			DO CASE
				CASE LEFT(ALLTRIM(LOMAILMAN.LASTERRORTEXT),3)= "-3 "
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT+m.CRLF+m.CRLF+;
						"THIS PROBLEM GENERALLY MEANS THE NAME OF THE SMTP SERVER IS NOT CORRECT, IT IS NOT CURRENTLY RUNNING OR NOT VISIBLE FROM THIS WORKSTATION."+m.CRLF+m.CRLF+;
						"THIS CAN SOMETIMES BE CAUSED BY ENTHUSIASTIC ANTI-VIRUS SOFTWARE - TRYING TO STOP SPAM BEING SENT FROM YOUR PC.",48,"Problem")
				CASE LEFT(ALLTRIM(LOMAILMAN.LASTERRORTEXT),3)= "500"
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT+m.CRLF+m.CRLF+;
						"THIS PROBLEM GENERALLY MEANS THE SMTP SERVER IS CONFIGURED NOT TO ACCEPT E-MAILS FOR THE SPECIFIED RECIPIENT (OR THEIR DOMAIN [THE XXX.COM BIT]."+m.CRLF+m.CRLF+;
						"YOU WILL PROBABLY NEED TO GET THE SMTP SERVER'S ADMINISTRATOR TO HELP YOU.",48,"Problem")
				CASE LEFT(ALLTRIM(LOMAILMAN.LASTERRORTEXT),3)= "530"
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT+m.CRLF+m.CRLF+;
						"THIS PROBLEM GENERALLY MEANS THE SMTP SERVER IS CONFIGURED TO EXPECT AUTHENTICATION."+m.CRLF+m.CRLF+;
						"YOU WILL PROBABLY NEED TO GET A USERNAME AND A PASSWORD FROM THE SMTP SERVER'S ADMINISTRATOR.",48,"Problem")
				CASE LEFT(ALLTRIM(LOMAILMAN.LASTERRORTEXT),3)= "535"
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT+m.CRLF+m.CRLF+;
						"YOU HAVE PROBABLY ENTERED YOUR SMTP SERVER USERNAME OR PASSWORD INCORRECTLY."+m.CRLF+m.CRLF+;
						"YOU SHOULD CHECK THEM - THEY MAY BE CASE SENSITIVE.",48,"Problem",200)
				CASE LEFT(ALLTRIM(LOMAILMAN.LASTERRORTEXT),3)= "550"
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT+m.CRLF+m.CRLF+;
						"THIS PROBLEM GENERALLY MEANS THE SMTP SERVER IS NOT SET-UP TO ACCEPT E-MAILS FROM THIS WORKSTATION."+m.CRLF+m.CRLF+;
						"YOU WILL PROBABLY NEED TO GET THE SMTP SERVER'S ADMINISTRATOR TO HELP YOU.",48,"Problem")
				OTHERWISE
					_CLIPTEXT=LOMAILMAN.LASTERRORTEXT
					MESSAGEBOX("Problem Occurred during Test:"+m.CRLF+m.CRLF+LOMAILMAN.LASTERRORTEXT,48,"Unexpected Problem")
			ENDCASE
		ELSE
			*MESSAGEBOX("Message Sent - Use your E-Mail client to verify receipt",48,"No Out-Going Problem",30)
		ENDIF
		LNSUCCESS = LOMAILMAN.CLOSESMTPCONNECTION()
		IF (LNSUCCESS <> 1) THEN
			MESSAGEBOX( "Connection to SMTP server not closed cleanly.",48,"Problem")
		ENDIF

		RELEASE LOEMAIL
	ENDIF
	RELEASE LOMAILMAN
	RETURN(.T.)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[tomk3]

Hello,

I agree , I use and like chilkat which is a mighty and well documented tool.

The reason we do not use it for email is that an email sent via chilkat does not appear in user's "sent item folder" in Outlook or tobit david or ...
This may be a problem depending on customers requirement.

Regards
tom

 

[Mike Lewis]

I've now had an opportunity to test my original code using 365, and I'm pleased to report that it ran flawlessly, without any changes - as predicted by Nigel, above. (And the SMTP authentication issue doesn't seem to be a problem.)

Griff, thanks for that code. I won't take it any further for now, but I will come back to it when I can grab some time to play with ChilKat.

Thanks again to both of you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Tom's comment apart, which actually is a good thing for some people trying to manage mailbox sizes, the Chilkat email interface is very good indeed.
I'm sure my encapsulation is poor, but it works pretty much all the time.

Save for if some one tries to use a duff email address - griff!finedata.com for example - when the results are unpredictable

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

The reason we do not use it for email is that an email sent via chilkat does not appear in user's "sent item folder" in Outlook

Tom, that's right. Come to think of it, the reason I decided to use Outlook Automation in the first place was to make it easy for the user to keep track of sent items, as well as reviewing drafts, filing sent items, and so on. It's good that they will still be able to do that.

I've got another application where I use Mike Gagnon's CDO-based system (faq184-4969). But that's used for sending invoices, and so is completely automatic with no user intervention. It's still working well after 15 years.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Nigel Gomm]

Mike,

the Office365 RestAPI will also put emails in the sent items folder.

but is less useful for unattended emailing than SMTP because it uses oAuth and there is the risk of the token expiring.

n

 

[Chris Miller]

I had this problem of ignorance about the desktop licenses available within the Office 365 subscription (it's more like MSDN with Office only, which applications exactly depends on the edition (family, student, business), but Outlook and OneDrive plus the online versions of the applications usable from anywhere with an internet connection and supported browsers.

Take a look at MS offers, And you (roughly) know what's in it. As far as I am concerned I "just" am one of the 6 users of a family edition with the home versions of Office. And since it's mainly a subscription you get access to the latest versions of the Desktop, AFAIK Office 2019.

The owner has a dashboard to invite users and then they can immediately start using the online versions, but also have the downloads for local installation of the classic desktop apps.

I "solved" my problem about the ignorance of an admin by giving him a simple VBA macro to create a mail, which is quite 1:1 the VFP code for creating an Outlook mail, as you know, it mainly uses the Office object model with great downward compatibility and there isn't much to adapt, not much, which could go wrong except Outlook isn't installed. The API is a topic for itself, I haven't tried it yet, since I still think the desktop versions give you the best experience.

This macro points out that your customer can't expect compatibility of the desktop and online versions from the automation perspective and it shows how little you can go wrong about your code "not working anymore". Well, the requirements need to be fulfilled, Desktop Outlook, in short. And then a three-liner can't fail unless outlook.application doesn't exist.

' This macro works in Excel as well as Word, for example.

Sub Mail_small_Text_Outlook()
'For Tips see: [URL unfurl="true"]http://www.rondebruin.nl/win/winmail/Outlook/tips.htm[/URL]
'Working in Office 2000-2016  & remark: I'd say up to 2019.
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)  '0 = vbMailItem or similar.

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "your.mail@example.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

It helped me to show to the admins, how the automation works and you can point to the web API to let them see the drastic difference and that it's not just an issue with your code, but the requirement for automation are the COM/OLE automation servers installed with the Office desktop applications.

I'd challenge anyone to report a desktop Office version (since Outlook 95) that wouldn't start Outlook and create/send or display a mail with this macro, despite the security issues Nigel addressed already (redemption RDO).



Chriss