Using SendKeys

Not all applications support Automation. In some cases, you can still control some aspects of the application even if it doesn't support Automation. You can use Excel's SendKeys method to send keystrokes to an application, simulating actions that a live human might perform.

Although using the SendKeys method might seem like a good solution, you'll find that it can be very tricky. A potential problem is that it relies on a specific user interface. If a later version of the program that you're sending keystrokes has a different user interface, your application might no longer work. Consequently, you should use SendKeys only as a last resort.

Following is a very simple example. This procedure runs the Windows Calculator program and displays its Scientific mode: That is, it executes the View Scientific command.

 Sub TestKeys()      Shell "calc.Exe", vbNormalFocus      Application.SendKeys "%vs" End Sub 

In this example, the code sends out Alt+V (the percent sign represents the Alt key) followed by S.

SendKeys is documented in the Help system, which describes how to send nonstandard keystrokes, such as Alt and Ctrl key combinations.

The code that follows is a more elaborate procedure that uses SendKeys . This routine is similar to the Outlook example that I present earlier in this chapter (see "Sending Personalized E-Mail via Outlook"). The difference is that it creates e-mail messages for Outlook Express - Microsoft's e-mail client that doesn't support automation.

 Sub SendEmailViaOutlookExpress()   Dim cell As Range   Dim Subj As String   Dim EmailAddr As String   Dim Recipient As String   Dim Bonus As String   Dim Msg As String   Dim HLink As String   For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)     If cell.Value Like "*@*" Then       'Get the data       Subj = "Your Annual Bonus"       Recipient = cell.Offset(0, -1).Value       EmailAddr = cell.Value       Bonus = Format(cell.Offset(0, 1).Value, " 
 Sub SendEmailViaOutlookExpress() Dim cell As Range Dim Subj As String Dim EmailAddr As String Dim Recipient As String Dim Bonus As String Dim Msg As String Dim HLink As String For Each cell In Columns("B"). Cells .SpecialCells(xlCellTypeConstants) If cell.Value Like "*@*" Then 'Get the data Subj = "Your Annual Bonus" Recipient = cell.Offset(0, -1).Value EmailAddr = cell.Value Bonus = Format( cell .Offset(0, 1).Value, "$0,000.") 'Compose message Msg = "Dear " & Recipient & "%0A" Msg = Msg & "%0A" & "I am pleased to inform " Msg = Msg & "you that your annual bonus is " Msg = Msg & Bonus & "%0A" Msg = Msg & "%0A" & "William Rose" Msg = Msg & "%0A" & "President" 'Build hyperlink HLink = "mailto:" & EmailAddr & "?" HLink = HLink & "subject=" & Subj & "&" HLink = HLink & "body=" & Msg 'Send it ActiveWorkbook.FollowHyperlink HLink Application.Wait (Now + TimeValue("0:00:02")) SendKeys "%s", True End If Next End Sub 
 ,000.")       'Compose message       Msg = "Dear " & Recipient & "%0A"       Msg = Msg & "%0A" & "I am pleased to inform "       Msg = Msg & "you that your annual bonus is "       Msg = Msg & Bonus & "%0A"       Msg = Msg & "%0A" & "William Rose"       Msg = Msg & "%0A" & "President"       'Build hyperlink       HLink = "mailto:" & EmailAddr & "?"       HLink = HLink & "subject=" & Subj & "&"       HLink = HLink & "body=" & Msg       'Send it       ActiveWorkbook.FollowHyperlink HLink       Application.Wait (Now + TimeValue("0:00:02"))       SendKeys "%s", True     End If   Next End Sub 

CD  

This example, named  personalized email - OE sendkeys.xlsm , is available on the companion CD-ROM. The procedure works correctly only if Outlook Express is your default e-mail client. If you use a different e-mail client, you may be able to modify the procedure so that it sends keystrokes appropriate for your software.

Figure 20-13 shows a worksheet that contains data used in the e-mail messages: name , e-mail address, and bonus amount.

Figure 20-13: This information is used in the Outlook Express e-mail messages.

The SendEmail procedure assumes that Outlook Express is the default e-mail client. It loops through the rows in the worksheet and creates a message (stored in the Msg variable). It uses the FollowHyperlink method to launch Outlook Express's New Message window. For the first record, the hyperlink is as follows:

 mailto:[e-mail]?subject=Your Annual Bonus &body=Dear John Jones%0A%0AI am pleased to inform you that your annual bonus is ,000.%0A%0AWilliam Rose%0APresident 

Note  

The %0A character sequence represents a line break.

The procedure pauses for two seconds and then uses SendKeys to issue the Alt+S command, which puts the message in the Outlook Express Outbox. This pause is required to ensure that the e-mail message is onscreen when the keystrokes are sent. You might need to adjust the delay, depending on your system. You might find that a one-second delay is sufficient, but others might need to increase it to three or more seconds.

Although this technique works fine, note the character limit on the length of the hyperlink (around 730 characters ). Therefore, this is suitable only for short messages.

Figure 20-14 shows one of the messages in Outlook Express.

Figure 20-14: This Outlook Express message uses data stored in a worksheet.