Excel tips and tricks – Project Management: How to send an email automatically through Excel VBA

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….

insideiimtasklist

 

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

insideiimemailmacro

4.  Close this window, and you will see the code for the EmailSender Method….

insideiimemailmacro2

 

Start playing with the code and enjoy…..

Read this article by Anup Kumar Agarwal on Excel Tips and Tricks

 

Comments

3 comments

Argha Narayan De

Hi, great article on excel automation. However, there are couple of things in the code which I would like to highlight in order to make it more generic.
1) The code assumes that there are only 3 rows of data (A2:A5). If any other row is automatically added, then the code would need to be changed again, which can be trouble some for people who are not conversant with programming.
2) The email sender sub routine is dealing with outlook only. If the user uses any other email app (mozilla thunderbird etc.), then the code might throw errors. Again changes has to be made programmatically.
Resolutions:
1) Code:
Dim sh as worksheet
Dim n as integer
set sh = ActiveWorkbook.ActiveSheet
n=2
while sh.Range(“A” & n) “”
‘Prog logic
n = n+1
wend

2) Use workbook.sendmail function: this is a generic method and can be used with any email app. However, options like CC, BCC or setting the body of the mail can not be done. For that outlook has to be used. But this can be useful for someone who is not using outlook. Syntax: ActiveWorkbook.Sendmail abc@gmail.com “Mail subject”
if the requirement is to send the mail to multiple recipients at one go, then the mail ids can be stored in an array and the array can be used in sendmail.
Hope this helps

Argha Narayan De

For some reason not-equality sign ( less than symbol followed by greater than symbol) is not getting printed in the comments. The while loop should be:
while sh.Range(“A” & n) not-equality symbol “”