New Posts  All Forums:Forum Nav:

Automation Question

post #1 of 6
10/28/12 at 5:47pm
Thread Starter 
This is something that has been running through my mind all day and I am not sure about how to go into implementing it.

Here is what I want to do, my boss sends out a work schedule to everyone's e-mail and everyone I work with is on the schedule.
I would like set up a way to search my e-mail ( by subject line or any other method to narrow down the field to the correct e-mail), download the attachment (usually in excel format)
export in a CSV format that is formatted in a way that Google docs can understand and then import that CSV file into the Lightning add on for Thunderbird (which is linked to my Google calender).

Also it is worth noting I only want to import my schedule into my calender not my co-workers.

So far the only thing I can think of a quick macro script for the whole process but I would really like to script this out and I do not know where to begin, any input would be appreciated.

-Mac
AMD
(13 items)
  		  
CPUMotherboardGraphicsRAM
Athon 64 x2 6400 Asus M2n32-SLI Deluxe Sapphire HD Radeon 4870 x2 4 GB Gskill DDR2 
Hard DriveOSKeyboardPower
150 Gb WD raptor/500 Gb Windows 7 Ultimate 64 bit Logitech G15 Antec Earthwatts/ 650 
CaseMouse
Big Arse Rocketfish Logitech G5 
View all
  hide details  
Reply
AMD
(13 items)
  		  
CPUMotherboardGraphicsRAM
Athon 64 x2 6400 Asus M2n32-SLI Deluxe Sapphire HD Radeon 4870 x2 4 GB Gskill DDR2 
Hard DriveOSKeyboardPower
150 Gb WD raptor/500 Gb Windows 7 Ultimate 64 bit Logitech G15 Antec Earthwatts/ 650 
CaseMouse
Big Arse Rocketfish Logitech G5 
View all
  hide details  
Reply
post #2 of 6
10/29/12 at 7:08am
This would be anything but a quick macro. I've written routines exactly like this before and they take hours to code and a considerable about of time to execute as well. There really isn't an easy way to do this I'm afraid.

