May 01, 2014

Did you know you can access and manipulate any Office application from within Visual Basic (VB) code? In today’s blog post, we’ll go through the basic steps to connect to different Office applications through Excel VBA. Accessing Microsoft Word All you have to do is create an object of type ‘Word.Application’ and from then on, that object represents the Word Application. You can now use it to open documents and almost all features of Word will be available to you. Before you create Word.Application, you will need to make a reference to Microsoft’s Word Object Library, otherwise VB will not have the Word objects available. In other words, you need to tell VB where to find Word features that you’ll be going to use. To make a reference, go to Tools then to References, and from the list tick next to ‘Microsoft Word xx.0 Object Library.’ Now create a sub and enter this line:

Dim objWord as New Word.Application

From now on ‘objWord’ represents the Word application so you can do something like this:

objWord.Documents.Add objWord.Visible = True

When you run the sub, you should see a Word application pop up called Document1. Accessing Microsoft Excel Similarly, begin by creating a reference as you did with Word. This time, input this text in your sub:

Dim objExcel as New Excel.Application

From now on ‘objExcel’ represents the new Excel application so you can continue with something like this to;

objExcel.Workbooks.Add objExcel.Visible = True

Accessing Microsoft Outlook Again, create a reference and in the sub, enter the below text:

Dim objOl as New Outlook.Application

As with Word and Excel, ‘objOl’ now represents the new Outlook application. To send an email, you can set up the following code.

Dim outMail as Object Set outMail = objOl.CreateItem(0)     With outMail         .to = “[email protected]”         .CC = “”         .BCC = “”         .Subject = “This is the Subject line”         .Body = “Hi there”         .Attachments.Add ActiveWorkbook.FullName         ‘.Attachments.Add (“C:test.txt”)         .Send   ‘or use .Display     End With     Set outMail = Nothing     Set outApp = Nothing