Hi,

I found this code from one of your responses, but as a
rank beginner - first attempt at this kind of thing, that
I don't know enought as to where to go from here.

I have a UserForm with 1 ComboBox (ComboBox1) and 1
Command Button (CommandButton1). I need to somehow, and
maybe this is not the best way to do it, get the user when
they click the combo box to have it display their Contacts
folder (will eventually be a Public Folder) to see the
names of the companies. They would pick a name and it
would insert the address, phone contact name et al fields
into the worksheet at specific cells. I know this code it
not the right code but it appears to set a link up in some
way.

So any help where to begin would be truly appreciated!!!!!

Private Sub UserForm_Initialize()

'Sets ComboBox RowSource to Outlook Contacts Company Field
'ComboBox ControlSource is D3
'ComboBox1.RowSource = "Names"

End Sub

Private Sub CommandButton1_Click()
Sub GetContact()

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Personal Folders").Folders
("Contacts")

For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi

Set olCi = Nothing
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

'Next coding needs to be insert into cells D3 =
Company, D4 = Street Address, D5 = Locality, D6 =
State_or_Province D7 = Postal_Code, D8 = Display_Name, D9
= Office_Telephone_Number, D10 = Business_Fax_Number & B3
Department_Name

'Hides UserForm1
UserForm1.Hide
End Sub

Thanks again SO MUCH!!! If I can just get this I can
finish my project!

Shauna

Shauna,

Don't know for sure, but check out this macro to open a file for ideas.
Check the VB help for more details.

The GetOpenFilename part just opens the dialog to open a file.
You might add a ChDir to select the folder.

steve

Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

--
sb
"Shauna Koppang" wrote in message
...
Hi,

I found this code from one of your responses, but as a
rank beginner - first attempt at this kind of thing, that
I don't know enought as to where to go from here.

I have a UserForm with 1 ComboBox (ComboBox1) and 1
Command Button (CommandButton1). I need to somehow, and
maybe this is not the best way to do it, get the user when
they click the combo box to have it display their Contacts
folder (will eventually be a Public Folder) to see the
names of the companies. They would pick a name and it
would insert the address, phone contact name et al fields
into the worksheet at specific cells. I know this code it
not the right code but it appears to set a link up in some
way.

So any help where to begin would be truly appreciated!!!!!

Private Sub UserForm_Initialize()

'Sets ComboBox RowSource to Outlook Contacts Company Field
'ComboBox ControlSource is D3
'ComboBox1.RowSource = "Names"

End Sub

Private Sub CommandButton1_Click()
Sub GetContact()

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Personal Folders").Folders
("Contacts")

For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi

Set olCi = Nothing
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

'Next coding needs to be insert into cells D3 =
Company, D4 = Street Address, D5 = Locality, D6 =
State_or_Province D7 = Postal_Code, D8 = Display_Name, D9
= Office_Telephone_Number, D10 = Business_Fax_Number & B3
Department_Name

'Hides UserForm1
UserForm1.Hide
End Sub

Thanks again SO MUCH!!! If I can just get this I can
finish my project!

Shauna

HI Steve,

Thanks for that but I think the coding I got below is
probably a better start as it sets up the access to
Outlook.

Being so new to this what you sent might help for another
project, but not this one as I have to access Outlook and
access a specific folder and fields within it and have
them populate and area of a speadsheet.

But thanks for your assistance. I truly appreciate all
those people who have been helping me through this project
and tremendous learning process.

Shauna
-----Original Message-----
Shauna,

Don't know for sure, but check out this macro to open a
file for ideas.
Check the VB help for more details.

The GetOpenFilename part just opens the dialog to open a
file.
You might add a ChDir to select the folder.

steve

Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing
Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

--
sb
"Shauna Koppang" wrote
in message
...
Hi,

I found this code from one of your responses, but as a
rank beginner - first attempt at this kind of thing,
that
I don't know enought as to where to go from here.

I have a UserForm with 1 ComboBox (ComboBox1) and 1
Command Button (CommandButton1). I need to somehow, and
maybe this is not the best way to do it, get the user
when
they click the combo box to have it display their
Contacts
folder (will eventually be a Public Folder) to see the
names of the companies. They would pick a name and it
would insert the address, phone contact name et al
fields
into the worksheet at specific cells. I know this code
it
not the right code but it appears to set a link up in
some
way.

So any help where to begin would be truly
appreciated!!!!!

Private Sub UserForm_Initialize()

'Sets ComboBox RowSource to Outlook Contacts Company
Field
'ComboBox ControlSource is D3
'ComboBox1.RowSource = "Names"

End Sub

Private Sub CommandButton1_Click()
Sub GetContact()

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Personal
Folders").Folders
("Contacts")

For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi

Set olCi = Nothing
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

