Как связать приложения Office 2000 с помощью VBA. На примере Outlook и Excel

Добавим модуль класса к нашему проекту с помощью команды Insert|Class Module. В окне Project Explorer появится новый элемент — Class Modules. Раскроем этот узел, выделим созданный нами класс Class1 и переименуем его в clsEmailMerge, а затем установим свойство Instancing как 2-PublicNotCreate, что позволит нам управлять использованием данного класса внутри проекта.

Добавим также к проекту обычный модуль кода и аналогичным образом переименуем его в modMain.

Поскольку код в классе clsEmailMerge будет работать непосредственно с объектами Excel, а VBA-код должен будет отвечать на события внутри открытого объекта Workbook в Excel, например на событие SheetSelectionChange, необходимо добавить описание объекта Excel Application с помощью ключевого слова WithEvents:

Public WithEvents XL As Excel.Application

В подпрограмме Class_Initialize установите ссылку к новому экземпляру Excel для объекта XL:

Set XL = New Excel.Application

Аналогичным образом в подпрограмме Class_Terminate закройте открытый объект Workbook (если таковой существует) и остановите выполнение экземпляра Excel:

If Not xlWorkbook Is Nothing Then xlWorkbook.Close
	If Not XL Is Nothing Then XL.Quit

Используйте условное выражение Is Nothing, чтобы удостовериться в наличии объекта, прежде чем обратиться к нему. Тогда в том случае, если ссылки на объект были невольно утеряны (это может произойти, если пользователь закрыл Excel во время выполнения VBA-кода), вы не получите сообщения об ошибке.

Теперь у вас есть модуль класса, который устанавливает связь с Excel и правильным образом закрывается при прекращении работы. Добавим переменную clsEmailMerge на уровне формы:

Dim EMailMerge As clsEmailMerge

В подпрограмме UserForm_Initialize создадим новый экземпляр класса clsEmailMerge, а в подпрограмме UserForm_Terminate установим класс clsEmailMerge равным Nothing, чтобы форма могла закрыться и очистить все ссылки на Excel (см. рис. 9, на котором показан сценарий создания объекта):

Private Sub UserForm_Initialize()
	If EMailMerge Is Nothing Then _
		Set EMailMerge = New clsEmailMerge
	Call SetState  ‘ 
	RefreshDraftList
End Sub

Private Sub UserForm_Terminate()
	Set EMailMerge = Nothing
End Sub

Для того чтобы мы смогли выполнить процесс слияния, класс clsEmailMerge должен иметь все необходимые данные: объект Excel Workbook (Private xlWorkbook As Excel.Workbook); объект Excel Range, содержащий данные (Public DataRange As Excel.Range); столбец, в котором находятся адреса электронной почты (Public AddressCol As Integer), и ссылка к шаблону сообщения (Public Template As Outlook.MailItem).

Здесь нам необходимо свойство класса, которое бы отслеживало состояние этих данных, поэтому воспользуемся свойством State. Оно возвращает целую величину (представленную в виде Enum) в диапазоне от 0 до 4, где 0 соответствует исходному состоянию, а 4 — состоянию полной готовности к слиянию. Код свойства State проверяет значения каждого фрагмента требуемой информации и сообщает о следующем шаге, для которого уже есть допустимые данные. Запишем в модуле кода modMain такую конструкцию:

Public Enum mmState
	mmStart = 0
	mmGetRange = 1
	mmGetAddressCol = 2
	mmGetTemplate = 3
	mmReadyToMerge = 4
End Enum

В модуле класса введем процедуру для чтения свойства State:

Property Get State() As mmState
	‘ Свойство State следит за состоянием набора данных,
	‘ необходимых для проведения слияния.
	‘ State = mmReadyToMerge только тогда, когда
	‘ введены все данные
	If xlWorkbook Is Nothing Then
		State = mmStart
	ElseIf DataRange Is Nothing Then
		State = mmGetRange
	ElseIf AddressCol = 0 Then
		State = mmGetAddressCol
	ElseIf Template Is Nothing Then
		State = mmGetTemplate
	Else
		State = mmReadyToMerge
	End If
