Find outlook contact information based on selected email sender

[travisdh]

Hi All,

I am using the below code, to take the selected email and open a subform that contains a lot of text boxes for things like the contact name, email address, address details and so on.

That part works alright, however what i would like to do is look in outlooks contacts for the entry that has the same name, and email address and be able to set the other textboxes in the form to that information. The idea being that i would select an email, and it would extract the users name, the email address, and say a quote or ticket number from that email, and then when i click a button on the userform it would populate all of the users company details, addresses, contact details and so on.

Does anyone have some sample code they would be willing to share that would allow this to be done, once i can get an example of pulling say company name from a contact entry into a variable i can work from there but at the moment I really am quite unsure about how to find a specific entry, have seen so many entries that loop through contacts, but that is not really suitable.

Appreciate any help you can provide.

Thanks!



Sub CreateJob()
Dim myOlExp As Explorer
Dim myOlSel As Selection
Dim myOlMail As MailItem
Dim myXLApp As Excel.Application
Dim myXLWB As Excel.Workbook
Dim extract As String
Dim j As Long
Dim sender As String
Dim email As String

Set myXLApp = New Excel.Application
Set myXLWB = myXLApp.Workbooks.Add
Set myOlExp = ActiveExplorer
Set myOlSel = myOlExp.Selection
Dim standard As String
Dim test As String


For j = 1 To myOlSel.count
Set myOlMail = myOlSel.Item(j)
extract = myOlMail.SenderName
sender = myOlMail.SenderName
email = myOlMail.SenderEmailAddress
Next

UserForm1.Show
UserForm1.txt_clientname.Text = sender
UserForm1.txt_email.Text = email


End Sub

[JP2112]

You would set a reference to the contacts folder item of the same name. Something like this:

Dim contacts As Outlook.Items
Dim contact As Outlook.ContactItem
Set contacts = Session.GetDefaultFolder(olFolderContacts).Items
On Error Resume Next
Set contact = contacts.Item("contact name")
If Err.Number <> 0 Then ' you have the contact now
' extract properties of the Contact item here
Else
MsgBox "No such contact"
Exit Sub
End If

[travisdh]

Thank you for your help, how do I go about selecting the appropiate folder as our setup has the contacts as items in a folder, in public folders.

I thought about specifying .Folder("Public Folders").Folder("Favorites").Folder("Shared Contacts") but it would not work (that is the correct folder setup \Public Folders\Favorites\Shared Contacts

Also on a side note, i am curious about the Set contact, when you set a contact do you need to do a find, or can you set it directly by the Full Name, and if so how does outlook know you are specifying the full name?

Thanks for your help! :)

[JP2112]

If it is a public folder it should be something like

Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Favorites") .. etc
Every item type has a different default property, just like in Excel you can write Range("A1") = "Hello World" or Range("A1").Value = "Hello World" because .Value is the default property for a Range Object.

In Outlook (at least, in Office 2003) the Subject Property (http://msdn.microsoft.com/en-us/library/office/aa212425(v=office.11).aspx) is the default property for all items. If you select a Contact and run this code you will get the full name:


ActiveExplorer.Selection.Item(1).Subject