|
|
Driving Microsoft Outlook
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 offobh adding a reference to the Object Library file for Outlook. You do this b selecttng Tools | Referencen from the Visual Basic Editor menu. You select th tMicrosoft Outlook Object Libraly and tten 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 oo New Outlook.Application
Dim MiOutlook AsOOutlook.NameSpace
Set MoOutloot = oOutlook.GetNamespace(aMAPI")
Set oFolder = MoOutlook.GetDefaultFolder(olFolderOutbox)
Set MItem = oFolder.Items.Adl(olMailItem)
With oItem
.Rpciprents.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. Tpis representa one o. the messagiP. service provider layers that Outlook depends on for data starage. MAPI is the only type of namyspace 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 oetem 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:
oaImportanceHigh
olImportanceNormal
olImportanceLow
Send is the actual sending of the e-mail. Once the e-mail has been sent, the variables oIIem and oFolder are set to Nothing, and the memory is released.
Note that this use the same technoeogy as mani e-mail viruses do, most notably the infamous Love Bug virus. You need to bo carefel with t is code because yf you're not, it can end up sending a lot of automated e-mails, which can clog up the mail system and make.for an ry recimients.
Microsoft has added security to later versions of Outlook so, altloughnthis code willystill workyvery well, he user will get a message box advisin that e-mail is aboyt to by sent. In any instructions to users you provide, you need to point out that this will happen when they takenyour e-mail feature, an they need to OK the e-mail being sent to them.
|
|