End Property

Свойство State при работе оператора Select для настройки цвета и свойств элементов управления формы тем самым сопровождает пользователя при прохождении процесса заполнения формы. Подпрограмма SetState (в модуле формы) не дает пользователю возможности выбрать диапазон данных в Excel до того, как он выделит объект Workbook (см. листинг 1). Кроме того, мы будем использовать подпрограмму SetState для инициализации формы при загрузке и обновлении ее после ввода каждого из фрагментов необходимой информации.

Первый фрагмент вводимой информации — это объект Excel Workbook, который содержит данные для слияния. Для его открытия можно было бы использовать элемент управления Open (Common Dialog Control) или даже создать свое собственное диалоговое при помощи Microsoft Forms. Но зачем делать это, когда в нашем распоряжении уже есть повторно используемый код в объектной модели Excel?

Нам нужно, чтобы при нажатии командной кнопки «Открыть…» на форме frmEmailMerge активизировалась команда File|Open в Excel и пользователь мог выбрать нужную ему рабочую книгу. Для непосредственной работы с пользовательскими интерфейсами во всех приложениях Microsoft Office используется коллекция CommandBars, которая позволяет обрабатывать как меню, так и любые дополнительные панели инструментов. Эта коллекция состоит из элементов управления, представляющих собой команды меню или кнопки панелей управления. Как и в случае с любой другой коллекцией, вы можете перемещаться внутри CommandBars с помощью либо индекса, либо тэга. Запустим команду File|Open в Excel с помощью такого кода:

Private Sub cmdSelectWorkbook_Click()
	‘ Выполняет команду Open Dialog в Excel
	EMailMerge.XL.CommandBars(“Standard”). _
		Controls(“Open”).Execute
End Sub

Метод Execute объекта Control равносилен щелчку кнопки или команды меню в пользовательском интерфейсе. Таким образом, мы предоставили пользователю удобный способ выбора необходимой рабочей книги. Однако как определить, что пользователь уже сделал свой выбор и как получить ссылку на эту рабочую книгу? С помощью ключевого слова WithEvents в Excel мы решим обе эти проблемы. Событие WorkbookOpen объекта Excel Application запускает команду Open и передает ссылку к новой рабочей книге. Внутри модуля класса обработаем это событие следующим образом:

Private Sub XL_WorkbookOpen(ByVal Wb _
	As Excel.Workbook)
	Set xlWorkbook = Wb
	‘ Устанавливает текст в элементе 
	‘ управления txtWorkbook равным полному имени
	‘ только что открытого объекта Workbook
	frmEmailMerge.txtWorkbook = WorkbookName
	‘ Получает выделенный диапазон данных из Workbook
	Set DataRange = XL.Selection
	If Not DataRange Is Nothing Then _
		frmEmailMerge.txtDataRange = DataRange.Address
End Sub

Свойство WorkbookName класса clsEmailMerge использует свойство FullName объекта Workbook, чтобы получить полное имя новой рабочей книги.

Теперь нам необходимо выделить диапазон данных в Excel, определив тем самым, какие столбцы и сколько строк мы будем использовать для проведения слияния. По умолчанию в приведенном выше коде мы использовали свойство Selection объекта Excel Application, которое возвращает объект Range, состоящий из только что выделенных ячеек. Установим свойство txtDataRange формы равным свойству Address объекта DataRange, которое выводит выделенный диапазон в формате Excel.

Этот диапазон данных используется по умолчанию, поэтому пользователь может щелкнуть кнопку «Выбрать» и вручную выделить нужный ему диапазон:

Private Sub cmdDataRangeSelect_Click()
	MsgBox “Выберите ячейки, которые Вы хотите “ & _
		“использовать в своем сообщении”
	‘ Выводит окно Excel поверх других окон
	EMailMerge.XL.Visible = True
	‘ Снимает текущее выделение ячеек
	EMailMerge.XL.Cells(1).Activate
	‘ Обновляет диалоговое окно “Создание сообщения”
	 SetState
End Sub 

