Page 154 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

● Wait is an optional variant expression, usually expressed as a True or False Boolean
value that specifies if the procedure should halt further execution until the keys have
been processed. When True, the procedure will pause until all keys sent have been pro­
cessed. When False or omitted, the procedure continues execution without regard to
whether or not the keys have been processed.
The keys are placed into a key buffer first until an application calls for keys from the buffer.
This means the keys need to placed into the buffer before you call a method that will require
the keystrokes.
This example goes through a list of customers and generates a personalized e-mail message
for each customer. The SendKeys method is used to pass an Alt+S keystroke to the default
e-mail program to actually send the e-mail message. The Wait method is used to allow the
e-mail program time to generate the e-mail message before trying to send it.

Sub SendEmail()
Dim strLink As String, rgeEmail As Range
Dim strMsg As String

strMsg = "%0A%0AThis month save $10 off all orders over $100.%0A "
strMsg = strMsg & "Visit The Garden Company for all your gardening needs."
For Each rgeEmail In Range(Cells(2, 1), Cells(7, 1))
strLink = "Mailto:" & rgeEmail.Offset(0, 1).Value & "?subject="
strLink = strLink & "Monthly Special%0A&body="
strLink = strLink & "Dear " & rgeEmail.Value & ",%0A"
strLink = strLink & strMsg
ActiveWorkbook.FollowHyperlink (strLink)
Application.Wait (Now + TimeValue("0:00:02"))
SendKeys "%s", True
Next rgeEmail

End Sub
This chapter introduced the Application object, which contains a number of useful properties
and methods that let you affect how Excel functions at its highest levels. You can assign new
procedures to control key combinations, prevent the screen from flashing as a procedure
makes multiple changes to a workbook, and work with the active window, workbook, chart,
sheet, or cell with the ActiveWindow, ActiveWorkbook, ActiveChart, ActiveSheet, and ActiveCell
properties. Chapter 7 moves to the next level of detail, introducing you to the Workbook and
Worksheet objects.

Chapter 6

128