Using the Outlook.Application object to read e-mails from Visual Foxpro.
Trying to read e-mail using POP3 or IMAP proved to be hell to me. Documentation is not present or hardly legible. Because we use Microsoft® Outlook® at our company, I was able to connect to Outlook using the Outlook.Application object.
This simple code finds all mails in a certain folder (loTelecomMap):
loOutlook = CREATEOBJECT("Outlook.application")
loMAPI = loOutlook.GetNameSpace("MAPI")
loTelecomMap = loMAPI.GetDefaultFolder(6).Folders([Business]).folders([Telecom])
loMails = loTelecomMap.Items
loMessage = loMails.Find("[Subject]='Mail subject title'") && starts with the last (newest) message
DO WHILE NOT ISNULL(loMessage)
ldDate = TTOD(loMessage.ReceivedTime)
lcMessage = loMessage.Body
lcHTMLsource = loMessage.HTMLbody
* More actions on the loMessage object
loMessage = loMails.FindNext() && Next message
ENDDO
RELEASE loOutlook
I had great help using a whitepaper by Andrew MacNeill. Linking to his page is prohibited, so you have to copy-and-paste the URL yourself: http://www.aksel.com/whitepapers/OutlookAutomation.htm. To accommodate for possible closing of that reference, I have included the pages contents (the whitepaper) in PDF form on this page.
If you just want to read the contents of an Excel spreadsheet, there is an easier way using the Microsoft Excel (ODBC) Driver.
In a previous post (Foxpro and Excel) we have shown how to control Excel using the Excel.Application object. But if you are just interested in the contents, there is an even easier way to achieve this: The Microsoft Excel (ODBC) driver. that is present on most systems with MS Office installed.
To get an SQL connection to an Excel sheet is no more difficult than this:
lcXLfile = [c:\temp\demo.xlsx]
* Warning: The driver wil never fail!
* If the file does not exist, this driver wil create the file.
IF !FILE(lcXLfile)
? [Excel file not found]
RETURN .F.
ENDIF
lcConnection = [DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=]+lcXLfile
lnHandle = SQLSTRINGCONNECT(lcConnection)
* Get all sheetnames in a cursor (every sheet is a Table)
? SQLTABLES(lnHandle,[],[crSheets])
lcQuery = [SELECT * FROM ]+TRIM(STRTRAN(crSheets.Table_Name,['],["]))
? SQLEXEC(lnHandle, lcQuery, [crResults])
SQLDISCONNECT(lnHandle)
The SQLTABLES command creates a cursor with a row for each sheet in the Excel file. You can filter the kind of tables in the second parameter. This is a typical row:
Field name
Field type
Length
Example
TABLE_CAT
Memo
4
C:\TEMP\DEMO.XLSX
TABLE_SCHEM
Character
128
.NULL.
TABLE_NAME
Character
128
‘Sheet1$’
TABLE_TYPE
Character
128
TABLE
REMARKS
Character
254
.NULL.
SQLTABLES structure
Remarks
Quotes in the SQLTABLES result
The TABLE_NAME field contains single quotes, These seem not to work in a SQL statement. So you have to replace them with double quotes. Check out the lcQuery line in the code above..
Field names in queries
Only when a sheet is properly filled the field names in queries are automatically named correctly: The titles in the first row are accepted as fieldnames. However, if the first row is left blank or there are multiple tables on a page, the result becomes erratic.. In this situation some or all fields get the title F1. and so on (number being the column number).
Field types
It seems the unaltered query results deliver just 2 kinds of columns: Numbers and Memo’s. Both columns may contain .NULL. values.
Quotes in query’s
To compare or use strings in SQL query’s, you need to use the single quotes again. For instance:
lcQuery = [SELECT * FROM "Sheet1$" WHERE surname='de Graaf']
SQLEXEC(lnHandle,lcQuery,[crDeGraaf])
Double complex because the sheet (table) name has to be encapsulated in double quotes, while a compare string has to be encapsulated in single quotes.. I can’t think of a reason for this odd behavior.
Read only
As to be expected, this ODBC method to an Excel file is limited to reading the file. Trying to insert or update using SQL commands renders the result that the query has to be updatable.
Soms worden gegevens aangeleverd op meerdere tabbladen, en deze wil je tot een enkele tabel combineren.
Je begint met het maken van een nieuwe Query als volgt:
Dan kies je het bronbestand:
Klik op Openen. In de volgende dialoog kan je kiezen welke tabbladen je wil importeren. Maar dat wil je nog niet! Als je het wel doet, dan maakt Powerquery voor elk tabblad een nieuwe Query waardoor je moeilijk kan combineren. Nee, je kiest hier de map en voor bewerken!
Nu krijg je keurig alle tabbladen als regels te zien:
Als je een Table klikt, dan zie je dat de hele tabel in deze cel zit gepropt:
We gaan de mutaties niet vergelijken, en het verborgen werkblad FilterDatabase willen we ook niet behandelen. Vandaar dat we de tabbladen uitsluiten van verdere verwerking door hier een filter toe te voegen waarbij we de ongewenste werkbladen de-selecteren:
Nu hebben we alleen de gewenste werkbladen. We gaan deze combineren door de knop met de 2 pijltjes in de Data kolom:
Hierna moeten we kiezen welke kolommen we willen meenemen. Ook staat standaard aan dat je de oorspronkelijke kolomnaam als voorvoegsel gebruikt. Dat wil je niet. Alle kolommen zouden dan Data.Postcode, Data.Huisnummer enzovoorts gaan heten. Dit vinkje dus weghalen!
Nu heb je een enkele tabel met alle rijen en een paar extra kolommen (4). De eerste kolom bevat de bron (uit welk bestand stammen de gegevens). De kolom Item staat achter de ingelezen kolommen en bevat hier de naam van het tabblad waar de gegevens vandaan komen.
Nu moeten de kolomnamen nog worden aangepast, want een kolom met een nummer is weinig duidelijk:
Gelukkig is er een mooie functie voor die de eerste rij omzet naar kolomnamen:
We moeten wel de naam van de eerste kolom herstellen, en het is vervelend dat de overige titelregels tussen de gegevens zijn blijven staan:
De overbodige titelregels kunnen we eenvoudig uitfilteren in bijvoorbeeld de perceelnummer kolom:
Daarna de kolommen een zinvolle naam gegeven, en de tabblad kolom naast de bronkolom geplaatst.
Nu moeten de kolomtypes nog worden aangepast. Dat kan je zelf handmatig doen, maar het is sneller om alles te selecteren (Ctrl-A) en vervolgens de knop Gegevenstype detecteren te klikken. Je ziet nu dat Powerquery vaak het juiste type kan bepalen:
Using Webhooks, you can connect services with eachother. For instance: You can install the Webhooks Add-on to Gravity Forms on your WordPress website. You can then define 0 to many Webhooks. Each Webhook delivers the form data in the required form (JSON/ Form fields) to any desired URL.
To test the data that is generated, there is this great Webhook Site service. On visiting, you get an URL target for your Webhook. This target URL is then used in the Webhook with the Gravity Form. Upon submitting the form, you can see the data generated on the Webhook.Site webpage.
I always warn people for their dependency on provider e-mail services. The risk is when you change provider, you lose all your valuable e-mails in a jiffy. Enter Mailstore Home: This software (free for personal use) allows you to connect to your various mail servers, and archive the messages in a local store (in fact (sadly) it refused to create a store on my NAS). From that moment on, your e-mail messages are yours again! You can search them, view them anytime. No matter if your mail server crashes, is unavailable, or even if you have no internet connection: You have your e-mail.
After replacing my computer, I needed to reinstall Visual Foxpro. I had the install disc contents, but whatever executable I started with: it kept on crashing. A Google search brought silly advice, but fortunately there was a gem: an article on wOOdy’s Blog. This explained what the right procedure is and what file to start with (VFPSTART.HTA). It stil would not work. Not from a folder, and not from a mapped network disc. I found the free AnyBurn software that gave me the opportunity to create an ISO file from my install folder. Now it was as easy as double clicking the ISO file to have Windows mount the CD image and assign it a drive letter. Now everything worked like a charm. Then we have to apply both service packs, and perhaps some useful suggestions wOOdy made.
I am using Gliffy a lot. It is a great graphic tool (free Chrome extension) to create flowcharts, UML diagrams, network diagrams and much more. Very important is the rubber-banding support: when you connect objects using their red indicated connections (during drawing), these connections are maintained wherever you drag the objects.
I needed to test whether some ports were enabled or blocked. This tool/ service is great: It simply accepts any port request and tells you if it succeeded. From the PortQuiz page:
This server listens on all TCP ports, allowing you to test any outbound TCP port.
You have reached this page on port 80. Your network allows you to use this port. (Assuming that your network is not doing advanced traffic filtering.)
Network service: http Your outgoing IP: 86.93.90.146
Test a port using a command
$ telnet portquiz.net 80
Trying ...
Connected to portquiz.net.
Escape character is '^]'.
$ nc -v portquiz.net 80
Connection to portquiz.net 80 port [tcp/daytime] succeeded!
$ curl portquiz.net:80
Port 80 test successful!
Your IP: 86.93.90.146
$ wget -qO- portquiz.net:80
Port 80 test successful!
Your IP: 86.93.90.146
# For Windows PowerShell users
PS C:\> Test-NetConnection -InformationLevel detailed -ComputerName portquiz.net -Port 80
You can use the new Windows 10 clipboard tool with the combination Windows-Shift-S (Try it now). It has nice possibilities like selecting a rectangle, free shape, current window or complete screen. But I want more. For instance: a form of history than can easily be viewed and selected. I found this nice software that can even clip and paste cross platform (haven’t tested it yet) through Google cloud storage.
Enter the free 1Clipboard. You clip whatever you want using Windows-Shift-S. After that, you can see your last clip in the Windows message center. With the key combination Ctrl-Alt-V the program opens, showing you the clipboard history and allowing you to select any clip (which is put onto the clipboard when you click it), or make it a favorite. There is even a search available.