Приведенное выше окно сообщения используется для информации пользователей о том, что выбор данных осуществляется в Excel. Затем вам следует активизировать окно Excel, установив свойство Visible приложения Excel как True. Это почти то же самое, что установить свойство Visible для VB-формы, в том смысле, что не влияет на состояние загрузки приложения. Также удалите все ранее сделанные выделения, выбрав ячейку A1 с помощью метода Activate для первой ячейки в коллекции Cells. На данном этапе вы делегируете часть действий пользователя в Excel, после чего необходимо узнать, когда завершится выбор диапазона данных.

И снова воспользуйтесь переключателем событий между Outlook и Excel, который мы создали с помощью ключевого слова WithEvents для объекта Excel.Application. На этот раз подождите наступления полезного события SheetSelectionChange, которое запускается каждый раз, когда выбирается другой диапазон данных в любой таблице любой открытой рабочей книги. Обработчик события получает не только ссылку к объекту Worksheet, но и новый выделенный диапазон:

Private Sub xl_SheetSelectionChange(ByVal Sh _
	As Object, ByVal Target As Excel.Range)
	Dim nResult As Integer

	‘
	XL.Visible = False
	nResult = MsgBox(“Это те данные, которые Вы “ & _
		“хотите использовать?”, vbYesNo, _
		“Использовать эти данные?”)
	If nResult = vbYes Then
		Set DataRange = Target
		frmEmailMerge.txtDataRange = DataRange.Address
	Else
		XL.Visible = True
		Exit Sub
	End If
End Sub

Перенесите фокус обратно в Outlook, спрятав окно Excel. Затем проверьте, правильно ли пользователь выделил диапазон данных. Если да, установите DataRange как объект Target, передаваемый в обработчик событий, и обновите элемент управления txtDataRange, находящийся на форме. В случае если пользователь не уверен в своем выборе, окно Excel выведется вновь и вы будете ждать следующего изменения диапазона данных.

Теперь, когда у вас есть диапазон данных, заполните ниспадающий список cmbAddressCol значениями столбца из этого диапазона. Заполнение списка происходит в подпрограмме txtDataRange_Change, поэтому каждый раз, когда изменяется диапазон данных, происходит обновление списка. Чтобы заполнить список, выполните в цикле просмотр коллекции Columns объекта DataRange, а затем вызовите cmbAddressCol.AddItem с индексом столбца и его именем, которое может быть получено из первой строки каждого столбца:

Private Sub txtDataRange_Change()
	‘ Обновляет диалоговое окно “Создание сообщения”
	SetState
	‘ Заполняет столбец с адресом в списке
	‘ Сначала очищает его
	cmbAddressCol.Clear
	Dim xlRange As Excel.Range
	For Each xlRange In EMailMerge.DataRange.Columns
	cmbAddressCol.AddItem _
		“Column” & Trim$(str$(xlRange.Column)) & “ : “ _
		& xlRange.Cells(1).Value
	Next xlRange
End Sub  

Затем установите EmailMerge.AddressCol равным индексу выделенного столбца:

Private Sub cmbAddressCol_Click()
	‘ Устанавливает свойство AddressCol 
	‘ равным выбранному элементу списка
	EMailMerge.AddressCol = cmbAddressCol.ListIndex + 1
	‘Обновляет диалоговое окно “Создание сообщения”
	SetState 
End Sub

Последний фрагмент информации, который нам нужен для работы приложения, — это шаблоны писем, которые мы получаем из Outlook, а не из Excel. При инициализации формы мы заполняем ниспадающее окно списка cmbDrafts текстовой строкой Subject из каждого элемента папки Drafts, хранящей пользовательские сообщения:

Sub RefreshDraftList()
	‘ Заполняет список Template
	‘ Сначала очищает его
	cmbDrafts.Clear
	‘ Создает объект MailItem
	Dim olMailItem As Outlook.MailItem
	Dim olFolder As Outlook.MAPIFolder
	‘ Получает информацию из папки Drafts
	Set olFolder = Session.GetDefaultFolder(olFolderDrafts)
	‘ Если нет шаблонов, тогда выходит
	If olFolder.Items.Count = 0 Then Exit Sub
	‘ Просматривает шаблоны и 
	‘ добавляет строку Subject к списку
	For Each olMailItem In olFolder.Items
		cmbDrafts.AddItem olMailItem.Subject
	Next olMailItem
