One of the most abused tools in corporate circles is also one of the most potent, if you know how to use it. I am talking about Microsoft Excel. That said, it is ridiculous to see investment banking jobs specify in their JD that the candidate must be proficient with Word, Power Point and Excel. Proficiency in these areas is probably one of the most easily reproducible skills, and I have no idea why they are so much in demand. Nonetheless, For most MBA grads, at least for a few years after graduation, professional life consists of an endless march down the path of updating excel sheets and making power point presentations.
Selling Excel and Power Point to normal users (as against Power Users) is like a putting a nuclear weapon in the hands of someone who's preferred fighting technique is to hack his opponents down with a sword. This is the single-most important reason why Power Point is misused - it is a powerful tool, intended for visual communication, placed in the hands of people who don't have the slightest clue about the basics of visual communication.
Coming to excel - a much more sinister tool than power point. Unlike Power Point whose torture of our souls is a visible event we endure with clenched fists and gritted teeth, the impact of excel on our lives is much more subliminal. Excel sheets are subjected to much less stringent quality checks, and nobody tracks or knows about anything going wrong........until things blow up. And when they do, the results are often spectacular.
Did you know that the JP Morgan London Whale Trading Loss was caused by an error in an Excel SpreadSheet?
Influential Harvard Professors have been laid low by spreadsheet errors
Now coming to the point. Excel is a really powerful tool. And I will be demonstrating scenarios where it can be tremendously useful as an automation tool to increase your productivity.
How to automate sending a mail through excel
Imagine you have a scenario where everyone on the team is assigned a task with a deadline. You would want to automatically send them a mail when they are two days away from the deadline. Here's how the spreadsheet looks....
Our challenge is to
send an automated email to anyone whose task is pending and the due date is less than 3 days away (less than or equal to).
In the above case, our system should send an email to John and Ram (this post having been written on 12-June-2014).
Here's how the Macro automation would work
1. Create an Outlook Application in your code ....(To reach this rarefied world, you will have to create a Macro - Go to
View Tab, Click on Macros, and say Create Macro. You can have a look at the code)
Set outlookApp = CreateObject("Outlook.Application")
This is required to launch Outlook.
2. Now, you need to create an Email Item within this application.
Set outlookMailItem = olApp.CreateItem(0)
3. Now for this mailItem, you can set properties like BCC, Subject, Mail body, Attachments, and then say Send.
Its as simple as that...Really.
4. Now, for the file shown above, you can loop through the data and repeat the above steps. I am assuming that you would be fairly familiar with
for loops, and how to modify them. I have left just enough obvious data in my demo so that you can figure out how it works, even if you are not a proficient programmer.
Use the attachment and let me know if you have any queries. If you have a better approach, or some cool ideas, do share your knowledge in the comments section. You will need to have outlook open while running this automation.
The file can be accessed by clicking on the link below
InsideIIM Email Demo
Steps:
1. Open the Excel Sheet. Edit the email addresses in the second column, and click on Send Email to experience the macro.
2. To experiment, go to the
View Tab, click on Macros and say View Macros.
3. Click on SendEmail...
You will see the source code
4. Close this window, and you will see the code for the EmailSender Method....
Start playing with the code and enjoy.....
Read this article by
Anup Kumar Agarwal on Excel Tips and Tricks
