14.2 Excel and the Internet

Sending Excel Files as E-Mail

With the method SendMail of the Workbook object the workbook is immediately passed to the "installed mail system," as the on-line documentation somewhat vaguely puts it.

If you are working with Outlook or Outlook Express as your e-mail client, everything works just beautifully. Your e-mail lands right in the program's mail "out" folder. According to the setting in Outlook the e-mail is either sent at once, or otherwise the next time the button Send and Receive is pressed (if Outlook is configured for off-line modem operation).

Three parameters are passed to the method SendMail : the recipient's address, the subject (that is, the Subject text), and, optionally , the Boolean value True or False depending on whether a confirmation from the recipient is desired. (Please note, however, that only relatively few e-mail systems support recipient confirmation, regardless of what is set.)

 ThisWorkbook.SendMail "joyce@dublin.eire", "Bloomsday events"

If you wish to send e-mail to several recipients simultaneously , you can pass an Array of character strings in the first parameter. It is unfortunately not possible to set an informational text. Before executing SendMail you do not need first to save the workbook. Excel sends the current version automatically.

Determining the Type of Installed Mail System

With the property MailSystem of the Application object you can determine whether an e-mail system is installed on the local computer, and if so, which e-mail system it is. This property can assume three values:

xlMAPI	The e-mail system is MAPI compatible (Messaging Application Program Interface, a Microsoft Standard).
xlPowerTalk	The e-mail system is based on PowerTalk (an e-mail system for Apple Macintosh computers). In this case, with the Macintosh version of Excel e-mail can be sent via the Mailer object.
xlNoMailSystem	No e-mail system is available (none, at least, compatible with MAPI or PowerTalk).

It is not explicitly documented, but it appears that SendMail is used only when MailSystem = xlMAPI is true. How well it functions when Outlook is not used as the e-mail system could not be tested (on my computer, at least under Windows NT, no other e-mail system is installed).

Sending E-Mail Interactively

If you wish to give the user control over the sending of e-mail and to select the receiver as well as an info text, there are two variants. The first consists in invoking a dialog for sending an e-mail message. Then an Outlook window appears in which the current workbook has already been inserted in the otherwise empty message:

 Application.Dialogs(xlDialogSendMail).Show

The second variant consists in setting the property EnvelopeVisible for the current workbook to True . Then beneath the menus and toolbars four lines appear that enable the input of a recipient address and direct dispatch of the e-mail (see Figure 14-1). You can use the following instruction to turn EnvelopeVisible on and off:

 ThisWorkbook.EnvelopeVisible = Not ThisWorkbook.EnvelopeVisible

Figure 14-1: Left: the xlDialogSendMail dialog; right: Excel with EnvelopeVisible=True

If you execute the instruction in the event procedure of a button, take care that you have set TakeFocusOnClick for the button to False . Otherwise, you will get an error message.

Again, it remains unclear whether both methods work without Outlook.

HTML Import

Pointer

The importation of text (Chapter 5), databases (Chapter 12), and HTML documents are all accomplished in Excel with the same QueryTable object. Here we shall describe only the HTML features. The data source type is derived from the property QueryType of the QueryTable object (xlODBCQuery, xlTextImport, or xlWebQuery). According to QueryType it is then other properties of the QueryTable object that describe the parameters of the import.

With DataGet External DataNew Web Query you can select any HTML file (either on the Internet or in a local network). Pressing Browse Web starts Internet Explorer. The web address set there is automatically taken into the Excel dialog (Figure 14-2). In Excel 2002 the dialog was improved, in that an arbitrary table within the web site can be conveniently selected with the mouse.

Figure 14-2: Importation from an HTML file

In the following importation, Excel attempts, depending on the settings of the options, to import the entire document, a particular table from the document, or simply all tables in the document. (Considered as tables are all parts of the HTML file that are enclosed between <TABLE> and </TABLE> . Because of the many possible formats for tables in HTML documents, <TABLE> constructions are often used for text passages that do not look at all like a table.)

In principle, web importation functions surprisingly well. The problem is that the Internet changes rapidly , and many web sites are completely restructured at least once a year. An application that, say, regularly reads stock quotations from the internet must therefore regularly be adapted to the current layout of the quotation web page.

A few words on setting the QueryTable properties: QueryType contains xlWebQuery (4) . Name must agree with the name of the range of cells into which the data were imported. Destination specifies the first cell of the import range. The Connection character string has the structure " URL ;http://www.name.com/seite.htm". Various details of the importation are set in the WebXxx properties. As usual, the quickest way to find plausible settings for these properties is via the macro recorder.

HTML Export, Web Components

