Here’s what I’d like to do. Write a script/program/some-code/whateverthekidsarecallingitthesedays that:
Creates a spreadsheet in Excel titled hereinafter ASMNT. It is okay if this overwrites an already existing ASMNT Excel file.
2 Finds every message in my outlook inbox with “assessment results” in the title (let’s say going back to a date I would specify on running the thing).
For each found email:
3a. Opens any spreadsheet attached to said email
3b. Finds the rows in that spreadsheet attached to that email which contain my name
3c. Copies those rows all into new rows in ASMNT
3d. Closes that spreadsheet attached to that email
3e. Archives that email
I’ve successfully learned bits and pieces of languages before for specific purposes (for example, learned the basics of object oriented programming in Python to take care of a personal project involving string manipulation). So to a certain level of complexity, I can totally learn to do things.
But do the tools exist for creating a script like the one I just described above, where said tools are easily accessible to someone who doesn’t do coding every day?
Or would such a thing be a pipe dream?
(I shouldn’t really be trying to figure out how to do this–I only get into the situation where I’d like to use such a script when I’ve been a Very Naughty Boy and am far behind on a certain aspect of my workload…)
Assuming that Microsoft is still doing it, they used to expose all of the functionality of their Office products via COM and had documentation on how to do the sorts of things that you’re asking to do via “Windows Script Host”:
It certainly sounds like the sort of task that one could do, though, I can’t guarantee that the objects will have the specific APIs that you need.
I have some vba code at work that runs in excel and saves the selected email in outlook to a given folder. It’s not everything you need but it is certainly part of the puzzle. I get back to work in 12 hours so will give it to you then. The rest is doable with a bit of effort.
Just to warn you, Windows Host Script isn’t a particularly popular platform for coding. With a lot of hunting you can find the identifiers for the COM objects you need and either find the API listed (under some different name) in MSDN or dump the COM interface, but it’s a lot of hunting.
You can do some pretty cool things with almost no code. But you may spend hours and days just trying to find documentation and examples.
Microsoft has the tools available for free to do all of this via Office interop. I used to do this type of office application automation years back before switching to web application development.
So, it’s been a while, but it sounds like you want to create an outlook add-in that will interact with emails by saving the contents of excel attachments to an excel file, and archive the email.
There are tons of tutorials out there, google .net outlook add-in. VB.net and C# are the languages supported.
Rather than using Windows Scripting Host / vb script / c script to open Excel, and Outlook, and Excel, I’d use Excel to open Outlook.
You can use any scripting language You will find the most examples are VBS (for outlook) and Excel VBA.
You can normally import VB Script into Excel VBA. Going the other way is a little more difficult.
You could use Outlook as the VBA host, but that is much less common, and slightly more difficult, so you’d have look much harder to get examples.
You can use Open Office as the scripting host: it hosts a VBA compatible language, and Python, and some other scripting languages, but again, most of the examples you find are going to be VBS (Outlook) and VBA (Excel), and although OO is a much better Python host than Excel is, it’s a f-g irritating VBA host.
The method I would use would be: Open Outlook. Open Spreadsheet. Press button, or select menu item, or run macro. Excel VBA deletetes Sheet2, and fills it again using spreadsheets from mail messages.
The main loop would be the loop which opens every email message and gets the attachement Inside the main loop I’d open the attachement (It’s fast and easy to open a spreadsheet in Excel) Then I’d search for the line and copy the line to Sheet2
Each element of this has many examples on the web. Stringing them all together and ironing out the bugs would take me a couple of hours, and would probably take longer for you.
As much as I hate VBA, I have to agree with Melbourne that an Excel-hosted VBA script is really the right tool for this application. It certainly could be done with Python, openpyxl, and COM bindings, but if your end goal is just to transfer data from Excel spreadsheet to Excel spreadsheet, it’s probably not worth the effort. On the other hand, if you want to extract data from an HDF5 file and dump it into Excel or vice versa, Python/openpyxl or a similar independent scripting tool is definitely the way to go, especially if you are going to do this repeatedly.
(microsoft Clippy voice) "It sounds like you’re attempting to collate all your previous employee assessments via automated search of your inbox. Would you like some assistance on:
[ul]
[li]Doing that thing[/li][li]Getting another job[/li][li]Committing suicide[/li][li]Committing suicide by pastry[/li][li]Committing another job[/li][/ul]
Private Sub AttEmails()
Dim olApp As Outlook.Application
Dim olExp As Outlook.Explorer
Dim olSel As Outlook.Selection
Dim olMail As Outlook.MailItem
Dim i As Long
Dim savePath As String
Set olApp = GetObject(, "Outlook.Application")
Set olExp = olApp.ActiveExplorer
Set olSel = olExp.Selection
For i = 1 To olSel.Count
Set olMail = olSel.Item(i)
savePath = "C:\\somevalid\save\path"
Call olMail.SaveAs(savePath, olMSG)
Next i
End Sub
The above code will take all the selected emails (you can select more than 1) and save them to the specified path.
As I said, it is a piece of the puzzle. Now you know how to get excel to speak to outlook and you have an idea of how the various objects in outlook are structured.
To run the above, you need to paste the code into a module in Excel and you need to add a reference to the Microsoft Outlook object library.
You might also consider approaching this outside of the Office ecosystem. You’re just looking at emails, attachments, and spreadsheets – all of which are easily handlable by standard scripting languages, without any need to involve Office interop, Win32 API, or the needless complexities of the GUI.
If you ignore all that, it’s basically “Find all the .xslx in this attachment folder. Read it into memory using a reader library. Parse the CSV for my name. Append to the destination file. After the loop, convert destination file to Excel (or just open the CSV in Excel).”
That script, once written, will probably run in 10 seconds rather than the many minutes it will take GUI automation to open each email in Outlook and interact with it. Office automation is built for user-facing business apps. You’re just doing an email scrape and so can ignore all that complexity if you want.
I just had a thought that selecting the e-mails might be most easily done by manually creating a new folder in Outlook, and manually creating an Outlook rule that moves all e-mails with “assessment results” to that folder (I think you can also specify who they’re from, etc.). That way your script can just pull everything from that folder, and, if you can’t automate the archiving and date selecting, you can just run the script, then manually achive everything in the folder, then leave it alone until the next time you run the script when there will be only the new e-mails in the folder (which you can archive after running the script, etc.)
As a bonus, it declutters your regular in-box so you don’t have to skip over the Assessment Results e-mails.