'Next coding needs to be insert into cells D3 =
Company, D4 = Street Address, D5 = Locality, D6 =
State_or_Province D7 = Postal_Code, D8 = Display_Name,
D9
= Office_Telephone_Number, D10 = Business_Fax_Number &
B3
Department_Name

'Hides UserForm1
UserForm1.Hide
End Sub

Thanks again SO MUCH!!! If I can just get this I can
finish my project!

Shauna


.

Shauna,

Yep! I don't do that much interfacing with Outlook, so that is another weak
spot in my resume...

But glad the code I sent may be of help later.

--
sb
"Shauna Koppang" wrote in message
...
HI Steve,

Thanks for that but I think the coding I got below is
probably a better start as it sets up the access to
Outlook.

Being so new to this what you sent might help for another
project, but not this one as I have to access Outlook and
access a specific folder and fields within it and have
them populate and area of a speadsheet.

But thanks for your assistance. I truly appreciate all
those people who have been helping me through this project
and tremendous learning process.

Shauna
-----Original Message-----
Shauna,

Don't know for sure, but check out this macro to open a
file for ideas.
Check the VB help for more details.

The GetOpenFilename part just opens the dialog to open a
file.
You might add a ChDir to select the folder.

steve

Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing
Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

--
sb
"Shauna Koppang" wrote
in message
...
Hi,

I found this code from one of your responses, but as a
rank beginner - first attempt at this kind of thing,
that
I don't know enought as to where to go from here.

I have a UserForm with 1 ComboBox (ComboBox1) and 1
Command Button (CommandButton1). I need to somehow, and
maybe this is not the best way to do it, get the user
when
they click the combo box to have it display their
Contacts
folder (will eventually be a Public Folder) to see the
names of the companies. They would pick a name and it
would insert the address, phone contact name et al
fields
into the worksheet at specific cells. I know this code
it
not the right code but it appears to set a link up in
some
way.

So any help where to begin would be truly
appreciated!!!!!

Private Sub UserForm_Initialize()

'Sets ComboBox RowSource to Outlook Contacts Company
Field
'ComboBox ControlSource is D3
'ComboBox1.RowSource = "Names"

End Sub

Private Sub CommandButton1_Click()
Sub GetContact()

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Personal
Folders").Folders
("Contacts")

For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi

Set olCi = Nothing
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

'Next coding needs to be insert into cells D3 =
Company, D4 = Street Address, D5 = Locality, D6 =
State_or_Province D7 = Postal_Code, D8 = Display_Name,
D9
= Office_Telephone_Number, D10 = Business_Fax_Number &
B3
Department_Name

'Hides UserForm1
UserForm1.Hide
End Sub

Thanks again SO MUCH!!! If I can just get this I can
finish my project!

Shauna


.

Shauna

Change this part


For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi


to

Me.ComboBox1.Clear

For Each olCi In Fldr.Items
Me.ComboBox1.AddItem olCi.FullName
Next olCi

That will populate the combobox with all of the contacts in the folder.
Then move this macro to the Initialize event so that the combobox is
re-populated each time you run the form.

I assume the button should put the relevant data on the worksheet, so make
your button's click event look something like this

For Each olCi In olFldr.Items
If olCi.FullName = Me.ComboBox1.Value Then
Sheet1.Range("d3").Value = olCi.CompanyName
Sheet1.Range("d4").Value = olCi.BusinessAddress
'Other data you want to write goes here in the same format
End If
Next olCi

This loops through the contacts and find the one that matches the one
selected in the combobox. Then it writes the pertinent information to
whatever cells you choose.

Here's another thing you want to do: Make module level variables for olApp,
olNs and olFldr - that is, Dim those variables above all your procedures,
but below the Option Explicit statement. The Code Module behind your
userform should look roughly like this

Option Explicit

Dim olApp as Outlook.Application
Dim olNs as Outlook.NamesSpace
Dim olFldr as Outlook.MAPIFolder

Private Sub CommandButton1_Click()
'code to write to cells

Unload Me 'Don't just hide, actually unload it
End Sub

Private Sub Userform_Initialize()
Set olApp = New Outlook.Application
Set olNs = olApp.GetNameSpace("MAPI")
Set olFldr = olNs.GetDefaultFolder("Contacts")

'populate combobox here
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

This way, you'll only have to connect to Outlook when the userform is loaded
and disconnect when it is unloaded as opposed to connecting to it everytime
you do something with outlook. The variables (module level variables) at
the top can be used in any sub in that module, so you don't have to Dim them
in every module.

That's a lot of info, I know. Do what you can with it and post back when
you get stuck. For a faster reply, make sure you reply to this post - it
will show up as red in my newsreader and I'll see it for sure that way.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Hi Dick,

Thanks again so much!!!

OK so here is how I change my code. Does not seem to work
yet. Probably because I am still so novice I put things
in the wrong places.

Private Sub UserForm_Initialize()