However, if you do still want to do this then here's how you'd go about it (I'll explain the process, but I don't have time to write the code):

  1. firstly, you need to create an outlook object then use that to connect to your inbox (that's a few lines of code in itself)
  2. copy every e-mail into a temporary folder. you need to do this because outlook indexes e-mails on the fly, so if you receive an e-mail while you're processing your e-mails, you'll potentially end up with duplicate spreadsheets.
  3. next you need a needed loop: one to scan through every e-mail and one to then scan through every attachments within each e-mail
  4. in every instance of a .xls, either scan the e-mail for keywords or defer checking of the spreadsheet.
  5. save the spreadsheet (if the previous stage was successful)
  6. then move your e-mails back to your inbox - except the ones with timesheets, move those into a separate "processed" folder so they don't get reprocessed again next time.
  7. now either create an excel object or run a new macro in excel
  8. create a file system object
  9. cycle through each .xls within your output directory, opening them one by one
  10. if the checking had been deferred, then open spreadsheet and check key cells for identifiers that it is compatible time sheet
  11. do any required post processing. eg copy key data into a new blank spreadsheet with the correct layout
  12. save as csv
  13. delete processed spreadsheets
  14. ?????
  15. PROFIT

Depending on scale of your data (eg if you have a few hundred e-mails), you'd likely need a some kind of basic GUI displaying the status as VBA isn't the quickest of languages, so you need to know how far along the who whole process is.

You'll need some error handling as well; to manage if spreadsheets are password protected etc.

So all in all, you're looking at a few days worth of coding as there's so many varables you need to account for (as is the case when dealing with unsanatised data). so a better option would be to ask your manager to send CSV documents rather than XLS
Reply
Reply
post #3 of 6
10/29/12 at 5:31pm
Thread Starter 
Thank you for the information
Quote:
a better option would be to ask your manager to send CSV documents rather than XLS

This will prolly not happen. I was also thinking about doing it in python. I would just set up a scheduled event either using event scheduler or cron (depending on what is I want to run it on haven't decided yet)

I can across this

http://www.theverge.com/2012/4/30/2989427/send-gmail-attachments-to-drive-script

which should handle the automatic downloading of attachment though it is based the tag given to the e-mail and I am not sure if I can auto tag e-mails based on the sender information but that is another problem atm.

After I get the files on my drive I was looking into an excel macro to automatically save the entire schedule as a CSV and the use the python CSV API

http://docs.python.org/2/library/csv.html

to read and format the file the way I need it to be ( this will be the hard part for me since It has been a year or so since I have worked with python for an extended amount of time and even then what experience I have with it was only in a classroom setting) I am not even sure if this function will do what I want it since I only glanced over it.

Then I have to find a way to automate the importation into Lightning which I have not even looked into yet.

I understand that this may take me awhile but it's intresting atm and it is keeping my mind busy so at least it should be fun.


-Mac
AMD
(13 items)
  		  
CPUMotherboardGraphicsRAM
Athon 64 x2 6400 Asus M2n32-SLI Deluxe Sapphire HD Radeon 4870 x2 4 GB Gskill DDR2 
Hard DriveOSKeyboardPower
150 Gb WD raptor/500 Gb Windows 7 Ultimate 64 bit Logitech G15 Antec Earthwatts/ 650 
CaseMouse
Big Arse Rocketfish Logitech G5 
View all
  hide details  
Reply
AMD
(13 items)
  		  
CPUMotherboardGraphicsRAM
Athon 64 x2 6400 Asus M2n32-SLI Deluxe Sapphire HD Radeon 4870 x2 4 GB Gskill DDR2 
Hard DriveOSKeyboardPower
150 Gb WD raptor/500 Gb Windows 7 Ultimate 64 bit Logitech G15 Antec Earthwatts/ 650 
CaseMouse
Big Arse Rocketfish Logitech G5 
View all
  hide details  
Reply
post #4 of 6
10/30/12 at 1:36am
I've not done much in python outside of basic XBMC addons, so I probably couldn't help you there. Just be aware that you need to differentiate between e-mails who's attachments you've already handled during a previous execution of the script, and new ones.

Sounds like you'll have some fun with this though smile.gif
Edited by Plan9 - 10/30/12 at 1:39am
Reply
Reply
post #5 of 6
11/8/12 at 6:39am
I'd go with VBA and I'm sure there would be some batch somewhere in there too. I'd probably google each step to see how to automate each step on it's own and then batch it together somehow. I'd start with google.
First Gaming Rig
(16 items)
  		  
CPUMotherboardGraphicsHard Drive
Intel i7 2600k Asrock P67 Pro 3 SE GTX 580 WD 10EALX 
Optical DriveCoolingOSMonitor
ASUS DRW Havik 140 Windows 7 Ultimate ASUS VH228T 
MonitorMonitorKeyboardPower
ASUS VH228T Toshiba 32RV600A Packard Bell Aero Cool Strike X 1100w 
CaseMouseMouse PadAudio
Asus Antec Logitech MX518 razer goliathus Creative 2.1 
View all
  hide details  
Reply
First Gaming Rig
(16 items)
  		  
CPUMotherboardGraphicsHard Drive
Intel i7 2600k Asrock P67 Pro 3 SE GTX 580 WD 10EALX 
Optical DriveCoolingOSMonitor
ASUS DRW Havik 140 Windows 7 Ultimate ASUS VH228T 
MonitorMonitorKeyboardPower
ASUS VH228T Toshiba 32RV600A Packard Bell Aero Cool Strike X 1100w 
CaseMouseMouse PadAudio
Asus Antec Logitech MX518 razer goliathus Creative 2.1 
View all
  hide details  
Reply
post #6 of 6
11/8/12 at 7:44am
Quote:
Originally Posted by nooboc2012 View Post

I'd go with VBA and I'm sure there would be some batch somewhere in there too. I'd probably google each step to see how to automate each step on it's own and then batch it together somehow. I'd start with google.
You don't need any batching. In fact VBA is quite significantly more powerful than Windows batch files (even in spite of how crappy VBA is)
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming