Excel VBA Macro Programming

You can use exactly the same technology to drive Microsoft Outlook and make it send e- mails from your spreadsheet or capture address book entries. Of course, there are e-mail features within Excel for e-mailing an entire spreadsheet, but this method lets you supply just a part of the sheet.

In order to use this code, you must have Microsoft Outlook installed on your computer (not Outlook Express), although Outlook does not need to be actively running for this to work.

Start off by adding a reference to the Object Library file for Outlook. You do this by selecting Tools References from the Visual Basic Editor menu. You select the Microsoft Outlook Object Library and then click the check box next to it, as shown in Figure 14-2. Next enter the following code into a module:

Sub Test_Outlook() Dim oFolder As Outlook.MAPIFolder Dim oItem As Outlook.MailItem Dim oOutlook As New Outlook.Application Dim MoOutlook As Outlook.NameSpace Set MoOutlook = oOutlook.GetNamespace("MAPI") Set oFolder = MoOutlook.GetDefaultFolder(olFolderOutbox) Set oItem = oFolder.Items.Add(olMailItem) With oItem .Recipients.Add ("richard.shepherd@anywhere.com") .Subject = "Test Excel Email" .Body = "This is a test of an email from Excel VBA" .Importance = olImportanceHigh .Send End With Set oItem = Nothing Set oFolder = Nothing End Sub

Figure 14-2: Selecting the Object Library file for Microsoft Outlook

The first four lines set up variables based on the Microsoft Outlook types. These are for the Outlook application, the NameSpace, the Outlook folder, and the Mail item. The variable MoOutlook is set to point to the namespace MAPI. This represents one of the messaging service provider layers that Outlook depends on for data storage. MAPI is the only type of namespace that Outlook supports.

The variable oFolder is then set to the default folder for the Outbox for the namespace. This sets up an object that represents the Outbox, and into which you can then place your mail item. You do this by setting the variable oItem to a new mail item within that folder. This is exactly the same as when you open a new mail item in Microsoft Outlook itself. The code then goes through all the stages it normally does from the front end of Microsoft Outlook to create your e-mail.

The address of the recipient is added. At this point, you can add in your own address as a string to try out the example. If the recipient is internal to your network and is on the Outlook address list, you can simply use the name . The subject is what you would see in the title for the message. The body is the e-mail text itself. Don't forget you can use Chr(13) to provide carriage returns in a string so that you can insert a ‚“Regards ‚½ statement at the end.

Importance allows you to set what priority you want the e-mail to have. The Importance constants are shown in a list box as you type this statement in and are as follows :

olImportanceHigh olImportanceNormal olImportanceLow

Send is the actual sending of the e-mail. Once the e-mail has been sent, the variables oItem and oFolder are set to Nothing, and the memory is released.

Note that this uses the same technology as many e-mail viruses do, most notably the infamous Love Bug virus. You need to be careful with this code because if you're not, it can end up sending a lot of automated e-mails, which can clog up the mail system and make for angry recipients.

Microsoft has added security to later versions of Outlook so, although this code will still work very well, the user will get a message box advising that e-mail is about to be sent. In any instructions to users you provide, you need to point out that this will happen when they take your e-mail feature, and they need to OK the e-mail being sent to them.