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
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
Chat
Comments
Argha 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
15 Jun 2014, 06.15 PM
Argha De
A small correction: while sh.Range(“A” & n) ""
15 Jun 2014, 06.21 PM
Argha 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 “”
15 Jun 2014, 06.24 PM