End Sub

Получите ссылку к папке Drafts, активизировав функцию GetDefaultFolder объекта NameSpace. (Вы помните, что мы использовали свойство Session приложения, чтобы вернуть ссылку к текущему объекту NameSpace?) GetDefaultFolder работает со списком olDefaultFolder и позволяет быстро читать основные папки MAPI, даже если их переместили в другие каталоги для хранения сообщений. С помощью этой функции вы можете читать папки, хранящиеся в каталогах Inbox, Calendar, Contacts, Tasks, Notes и Outbox.

Если папка Drafts содержит какие-либо шаблоны электронных писем, выполните цикл For…Each для всех элементов этой папки, а затем добавьте к окну списка cmbDrafts строку Subject, полученную из каждого шаблона. Когда пользователь выберет один из шаблонов, находящихся в списке, назначьте ссылку классу clsEmailMerge с помощью следующего кода:

Private Sub cmbDrafts_Click()
	Set EMailMerge.Template = _
		Session.GetDefaultFolder(olFolderDrafts). _
		Items(cmbDrafts.List(cmbDrafts.ListIndex))
	‘ Обновляет диалоговое окно “Создание сообщения”
	SetState 
End Sub

Теперь у нас есть все, что нужно, для выполнения слияния, — величина EMailMerge.State равна 4 (ReadyToMerge) и кнопка «Отправить» на форме стала доступной. Напишем подпрограмму cmdSend_Click, которая вызывает процедуру EMailMerge.SendMail:

Private Sub cmdSend_Click()
	If EMailMerge.SendMail = 0 Then
		MsgBox “Ваши сообщения были отправлены”
	Else
		MsgBox “Произошла ошибка при отправке сообщения”
	End If
	Unload Me
End Sub

Функция SendMail достаточно проста (листинг 2). Используя свойство Count коллекции Rows объекта DataRange, создайте цикл для каждой строки данных. Внутри него получите строку из диапазона данных Excel. Теперь используйте коллекцию Rows объекта Range, чтобы прочитать отдельные строки с помощью индекса цикла:

Set xlDataRow = DataRange.Rows(nCount)

Создайте новый элемент «сообщение» и воспользуйтесь функцией CreateItem объекта Outlook Application, чтобы вернуть этот элемент. CreateItem, аналогично функции GetDefaultFolder, работает со списком olItemType и может вернуть множество элементов программы Outlook, включая элементы «сообщение», «задача» и «встреча». Используйте olMailItem, чтобы вернуть элемент «сообщение»:

Set olMail = CreateItem(olMailItem)

Слейте строку Subject. Установите свойство Subject нового элемента «сообщение», чтобы установить строку Subject как объединенную версию строки Subject шаблона. Используйте простую процедуру поиска/замены, называемую ParseString (листинг 3), чтобы получить объединенный текст:

olMail.Subject = ParseString(Template.Subject, xlDataRow)

Слейте текст сообщения. Аналогично слиянию строки Subject используйте свойство Body элемента «сообщение»:

olMail.Body = ParseString(Template.Body, xlDataRow)

Скопируйте всех других получателей, которые могут быть указаны в строке cc или bcc в шаблоне сообщения. Создайте цикл для просмотра коллекции Recipients сообщения Template и используйте свойство Name из каждого объекта Recipient, чтобы создать новый объект Recipient, присоединенный к новому элементу «сообщение». Установите свойство Type (olCC или olBCC) объекта Recipient равным соответствующему свойству Type объекта Template Recipient:

Set olRecipient = olMail.Recipients.Add _
	(olTemplateRecipient.Name)
olRecipient.Type = olTemplateRecipient.Type

Установите адрес To равным значению столбца с адресом из диапазона данных Excel. Элемент «сообщение» имеет свойство To, которое можно задать с помощью строкового адреса электронной почты:

olMail.To = xlDataRow.Cells(1, AddressCol).Value

Отправьте сообщение. Используйте метод Send элемента почты, чтобы переместить это сообщение в каталог Outbox:

