How to access Office Applications from within VBA

 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

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top