
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.ApplicationFrom now on ‘objWord’ represents the Word application so you can do something like this:
objWord.Documents.Add objWord.Visible = TrueWhen 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.ApplicationFrom now on ‘objExcel’ represents the new Excel application so you can continue with something like this to;
objExcel.Workbooks.Add objExcel.Visible = TrueAccessing Microsoft Outlook Again, create a reference and in the sub, enter the below text:
Dim objOl as New Outlook.ApplicationAs 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 NowNext up:
- Turn the heat up on your text in Photoshop
- An introduction to cloud computing
- Calculate the Resources Standard Rate in Microsoft Project
- Response Groups in Lync Server 2013
- Becoming a great workplace trainer starts with three words (Part 1)
- The Exchange Admin Center (EAC) of Exchange 2013 – It’s new!
- How to create fillable forms in Microsoft Word
- EAs and PAs: Asking your boss the right questions
- VBA Excel: Finding the last row of a worksheet (Part 1)
- Implementing Big Data Solutions in SQL Server 2014
Previously
- Dependency Injection in C#
- Customise the Quick Access Toolbar in Microsoft Office
- What’s new in Microsoft Exchange Server 2013 SP1
- ANZAC Day – Lest We Forget
- How to link two lists in Microsoft SharePoint
- A new manager’s first conversations
- PowerShell is for infrastructure types…right.
- Have an eggsellent Easter!
- Insert an online video into Word 2013
- They won’t take their shoes off!