S
Shauna Koppang
I have the following coding which pulls information from a
public folder in Outlook displaying Company in ComboBox2
and I would like to create another ComboBox3 that is I
choose a name in ComboBox2 of a company e.g. ABC Company
who may have 2-3 items in that folder, that ComboBox3 will
display the names of those Full names to help the user
pick the right one. Public Folders/All Public
Folders/Shared Public Folders/Purchase Orders - Test
Clients
E.g. ComboBox2 ComboBox3
(Company) (Full Name)
ABC Company John Smith
ABC Company Jane Doe
ABC Company Susan Wilson
Also,
I would like to have ComboBox4 (and possibly ComboBox5)
pulling from another PublicFolder called under Purchase
Orders - Test called Vendors doing the same as above.
Folders/All Public Folders/Shared Public Folders/Purchase
Orders - Test Vendors
E.G. ComboBox4 ComboBox5
(Company) (Full Name)
Ingram Micro Ryan Jones
Ingram Micro Bob Smith
And then there appears to be error handling!! Yikes. If
the Company they choose right now in Clients is missing
info in a specific field e.g. email address 1 I get an
error.
I am now WAY over my head! Please Help!
Thanks!
Shauna
'Tools menu, References ensure both Outlook choices are
made
Option Explicit
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim olFldr As MAPIFolder
'Dim mynewfolder As MAPIFolder
Private Sub UserForm_Initialize()
Dim myItems As Outlook.Items
Dim olCi As ContactItem
'Sets ComboBox RowSource
ComboBox1.RowSource = "UserNames"
'Sets the first item to be the default
UserForm1.ComboBox1.ListIndex = 0
'Sets ComboBox2 Outlook Contacts FullName
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
'Sets to Personal Default folders
'Set olFldr = olNs.GetDefaultFolder(olFolderContacts
'Sets for Public Folders
Set olFldr = olNs.Folders("Public Folders").Folders _
("All Public Folders").Folders("Shared Public
Folders").Folders _
("Purchase Orders - Test").Folders("Clients")
'Set mynewfolder = olFldr.Folders("Old Contacts")
Me.ComboBox2.Clear
Set myItems = olFldr.Items
myItems.Sort "[CompanyName]", False
'For Each olCi In olFldr.Items
For Each olCi In myItems
'For Each olCi In mynewfolder.Items
Me.ComboBox2.AddItem olCi.CompanyName
Next olCi
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer,
CloseMode As Integer)
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
'Set mynewfolder = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim olCi As ContactItem
'For Each olCi In mynewfolder.Items
For Each olCi In olFldr.Items
If olCi.CompanyName = Me.ComboBox2.Value Then
Sheet1.Range("E9").Value = olCi.CompanyName
Sheet1.Range("E10").Value =
olCi.BusinessAddress
Sheet1.Range("E11").Value =
olCi.BusinessTelephoneNumber ' & " Tel."
Sheet1.Range("E12").Value =
olCi.BusinessFaxNumber ' & " Fax."
Sheet1.Range("E13").Value = olCi.FullName
Sheet1.Range("E14").Value = olCi.Email1Address
'Other data you want to write goes here in the
same Format
End If
Next olCi
'Go to Cell A10 and AutoFit Row
'Application.Goto Reference:="R10C1"
'Selection.Rows.AutoFit
'Remove square from E10
Dim vendortext As String
vendortext = Sheet1.Range("E10").Value
'using worksheets("sheet1") is referencing the name of
the worksheets which _
in this case is "Entry Form - Internal PO", so either
change "Sheet1" to "Entry ..." _
or use general reference as I have below
'Worksheets("Sheet1").Range("E10").Value = Replace
(vendortext, Chr(10), "")
'Worksheets("Sheet1").Range("E10").Value = Replace
(vendortext, Chr(13), "")
Sheet1.Range("E10").Value = Replace(vendortext, Chr
(10), "")
Sheet1.Range("E10").Value = Replace(vendortext, Chr
(13), "")
Sheet1.Range("A13") = ComboBox1.Text
'Hides UserForm1
Unload Me
End Sub
public folder in Outlook displaying Company in ComboBox2
and I would like to create another ComboBox3 that is I
choose a name in ComboBox2 of a company e.g. ABC Company
who may have 2-3 items in that folder, that ComboBox3 will
display the names of those Full names to help the user
pick the right one. Public Folders/All Public
Folders/Shared Public Folders/Purchase Orders - Test
Clients
E.g. ComboBox2 ComboBox3
(Company) (Full Name)
ABC Company John Smith
ABC Company Jane Doe
ABC Company Susan Wilson
Also,
I would like to have ComboBox4 (and possibly ComboBox5)
pulling from another PublicFolder called under Purchase
Orders - Test called Vendors doing the same as above.
Folders/All Public Folders/Shared Public Folders/Purchase
Orders - Test Vendors
E.G. ComboBox4 ComboBox5
(Company) (Full Name)
Ingram Micro Ryan Jones
Ingram Micro Bob Smith
And then there appears to be error handling!! Yikes. If
the Company they choose right now in Clients is missing
info in a specific field e.g. email address 1 I get an
error.
I am now WAY over my head! Please Help!
Thanks!
Shauna
'Tools menu, References ensure both Outlook choices are
made
Option Explicit
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim olFldr As MAPIFolder
'Dim mynewfolder As MAPIFolder
Private Sub UserForm_Initialize()
Dim myItems As Outlook.Items
Dim olCi As ContactItem
'Sets ComboBox RowSource
ComboBox1.RowSource = "UserNames"
'Sets the first item to be the default
UserForm1.ComboBox1.ListIndex = 0
'Sets ComboBox2 Outlook Contacts FullName
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
'Sets to Personal Default folders
'Set olFldr = olNs.GetDefaultFolder(olFolderContacts
'Sets for Public Folders
Set olFldr = olNs.Folders("Public Folders").Folders _
("All Public Folders").Folders("Shared Public
Folders").Folders _
("Purchase Orders - Test").Folders("Clients")
'Set mynewfolder = olFldr.Folders("Old Contacts")
Me.ComboBox2.Clear
Set myItems = olFldr.Items
myItems.Sort "[CompanyName]", False
'For Each olCi In olFldr.Items
For Each olCi In myItems
'For Each olCi In mynewfolder.Items
Me.ComboBox2.AddItem olCi.CompanyName
Next olCi
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer,
CloseMode As Integer)
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
'Set mynewfolder = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim olCi As ContactItem
'For Each olCi In mynewfolder.Items
For Each olCi In olFldr.Items
If olCi.CompanyName = Me.ComboBox2.Value Then
Sheet1.Range("E9").Value = olCi.CompanyName
Sheet1.Range("E10").Value =
olCi.BusinessAddress
Sheet1.Range("E11").Value =
olCi.BusinessTelephoneNumber ' & " Tel."
Sheet1.Range("E12").Value =
olCi.BusinessFaxNumber ' & " Fax."
Sheet1.Range("E13").Value = olCi.FullName
Sheet1.Range("E14").Value = olCi.Email1Address
'Other data you want to write goes here in the
same Format
End If
Next olCi
'Go to Cell A10 and AutoFit Row
'Application.Goto Reference:="R10C1"
'Selection.Rows.AutoFit
'Remove square from E10
Dim vendortext As String
vendortext = Sheet1.Range("E10").Value
'using worksheets("sheet1") is referencing the name of
the worksheets which _
in this case is "Entry Form - Internal PO", so either
change "Sheet1" to "Entry ..." _
or use general reference as I have below
'Worksheets("Sheet1").Range("E10").Value = Replace
(vendortext, Chr(10), "")
'Worksheets("Sheet1").Range("E10").Value = Replace
(vendortext, Chr(13), "")
Sheet1.Range("E10").Value = Replace(vendortext, Chr
(10), "")
Sheet1.Range("E10").Value = Replace(vendortext, Chr
(13), "")
Sheet1.Range("A13") = ComboBox1.Text
'Hides UserForm1
Unload Me
End Sub