In interactive mode, HTML exportation is accomplished via the command FileSave As Web Page. What can be exported is a range of cells (the current selection), a worksheet, the whole workbook, or a chart.

Independent of the exported data there are two export variants: The resulting HTML code can be static or interactive. Static means that traditional HTML code is produced that can be viewed with almost any HTML browser. On the other hand, if the option Add Interactivity is chosen , then the HTML page contains a reference to a so-called web component (see below).

Setting the many export options is a bit confusing, since it is distributed among three dialogs:

The dialog Save As appears when FileSave As Web Page is executed. It is sufficient for simple saves.
The dialog Publish As Web Page is invoked by the Publish button in the Save As dialog. It contains, among other things, a list of previously exported objects in the current workbook and thereby simplifies repeating an export.
The dialog Web Options is invoked with the menu command ToolsWeb Options in the Save As dialog. There a host of options can be set that determine how the HTML document is to be constructed . (See Figure 14-3.)

Figure 14-3: One of the three exportation dialogs

Web Components

Web components refers to ActiveX controls that make available some of Excel's functions. For users this has the advantage that they can change data (for example, input new numerical values, sort differently, reorganize pivot tables, change the chart type). To this end there are three primary web components: Spreadsheet for normal worksheets (see Figure 14-4), PivotTableList for pivot tables, and Chart for charts . (There are, in addition, some web components for navigation and for creating a database link, but we shall not discuss them further here.)

Figure 14-4: A small range of cells that was exported with interactivity (that is, as a web component)

Web components offer an impressive array of functions (connected with a very attractive design), but they have three serious drawbacks:

The resulting web pages can be displayed only with Internet Explorer versions 4.01 and higher. Other browsers such as Netscape, Mozilla, and Opera cannot be used. Of course, Windows is necessary as well.
Web components can be used only if the user has a license for Office. In an intranet (an office network, say) with an Office network license it is permitted to transfer the web components from the local server and install them only when needed. Otherwise, Office must already be installed for the web components to be used.
The web components delivered with Office 2000 and 2002 contain a massive security hole. A new version of the web components was provided by Microsoft only in August 2002. However, since there are relatively many programs (from Microsoft as well as other software providers) that require the old (faulty) version of the web components, the update solves the security problem only in part. Extensive information is provided by Microsoft Security Bulletin MS02-044, available at http://www.microsoft.com/technet/security/bulletin/MS02-44.asp.

Thus web components may be technologically fascinating, but for the Internet they are perhaps unsuitable on account of their numerous restrictions. (Internet pages should be constructed in such a way that they can be read by as many internet users as possible. This is definitely not the case here.)

The central area of application for web components may thus be in intranets (although there it is actually simpler to make an Excel file as such available on the network).

Pointer

The functions of web components can be controlled via VBScript of Visual Basic code in DHTML pages.Web components are thus programmable, where in principle there is an object model such as Excel, although this is, of course, greatly restricted in its scope. A description of these functions would overreach the bounds of this book and does not really have anything to do with Excel. More information can be found in the programmer's handbook to the Office Developer Edition, in the MSDN library (look under OWC or Office Web Components), and in good books on Frontpage.

HTML Export via VBA Code with PublishObject

If you wish to save an Excel object via VBA code in an HTML document, you will need to make the acquaintance of PublishObject . Access to such objects is by way of Workbook.PublishObjects . To export an Excel object for the first time, you must create a new PublishObject with Add .

 Dim publ As PublishObject Dim fname$ fname = "c:\test.htm" Set publ = ThisWorkbook.PublishObjects.Add( _   SourceType:=xlSourceRange, Sheet:="Sheet1", Source:="$B:$C", _   Filename:=fname, HtmlType:=xlHtmlStatic, DivID:="ID1") publ.Publish

To the Add method must be added a number of parameters, for which there are like-named properties of the PublishObject (usually, however, read only): SourceType specifies the data type in question (for example, a range of cells or a chart). Sheet and Source specify in character strings which data are to be saved. HtmlType determines whether to create a static or interactive HTML document with web components.

The parameter DivID is strange indeed: With it an identification character string can be specified by means of which later the PublishObject can be accessed. DivID takes over the function that with all other objects is played by Name . (If the specified character string is already in use, the associated object will be overwritten with the new data. If you do not specify the parameter, Excel generates a character string automatically.)

Finally, you must execute the method Publish for the PublishObject . Only then is the HTML file generated.

Export Options

For fine control of the HTML export you can set the countless properties of the object DefaultWebOptions (global for Excel) or WebOptions (only for the current workbook). Neither option deals with an enumeration object, despite the -s ending. The most important properties of both objects are collected in Chapter 5, in the subsection "setting options with program code."