Can I open an Outlook dialog box from Excel?
 

I would like to insert an email address from the outlook insert names dialog
box into the active cell. Is it possible?

I can't find any reference to the Outlook built-in dialog boxes.

Thanks in advance

Can I open an Outlook dialog box from Excel?
 

The lingo used in Outlook is somewhat different than what you find in Excel
and Word which is why you don't find info on built in dialog boxes for
Outlook. You probably want a form associated with an "Inspector".

You could do something like

________________________
Set myOL = CreateObject("Outlook.Application")
Set myItem = myOL.Application.CreateItem(0)
Set myInspect = myItem.GetInspector
myInspect.Activate
-----------------------------------------------
but that just gets you the form for creating a new message. You could use
SendKeys to send a period and get the 'Select Names' window but I don't have
any idea how you would capture the name when the user selected one.

I'd suggest something like I've got below. For the example, I created a
UserForm1 that contains the list box, ListBox1. I also created a subroutine
in a module that simply has the one line:
UserForm1.Show

When the user form is activated, it grabs all the contacts in your contacts
folder and populates ListBox1 with those names. When ListBox1 changes
because the user selects one of the listed names, the associated email
address is entered in Cell C2. The one annoying (but necessary) feature is
that the user will have to click an OK to a security warning that some
program is harvesting email address info and they need to give approval for
a certain number of minutes or kill the routine.

You might also want to capture last name to the array and alphabetize the
list before populating the listbox but I wanted to keep it simple for now.

__________________________

Private arrContacts() As String

Private Sub ListBox1_Change()
Cells(2, 3).Value = arrContacts(1, ListBox1.ListIndex)
End Sub

Private Sub UserForm_Activate()

Const olFolderContacts = 10

Dim intCount As Integer

ReDim arrContacts(1, 0)
arrContacts(0, 0) = ""
arrContacts(1, 0) = ""
intCount = 1

Set objOL = CreateObject("Outlook.Application")
Set objNS = objOL.GetNamespace("MAPI")
Set objFldContacts = objNS.GetDefaultFolder(olFolderContacts)

For Each myItem In objFldContacts.Items
If TypeName(myItem) = "ContactItem" Then
ReDim Preserve arrContacts(1, intCount)
arrContacts(0, intCount) = myItem.FullName
arrContacts(1, intCount) = myItem.Email1Address
intCount = intCount + 1
End If
Next myItem

For L = 0 To UBound(arrContacts, 2)
ListBox1.AddItem arrContacts(0, L), L
Next L

Set objFldContacts = Nothing
Set objNS = Nothing
Set objOL = Nothing
End Sub


___________________________

Steve Yandl


"LuisE" wrote in message
...
I would like to insert an email address from the outlook insert names
dialog
box into the active cell. Is it possible?

I can't find any reference to the Outlook built-in dialog boxes.

Thanks in advance

Can I open an Outlook dialog box from Excel?
 

Thanks a lot Steve for the prompt response and for your time.
I found your approach very helpful.

I think the listbox is a multiselect one. I'm looking to transfer all the
selected entries into the same cell, separated by ";"

Would change FolderContacts for AddressLists allow me to capture all
addresses?

Thanks a lot again

"Steve Yandl" wrote:

The lingo used in Outlook is somewhat different than what you find in Excel
and Word which is why you don't find info on built in dialog boxes for
Outlook. You probably want a form associated with an "Inspector".

You could do something like

________________________
Set myOL = CreateObject("Outlook.Application")
Set myItem = myOL.Application.CreateItem(0)
Set myInspect = myItem.GetInspector
myInspect.Activate
-----------------------------------------------
but that just gets you the form for creating a new message. You could use
SendKeys to send a period and get the 'Select Names' window but I don't have
any idea how you would capture the name when the user selected one.

I'd suggest something like I've got below. For the example, I created a
UserForm1 that contains the list box, ListBox1. I also created a subroutine
in a module that simply has the one line:
UserForm1.Show

When the user form is activated, it grabs all the contacts in your contacts
folder and populates ListBox1 with those names. When ListBox1 changes
because the user selects one of the listed names, the associated email
address is entered in Cell C2. The one annoying (but necessary) feature is
that the user will have to click an OK to a security warning that some
program is harvesting email address info and they need to give approval for
a certain number of minutes or kill the routine.

You might also want to capture last name to the array and alphabetize the
list before populating the listbox but I wanted to keep it simple for now.

__________________________

Private arrContacts() As String

Private Sub ListBox1_Change()
Cells(2, 3).Value = arrContacts(1, ListBox1.ListIndex)
End Sub

Private Sub UserForm_Activate()

Const olFolderContacts = 10

Dim intCount As Integer

ReDim arrContacts(1, 0)
arrContacts(0, 0) = ""
arrContacts(1, 0) = ""
intCount = 1

Set objOL = CreateObject("Outlook.Application")
Set objNS = objOL.GetNamespace("MAPI")
Set objFldContacts = objNS.GetDefaultFolder(olFolderContacts)

For Each myItem In objFldContacts.Items
If TypeName(myItem) = "ContactItem" Then
ReDim Preserve arrContacts(1, intCount)
arrContacts(0, intCount) = myItem.FullName
arrContacts(1, intCount) = myItem.Email1Address
intCount = intCount + 1
End If
Next myItem

For L = 0 To UBound(arrContacts, 2)
ListBox1.AddItem arrContacts(0, L), L
Next L

Set objFldContacts = Nothing
Set objNS = Nothing
Set objOL = Nothing
End Sub


___________________________

Steve Yandl


"LuisE" wrote in message
...
I would like to insert an email address from the outlook insert names
dialog
box into the active cell. Is it possible?

I can't find any reference to the Outlook built-in dialog boxes.

Thanks in advance