Automating Outlook

[amberel]

I would like to automate the extraction of gpx files from the zipped Outlook attachment in which they arrive. I want to extract just one of the 2 files into a specific directory.

At present this takes about 14 mouse clicks. I have to click to open the attachment, select the file I want, make sure WinZip is pointing to the correct directory (which may vary depending on which PQ has sent the file), tell it to extract, to overwrite the existing file, and then close down several windows. I have to repeat this for the 5 PQ's for that day. It's a tedious process.

I'm a programmer, and while I do mostly C and Delphi I'm sure I could manage VB, which is what I assume I would need in order to automate Outlook. But I can't really see where to get started. Any pointers?

Rgds, Andy

[dino]

Do you use GSAK? If so this is what I do:

Right click on the zip file attachment and save to a folder in My Documents called PQs* (Do that until they are all saved)

In GSAK click Open, click the folder option and navigate to the PQs folder.

Click Go and it opens all the PQs in that folder. The next time I do it GSAK remembers the last folder location I used so that saves a bit of time on repeat loads.

* I name my PQs Name1, Name2, Name3, etc and each day overwrite the old one so that my data doesn't get duplicated and I don't have to go back and delete the old ones each day

[amberel]

Do you use GSAK? If so this is what I do:

Right click on the zip file attachment and save to a folder in My Documents called PQs* (Do that until they are all saved)

Thanks for that info dino, but this isn't for GSAK. I've written my own mapping software for the PC that imports the gpx files into SQL server and overlays the caches on my maps.

I could easily modify my own map software to unzip the zips before reading the gpx files, but saving out the zip files still involves more clicks than ideally I want to do - I'm looking to automate it so I just click on one button in Outlook and it does everything for me.

Rgds, Andy

[dino]

Ok.....good luck then

[Alan White]

Two thoughts:

Firstly. you do know that WinZip has a CLI? Perhaps that could automate a part of the task?

Secondly, and this is a bit off the wall and perhaps OTT, you didn't say if you used GSAK so I don't know if you know that it has a very good macro language and a POP3 reader. You could easily write a GSAK macro that would fetch the PQs, put them in the directory of your choice, unzip them, delete the -wpts file, and leave the PQs in the directory ready for the rest of your processing. You needn't, if you didn't want to, use any of the other features of GSAK.

[keehotee]

I would like to automate the extraction of gpx files from the zipped Outlook attachment in which they arrive. I want to extract just one of the 2 files into a specific directory.

At present this takes about 14 mouse clicks. I have to click to open the attachment, select the file I want, make sure WinZip is pointing to the correct directory (which may vary depending on which PQ has sent the file),

Click to open your zipped GPX. Once winzip has opened, just drag the file you want to the location you want it unzipped to - you don't have to use winzip's directory / "unzip to".
That should cut out at least 4 of your clicks.......

Failing that, if you're using XP, just right click on the zipped file and select "extract to" and bypass winzip altogether.

Sorry - answers are a bit rusty - I switched to winrar a while ago and haven't used winzip for months.

[amberel]

Two thoughts:

Firstly. you do know that WinZip has a CLI? Perhaps that could automate a part of the task?

Secondly, and this is a bit off the wall and perhaps OTT, you didn't say if you used GSAK so I don't know if you know that it has a very good macro language and a POP3 reader. You could easily write a GSAK macro that would fetch the PQs, put them in the directory of your choice, unzip them, delete the -wpts file, and leave the PQs in the directory ready for the rest of your processing. You needn't, if you didn't want to, use any of the other features of GSAK.

Thanks Alan,

Yes, I knew about WinZip CLI, and it is installed on my machines, but I can easily write code to unzip a file in Delphi as part of my import routine. If I fail to find a completely integrated Outlook solution that is my most likely course of action, as it reduces the number of mouse clicks involved to:

Double click to open the email
right click the attachment and saveas
navigate to directory (only on first of batch)
OK to overwrite
click to close email

but even that is pretty boring to do 5 times a day every day .

I don't use GSAK, but it may be worth looking at. I'm a bit hazy about how it would pick out the appropriate emails on my mail server, and I would have to be sure to run it every day because the emails are deleted off my server a couple of days after Outlook has collected them. It is handy to know what sort of facilities it offers.

Rgds, Andy

[amberel]

Click to open your zipped GPX. Once winzip has opened, just drag the file you want to the location you want it unzipped to - you don't have to use winzip's directory / "unzip to".
That should cut out at least 4 of your clicks.......

Failing that, if you're using XP, just right click on the zipped file and select "extract to" and bypass winzip altogether.

Sorry - answers are a bit rusty - I switched to winrar a while ago and haven't used winzip for months.

Thanks, I tried that but the benefits are marginal. It replaces 3 clicks with one click and drag, but requires that I open Explorer first and navigate to the appropriate sub-directory, which pretty much cancels out the gains.

I am using XP, but there is no context sensitive "extract to" when I right click on the file in the Outlook attachment bar.

Rgds, Andy

[Alan White]

I'm a bit hazy about how it would pick out the appropriate emails on my mail server, and I would have to be sure to run it every day because the emails are deleted off my server a couple of days after Outlook has collected them. It is handy to know what sort of facilities it offers.

