A simple program like Microsoft Excel 2010 can be used as a powerful tool by manipulating the software. Microsoft has published several “how to” guides such as this one Getting Started with VBA in Excel 2010. They are very good articles, and I am not trying to beat them. After all, they wrote the software anyway. I just want to introduce Excel as a powerful programing tool for home users in baby steps. Let’s see if I can do it!

Before we do anything, let me introduce you to the helpful colour code or coding! All the comments must be started with the character “‘” and is coloured in green by the editor. The iterations and actions attached to a set is coloured in black (like regular texts). The red will only appear once you step into the dark side. I intentionally left “End Su” at the end to provide an example of an error. By highlighting it red send a message to the coder that something is wrong. You will also get a pop-up warning as you leave the troublesome line (unless you have disabled it).

Visual Basic for Applications

Some of you may have used a type of add-on to MS Office products known as Macros. Usually you either download a Macro from somewhere or someone gave it to you because you have some complicated data that needs to be processed. Macros are exactly that; they are flexible programing codes that can work with MS Excel to process data and generate useful outputs. For example, at the end of this article, I will show you how to write a Macro to send mass emails with user specific data. These Macros are written in a programming language known as Visual Basic (VB). It is the high-level programming language of Basic (Which I am not going to go into at this time). All Microsoft Excel products shipped with VB support including the Student Versions of the software.

In order to program in Macro, you need to enable the Developer Options in MS Excel:

1. Right click on a ribbon (not on a an item)
2. Select Customize the Ribbon
3. Select Developer in the Excel Options window and click OK

You should now have the following Ribbon.

Security Concerns of VB

Like any other programming language, VB also has it’s own set of problems. Since all computers use code to operate and VB is a type of code, bad guys can use VB Macro scripts to steal your data and even harm your computer. Therefore Microsoft have disabled Macros by default. Before we do anything, we need to make sure that our security settings are desirable for VB programming. Let me assure you that unless you download an Excel file from an unknown source, for this tutorial you should not concern about the security (well, you are the one who writes the program anyway!). Go to the Developer Ribbon you just created and click on Macro Security under Code section.

I would recommend using Disable all macros with notifications option. This way you will protect yourself from unwanted attacks, but still allow you to program in VB.

Programming

Since almost all VB codes are similar to each other, I don’t see the value in writing a new code. So, I copied a code from Microsoft KB. However, I may improve the code in near future.

The following code will call in the Outlook (or your default mail program) to send an email with specific data from a cell. Once the script is in RUN, it will automatically execute the task. However, if you have a security setting to prevent this automation, a warning may pop up.

Sub Send_Range()
   ' Comments, in green, must start with "'".
   ' Select the range of cells on the active worksheet.
   ActiveSheet.Range("A1:B5").Select
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
      .Item.Send
   End With
End Sub

Next time, advance cell based email generation. I am soooo excited!