'Sets ComboBox Outlook Contacts FullName

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder("Contacts")


Me.ComboBox1.Clear

For Each olCi In Fldr.Items
Me.ComboBox1.AddItem olCi.FullName 'Full Name -
Change to Company Name?
Next olCi

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer,
CloseMode As Integer)

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Private Sub CommandButton1_Click()
Option Explicit

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

For Each olCi In olFldr.Items
If olCi.FullName = Me.ComboBox1.Value Then
Sheet1.Range("d3").Value = olCi.CompanyName
Sheet1.Range("d4").Value = olCi.BusinessAddress
'Other data you want to write goes here in the
same format
End If
Next olCi


'Hides UserForm1
'UserForm1.Hide
Unload Me

End Sub

Can you please let me know what I need to change!

Thanks again so much. I feel like I am SO CLOSE to
getting this :-)

Shauna


-----Original Message-----
Shauna

Change this part


For Each olCi In Fldr.Items
If olCi.NickName = "DoubleD" Then
Debug.Print olCi.FullName,
olCi.Email1Address
End If
Next olCi


to

Me.ComboBox1.Clear

For Each olCi In Fldr.Items
Me.ComboBox1.AddItem olCi.FullName
Next olCi

That will populate the combobox with all of the contacts
in the folder.
Then move this macro to the Initialize event so that the
combobox is
re-populated each time you run the form.

I assume the button should put the relevant data on the
worksheet, so make
your button's click event look something like this

For Each olCi In olFldr.Items
If olCi.FullName = Me.ComboBox1.Value Then
Sheet1.Range("d3").Value = olCi.CompanyName
Sheet1.Range("d4").Value = olCi.BusinessAddress
'Other data you want to write goes here in the
same format
End If
Next olCi

This loops through the contacts and find the one that
matches the one
selected in the combobox. Then it writes the pertinent
information to
whatever cells you choose.

Here's another thing you want to do: Make module level
variables for olApp,
olNs and olFldr - that is, Dim those variables above all
your procedures,
but below the Option Explicit statement. The Code Module
behind your
userform should look roughly like this

Option Explicit

Dim olApp as Outlook.Application
Dim olNs as Outlook.NamesSpace
Dim olFldr as Outlook.MAPIFolder

Private Sub CommandButton1_Click()
'code to write to cells

Unload Me 'Don't just hide, actually unload it
End Sub

Private Sub Userform_Initialize()
Set olApp = New Outlook.Application
Set olNs = olApp.GetNameSpace("MAPI")
Set olFldr = olNs.GetDefaultFolder("Contacts")

'populate combobox here
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer,
CloseMode As Integer)

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

This way, you'll only have to connect to Outlook when the
userform is loaded
and disconnect when it is unloaded as opposed to
connecting to it everytime
you do something with outlook. The variables (module
level variables) at
the top can be used in any sub in that module, so you
don't have to Dim them
in every module.

That's a lot of info, I know. Do what you can with it
and post back when
you get stuck. For a faster reply, make sure you reply
to this post - it
will show up as red in my newsreader and I'll see it for
sure that way.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


.

Shauna

What about it doesn't work? Read on.



Private Sub UserForm_Initialize()

'Sets ComboBox Outlook Contacts FullName


Put these three Dim lines above any code you have in that module - above the
first line that says Sub. By Dimming these inside the Initialize procedure,
they will only be available to this sub - so define them outside of all subs
and they will be available to all the subs.

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder

The rest of this looks OK, but you're probably getting a compile error
because of the above.

Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder("Contacts")


Me.ComboBox1.Clear

For Each olCi In Fldr.Items
Me.ComboBox1.AddItem olCi.FullName 'Full Name -
Change to Company Name?
Next olCi

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer,
CloseMode As Integer)


The variables are not defined in the scope of this procedure. Putting the
three Dim statements outside of any Subs will solve that. I assume this is
where you're getting the error.

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Private Sub CommandButton1_Click()
Option Explicit


Get rid of these three Dim statements. You'll be using the variables that
you define outside of any Subs for every sub that references them.

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder

This looks fine. Make sure your ComboBox is called ComboBox1 and the sheet
to which you are trying to write is called Sheet1. You can also use
something like ThisWorkbook.Sheets("MySheetName").Range...

Dim olCi As ContactItem

For Each olCi In olFldr.Items
If olCi.FullName = Me.ComboBox1.Value Then
Sheet1.Range("d3").Value = olCi.CompanyName
Sheet1.Range("d4").Value = olCi.BusinessAddress
'Other data you want to write goes here in the
same format
End If
Next olCi


'Hides UserForm1
'UserForm1.Hide
Unload Me

End Sub

Can you please let me know what I need to change!

Thanks again so much. I feel like I am SO CLOSE to
getting this :-)

Shauna

Keep posting, we'll figure it out.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.