Most people seem to use a separate email account for PQs, but you don't have to. GSAK only downloads the attachments for messages where the subject matches a filter you specify. For PQs the default filter is "[GEO] Pocket Query:" but you can specify anything you like. I have a macro which also processes notifications, watchlists etc.

If you moved to GSAK for PQ collection and processing you could stop collecting the mails in Outlook, which removes the timing problem. Personally I collect from both Outlook and GSAK as this makes it easier to keep an eye on the PQs. I then configure Outlook to do the deletion from Outlook and the mail server after 30 days, long after I know that GSAK has processed them.

Of course, GSAK is so much more than a POP3 client. You may like to pop over to www.gsak.net for more info.

[amberel]

Of course, GSAK is so much more than a POP3 client. You may like to pop over to www.gsak.net for more info.

Hi Alan,

Thanks for those thoughts. I have now had a quick look, but for the most part either my own program does it already or it's not something I want to do. But it is the sort of program to have in the back of the mind when new requirements arise.

Your comments have given me food for thought. I could make it a bit quicker by doing the unzipping in my Delphi app, but with a little bit more work I could also take the approach you suggested with GSAK by putting a POP3 client in my Delphi app, meaning the the whole import from mail message to SQL Server and to the screen would be just one click.

Rgds, Andy

[moote01]

Would the solution be to write your own POP front end for your SQL database, and forget about trying to automate Outlook. I know that this is possible on both MS-SQL and also in MySQL, and no doubt possible in many other SQL databases.

Then send all your PQ's to a new email account and use that new account's details in your code.

[amberel]

Would the solution be to write your own POP front end for your SQL database, and forget about trying to automate Outlook. I know that this is possible on both MS-SQL and also in MySQL, and no doubt possible in many other SQL databases.

Then send all your PQ's to a new email account and use that new account's details in your code.

That's more or less what I was saying in my last post, except it will be easier for me to do it in Delphi than in SQL Server. This is partly because the existing import code is written in Delphi, and partly because I'm much more fluent in Delphi than I am in SQL.

I admit I hadn't realised SQL Server could even do that - it constantly amazes me what is possible. However, SQL code to do anything much out of the ordinary always appears to me to be byzantine in its obscurity and complexity, so I keep the SQL simple and confined to just the business of storing and retrieving the data, and do the general purpose stuff in a language that is better suited to it .

Rgds, Andy

[fraggle69]

I've found python to be a very effective scripting language which has support for a whole range of different tasks. I think you're on the right track, by ignoring outlook and going straight into your pop3 account and downloading the .zip files. You could maybe set a rule in outlook not to download those specific files from the server.
As I am more hardware than hardcore software guru, it suits me because I find alot of the hard work has already been done and if I get stuck the support is out of this world. I tend to find that I just need to tweak things a little to get what I want.

take it easy

[Lakeuk]

That's more or less what I was saying in my last post, except it will be easier for me to do it in Delphi than in SQL Server. This is partly because the existing import code is written in Delphi, and partly because I'm much more fluent in Delphi than I am in SQL.

I admit I hadn't realised SQL Server could even do that - it constantly amazes me what is possible. However, SQL code to do anything much out of the ordinary always appears to me to be byzantine in its obscurity and complexity, so I keep the SQL simple and confined to just the business of storing and retrieving the data, and do the general purpose stuff in a language that is better suited to it .

Rgds, Andy

As Delphi is the language you're most used to then a module to access your pop3 mail server from delphi will be the best route. Putting keywords "delphi pop3 how-to" into google brings up a few results.

I'd recommend learning basic sql it'll help you alot, couple of useful links for you.
http://www.w3schools.com/sql/default.asp
http://www.databasejournal.com/featu...nt.php/3527921

I personal use Perl / MySQL / Uniserver for storing/manipulation all my geocaching data as well as trig points, hill bagging - all mapped on googlemap. This setup is stored on a usb flash so I can use it any where with a PC plus internet access if I want to use googlemaps

[amberel]

> As Delphi is the language you're most used to then a module to access
> your pop3 mail server from delphi will be the best route. Putting
> keywords "delphi pop3 how-to" into google brings up a few results.

I've got the FastNet components, though never used them before.

> I'd recommend learning basic sql it'll help you alot

I'm fine on basic SQL, create, alter, drop, insert into, update, delete, select, joins, etc. Where I'm weaker is the more obscure functions and in combining them into very complex expressions. Generally I don't find it limiting, but I'm aware that occasionally it is necessary for performance reasons.

Rgds, Andy

[fraggle69]

As Delphi is the language you're most used to then a module to access your pop3 mail server from delphi will be the best route. Putting keywords "delphi pop3 how-to" into google brings up a few results.

I'd recommend learning basic sql it'll help you alot, couple of useful links for you.
http://www.w3schools.com/sql/default.asp
http://www.databasejournal.com/featu...nt.php/3527921

I personal use Perl / MySQL / Uniserver for storing/manipulation all my geocaching data as well as trig points, hill bagging - all mapped on googlemap. This setup is stored on a usb flash so I can use it any where with a PC plus internet access if I want to use googlemaps

wow, that's pretty neat, can anyone give me any help/direction on how you manipulate your data to show up on googlemaps?