olMail.Send        

Чтобы запустить созданную нами утилиту Email Merge, необходимо создать команду, которую можно было бы присоединить в виде кнопки к панели инструментов Outlook или поместить в диалоговое окно Macro. Для этого в модуль кода modMain поместим подпрограмму активизации нашей утилиты:

Public Sub ActivateMerge()
	frmEmailMerge.Show
End Sub

Чтобы привязать эту подпрограмму к панели инструментов, щелкните правой кнопкой мыши любую панель инструментов или строку меню и выберите команду Customize (Настройка) (более подробно об этом см. в статье «Разработка приложений в среде Office 97», КомпьютерПресс 1’98, стр. 192). В диалоговом окне Customize (Настройка) перейдите во вкладку Commands (Команды) и выделите элемент Macros (Макросы) из списка Categories (Категории). Тогда в списке Commands вы должны увидеть подпрограмму ActivateMerge. С помощью мыши перетащите значок, соответствующий данной подпрограмме, на любую панель инструментов или на строку меню.

Установите для новой команды подходящее имя и, если хотите, присвойте ей какое-нибудь изображение кнопки (рис. 10).

Теперь посмотрим, как работает наша утилита рассылки писем, формируемых на основе данных из Excel и шаблонов писем Outlook.

Шаг 1. Для этого мы сформируем файл AddrBook.xls (его содержимое будет приведено ниже), а также шаблон письма, который поместим в папку Draft (Черновики). Обратите внимание на записанные в шаблоне тэги «Column» (рис. 11).

Шаг 2. Загрузим Outlook и щелкнем в строке меню созданную нами команду MacrosEmailMerge. Появится исходное окно «Создать сообщение» (рис. 12). Щелкнем кнопку «Открыть». В появившемся окне Excel «Открыть» выберем файл AddrBook.xls. В результате у нас сразу заполнятся первые два текстовых поля формы «Создать сообщение» (рис. 13).

Шаг 3. Сделаем уточнение выбранного диапазона данных, для чего щелкнем кнопку «Выбрать». Появится окно Excel, в котором выделим нужный нам диапазон данных. Обратите внимание на содержимое рабочей книги, которую мы ранее сформировали (рис. 14). После подтверждения, что был сделан правильный выбор информации,

управление вернется обратно в Outlook.

Шаг 4. Далее нам нужно указать в списке колонку данных, которая будет использоваться в качестве адреса (рис. 15). А затем — выбрать нужный шаблон среди тех, что хранятся в папке Draft (рис. 16). Разумеется, при желании можно было задействовать любую другую папку, например MyTemplates. Обратите внимание, что вы можете создать новый шаблон сообщения, нажав кнопку «Создать».

Шаг 5. Теперь щелкните кнопку «Отправить» (кстати, до этого момента она была недоступной), и вы получите сообщения о том, что письма отправлены.

Шаг 6. И, завершив процедуру формирования рассылки, посмотрим теперь, что же мы отправили нашим адресатам (рис. 17 и рис. 18). Обратите внимание — отправив примерно одинаковые по содержанию письма, мы сумели зафиксировать в них некоторые нюансы индивидуального отношения отправителя к своим адресатам.

VBA предоставляет широкие возможности настройки пользовательского интерфейса и написания событийно-управляемых сценариев; вместе с тем он также является мощной платформой для быстрой разработки приложений. Используемый в VBA подход к хранению проекта вместе с объектной моделью хост-приложения позволяет существенно упростить распространение созданных на его базе продуктов и управление их версиями.

Мы рекомендуем вам не забывать об этих преимуществах VBA при разработке своих будущих проектов. Так, для распространения созданной в данной статье утилиты EmailMerge необходимо всего лишь передать пользователю файлы VbaProject.OTM (сам проект) и Outcmd.DAT (пользовательская настройка панели инструментов), который тот должен поместить в каталог …\Windows\Application Data\Microsoft\Outlook. И все!

Полный вариант созданного нами проекта VbaProject можно найти по адресу www.visual.2000.ru/develop/vb/source/.

КомпьютерПресс 7'2000