Using VBA to Add Text
You would think that something as simple as adding text to a document, spreadsheet cell, slide body, or an e-mail message should be fairly uniform, and that all the Office applications would behave in more or less the same way.
Not a chance.
Adding Text to a Word Document with VBA
In VBA/Word, the simplest way to add text to a document is with the TypeText method. For example, the small program in Listing 40.1 creates a new document, adds a return address and date, and then exits, allowing the typist to continue where the macro left off.
Listing 40.1. TypeReturnAddress
Note that paragraph marks must be explicitly "typed" for them to appear in the document.
The Format() function takes a value (in the case of Listing 40.1, Now is the current date and time), and a formatting pattern ("mmmm d, yyyy" specifies the name of the month, date, and then a comma followed by the four-digit year). It produces a string formatted according to the pattern—for example, January 23, 2001. For more details, open the VBA Editor, click to position the insertion point inside the Format() command, and press F1.
Adding Text to an Excel Workbook with VBA
Adding text to Excel cells similarly requires little effort as shown in Listing 40.2.
Listing 40.2. TypeColumnHeadings
Public Sub TypeColumnHeadings()
Cells(1, 1) = "A Column Head"
Range("B1") = "B Column Head"
Cells(1, 3) = "C Column Head"
Range("D1") = "D Column Head"
End Sub
|
Both the Cells() property and the Range() property work equally well: With the former, you must specify the numeric location of the cell (with the column first, then the row, so B3, for example, becomes Cells(2,3)); with the latter, you have to refer to the range with a standard address, such as A1:C3.
A Range designation might include $ (dollar signs)—you can use the dollar signs as you would to indicate an absolute cell or range reference—but VBA/Excel ignores them.
Adding Text to an Outlook E-mail Message with VBA
Outlook works entirely differently from the rest of the Office suite. In an Outlook e-mail message, the contents of the Subject and Body are properties of the message itself.
This program creates a new e-mail message, inserts the "To:" and "Subject:" fields, types text into the body of the message, and then sends it:
Set Message = CreateItem(olMailItem)
Message.Recipients.Add ("[email protected]")
Message.Subject = "Meeting tonight at 8:00"
Message.Body = "Is that OK with you?"
Message.Send
Any attempt to access the Contacts list—in this case, running the Message.Recipients.Add command—triggers the macro virus warning shown in Figure 40.11. The user must click OK to proceed. This protection was built into Outlook as a response to the infamous ILOVEYOU virus, which uses this approach to send e-mail messages to everyone on an infected computer's Contacts list. The virus caused untold embarrassment when it swept like wildfire through Microsoft's Redmond campus—among people who should, supposedly, know better.
Figure 40.11. Outlook warns you when a macro attempts to get at entries in the Contacts list.
Unfortunately, there's no way to disable this particular warning message: If you run a macro that hits the Contacts list repeatedly, the most any user can do is disable the message for a specified number of minutes. The protection is particularly vexing for people who synchronize handheld computers with their Outlook Contacts list—you can't turn the message off. Outlook's security lockdown has been roundly criticized as a draconian, simplistic solution to a complex problem.
Outlook also warns the user each time a program attempts to add a message to the Outbox—another legacy of the ILOVEYOU debacle. The statement Message.Send in the previous macro triggers this warning: "A program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected, it might be a virus and you should choose 'No'." The user has to click Yes before the message will be deposited in the Outbox.
Tip from 
It's important that you be aware of these severe limitations before you attempt to write an Outlook macro. Any time your program tries to access Contacts data, the user will have to manually grant permission. Any time your program tries to put a message in the Outbox, permission must be given as well. Until some enterprising virus writer figures out a way to bypass these severe security restrictions, people using your macros will have no choice but to click away.
→ Outlook has no analog to the Trusted Sources list, nor does it recognize digital signatures, see "Macro Security".
Adding Text to a PowerPoint Presentation with VBA
PowerPoint uses the InsertBefore and InsertAfter methods to place text on a slide. Un fortunately, PowerPoint's objects work completely differently from Word's or Excel's, so you'll have an interesting time trying to get off the ground.
For example, to insert text before the currently selected text on a slide, you have to use this monstrosity:
ActiveWindow.Selection.TextRange.InsertBefore "Some text"
Word has a Selection object—but it doesn't require the ActiveWindow. or .TextRange you see here. Unfortunately, VBA/PowerPoint won't work without all the extra baggage.
Strike another blow for consistency across the Office applications.