writing html in vba: but why and how?

writing html is really easy

HTML is the language in which structured websites are written. HTML was first created by Tim Berners-Lee, Robert Cailliau, and others starting in 1989. It stands for HyperText Markup Language.

To write HTML you need two key concepts of markup tools: tags and attributes. There are plenty of HTML tutorials on the web and this really is not a HTML tutorial so let’s skip it.

But as a general concept, we will discuss the concept of Tags and Attributes. Tags are used to mark up the start of an HTML element and they are usually enclosed in angle brackets. An example of a tag is: <h1>. Most tags must be opened <h1> and closed </h1> in order to function. Attributes contain additional pieces of information. Attributes take the form of an opening tag and additional info is placed inside. An example of an attribute is: <img src=”mydog.jpg” alt=”A photo of my dog.”>

A golden rule to remember:
The vast majority of tags must be opened (<tag>) and closed (</tag>) with the element information such as a title or text resting between the tags. When using multiple tags, the tags must be closed in the order in which they were opened. For example: <strong><em>This is really important!</em></strong>

but, why would you want to write html in vba?

Yes, why would one want to write html using VBA? There are plenty of HTML editors like: Sublime, Notepad ++, Komodo, Visual Studio etc. Well for start, using VBA will allow you to use the plethora of the library and references available in MS Office Product. On the other hand when you use Office Products like Excel, Word, Outlook and Access for your specific purpose, you can dynamically refer to them within the HTML body and use them. So why you really want to write HTML in VBA really comes to being more dynamic in generating the desired results easily.

use case: for intense excel users

Have you ever come to a situation where you need to produce multiple files, emails or documents using the existing spreadsheet of the vast amount of consistent datas? Or in other words, making the computer do your boring tasks perfectly. Following could be the situation where you could use the method of writing HTML from VBA might be relevant for you:

  1. Mailing payment advice, payment slips to large number of vendors or employees
    Using MS Excel, MS Outlook, HTML and VBA
  2. Following up with clients for invoices or outstanding payments
    Using MS Access, MS Outlook, HTML and VBA
  3. Generating multiple HTML pages for the purpose of publishing dynamic contents in web
    Using MS Excel, MS Word, HTML and VBA
  4. Generating PDFs and Reports
    Using MS Excel, MS Word, HTML and VBA

an instance of implementation

Here I will cover an example of using the MS Excel, MS Outlook, HTML and VBA to send a list of employees their payslips for the payroll month based on the data and calculations in an excel spreadsheet.

Step 1: Ready your Calculations

Step 1 doesn’t really have anything to do with any coding. As discussed above, firstly you need to prepare your data and spreadsheets with the adequate formatting and calculations. In our example here we have a list of employees who need the pay slips emailed to their email addresses listed in some place (in our case, the “total” page).

Step 2: Setup of your Products

We are going to be using MS Excel, MS Outlook, HTML and VBA for this purpose. So, have your products setup in your computer. Setup a functioning email into your Outlook from where you want to be sending your emails. Also log into your products to make them fully functional.

Step 3: Visualize your HTML Requirements

The next step is to visualize what and how you want to send the mails to your employees. You probably want to set up some essential details like Company Name, Employee Name, Payroll Month, Disclaimers, and contents. You might need some HTML text formatting, table structuring, borders, colors and so on. For best HTML tutorials with examples visit W3Schools to get ideas about any specific tags and syntax related to HTML.

Step 4: Codify HTML into VBA

The next step is to codify the HTML requirement into VBA. An example of the code I used to mail the said pay slips to the employees is given below. You can find the text to the code in this link. 

Step 5: Set up Mailer Buttons and Send Emails

Once you finished the code and codified the HTMLs, you can then use the button tools in developer tabs or the shortcuts in the quick access toolbar for the action to mail the pay slips. Your Outlook Desktop Client will open up and the mails so designed in your structured HTML will be sent to your mailing lists.