Competitions

CAT Prep

Upskill

Placements

MBA Co'26

RTI Response

Rankings

Score Vs. %ile

Salaries

Campus Tour

How to send an email automatically through Excel VBA

Jun 12, 2014 | 4 minutes |

Join InsideIIM GOLD

Webinars & Workshops

Compare B-Schools

Free CAT Course

Take Free Mock Tests

Upskill With AltUni

CAT Study Planner

Final 19 Days to CAT 2024 Test-26

Participants: 125

Final 20 Days to CAT 2024 Test-26

Participants: 175

Final 21 Days to CAT 2024 Test-25

Participants: 145

Final 22 Days to CAT 2024 Test-24

Participants: 164

Final 23 Days to CAT 2024 Test-23

Participants: 102

Final 24 Days to CAT 2024 Test-22

Participants: 154

Final 25 Days to CAT 2024 Test-21

Participants: 144

Final 26 Days to CAT 2024 Test-20

Participants: 193

Final 27 Days to CAT 2024 Test-19

Participants: 170

Final 28 Days to CAT 2024 Test-18

Participants: 168

Final 29 Days to CAT 2024 Test-17

Participants: 175

Final 30 Days to CAT 2024 Test-16

Participants: 193

Final 31 Days to CAT 2024 Test-15

Participants: 183

Final 32 Days to CAT 2024 Test-14

Participants: 190

Final 33 Days to CAT 2024 Test-13

Participants: 191

Final 34 Days to CAT 2024 Test-12

Participants: 226

CAT 2017 VARC SLOT- 2

Participants: 311

CAT 2017 DILR SLOT- 2

Participants: 155

CAT 2017 VARC SLOT- 1

Participants: 297

CAT 2017 DILR SLOT- 1

Participants: 105

CAT 2017 QUANT SLOT 1

Participants: 150

CAT 2017 QUANT SLOT 2

Participants: 69

CAT 2018 QUANT SLOT 2

Participants: 65

CAT 2018 QUANT SLOT 1

Participants: 106

CAT 2018 DILR SLOT- 2

Participants: 57

CAT 2018 DILR SLOT- 1

Participants: 81

CAT 2018 VARC SLOT- 2

Participants: 202

CAT 2018 VARC SLOT- 1

Participants: 294

Final 35 Days to CAT 2024 Test-11

Participants: 154

Final 36 Days to CAT 2024 Test-10

Participants: 130

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