How do I enter Outlook contact data into excel?

[FSTC892]

I create sales quotes to customers in Excel. I want to be able to lookup the
customer contact information that is already in Outlook while working in
Excel and automatically copy or enter that contact information into my sales
quote in Excel. Is this possible?

[Michael]

Hi FSTC892.
The only way I can think of you doing that is to first export your contacts
folder to a .csv file and then open it with excel. Export is on the File
Menu. Maybe one of the MVP's knows a better way. HTH
--
Sincerely, Michael Colvin


"FSTC892" wrote:

> I create sales quotes to customers in Excel. I want to be able to lookup the
> customer contact information that is already in Outlook while working in
> Excel and automatically copy or enter that contact information into my sales
> quote in Excel. Is this possible?

[Steve Yandl]

It's possible but you might have to do some tweaking to get exactly what you
want.

The function and subroutine below is one I use to populate sheet(1) with
first name, last name, and email address. I keep postal address info in
Access but this could be expanded to extract the info from any of the fields
you use in Outlook. I also suspect that you want some sort of form with a
drop down list of Contacts which can also be done but would be a bit of
work, especially not knowing exactly how you want the whole thing to work.
Hope this gives you a decent head start. I suspect some will have routines
closer to what you want.

Public olApp As Outlook.Application
Public olNS As Outlook.NameSpace


Function InitOutlook() As Boolean
On Error GoTo Init_Error
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

InitOutlook = True

Init_End:
Exit Function
Init_Error:
InitOutlook = False
Resume Init_End
End Function

Sub ContactGrab()
Dim fdContacts As Outlook.MAPIFolder
Dim fdItems As Outlook.Items
Dim fdItem As Object
Dim R As Integer

If olApp Is Nothing Then
If InitOutlook = False Then
MsgBox "Unable to initialize Outlook application or namespace"
Exit Sub
End If
End If

Set fdContacts = olNS.GetDefaultFolder(olFolderContacts)
Set fdItems = fdContacts.Items

Sheets("Sheet1").UsedRange.Clear

R = 1
With Sheets("Sheet1")
.Rows("1").Font.Bold = True
.Cells(1, 1).Value = "Contacts First Name"
.Cells(1, 2).Value = "Contacts Last Name"
.Cells(1, 3).Value = "Contacts Email Address"
.Columns("A").ColumnWidth = 32
.Columns("B").ColumnWidth = 36
.Columns("C").ColumnWidth = 26

End With

For Each fdItem In fdItems
On Error Resume Next
R = R + 1
With Sheets("Sheet1")
.Cells(R, 1).Value = fdItem.FirstName
.Cells(R, 2).Value = fdItem.LastName
.Cells(R, 3).Value = fdItem.Email1Address
End With
Next

End Sub


Steve



"FSTC892" <[email protected]> wrote in message
news:[email protected]...
>I create sales quotes to customers in Excel. I want to be able to lookup
>the
> customer contact information that is already in Outlook while working in
> Excel and automatically copy or enter that contact information into my
> sales
> quote in Excel. Is this possible?

[Frank Parker]

Hi Steve,
Thankyou for the solution that I am looking for, however, I do not have an
idea how to use the info you have provided. Could you give further
instruction on how to insert a contact?

"Steve Yandl" wrote:

> It's possible but you might have to do some tweaking to get exactly what you
> want.
>
> The function and subroutine below is one I use to populate sheet(1) with
> first name, last name, and email address. I keep postal address info in
> Access but this could be expanded to extract the info from any of the fields
> you use in Outlook. I also suspect that you want some sort of form with a
> drop down list of Contacts which can also be done but would be a bit of
> work, especially not knowing exactly how you want the whole thing to work.
> Hope this gives you a decent head start. I suspect some will have routines
> closer to what you want.
>
> Public olApp As Outlook.Application
> Public olNS As Outlook.NameSpace
>
>
> Function InitOutlook() As Boolean
> On Error GoTo Init_Error
> Set olApp = New Outlook.Application
> Set olNS = olApp.GetNamespace("MAPI")
>
> InitOutlook = True
>
> Init_End:
> Exit Function
> Init_Error:
> InitOutlook = False
> Resume Init_End
> End Function
>
> Sub ContactGrab()
> Dim fdContacts As Outlook.MAPIFolder
> Dim fdItems As Outlook.Items
> Dim fdItem As Object
> Dim R As Integer
>
> If olApp Is Nothing Then
> If InitOutlook = False Then
> MsgBox "Unable to initialize Outlook application or namespace"
> Exit Sub
> End If
> End If
>
> Set fdContacts = olNS.GetDefaultFolder(olFolderContacts)
> Set fdItems = fdContacts.Items
>
> Sheets("Sheet1").UsedRange.Clear
>
> R = 1
> With Sheets("Sheet1")
> .Rows("1").Font.Bold = True
> .Cells(1, 1).Value = "Contacts First Name"
> .Cells(1, 2).Value = "Contacts Last Name"
> .Cells(1, 3).Value = "Contacts Email Address"
> .Columns("A").ColumnWidth = 32
> .Columns("B").ColumnWidth = 36
> .Columns("C").ColumnWidth = 26
>
> End With
>
> For Each fdItem In fdItems
> On Error Resume Next
> R = R + 1
> With Sheets("Sheet1")
> .Cells(R, 1).Value = fdItem.FirstName
> .Cells(R, 2).Value = fdItem.LastName
> .Cells(R, 3).Value = fdItem.Email1Address
> End With
> Next
>
> End Sub
>
>
> Steve
>
>
>
> "FSTC892" <[email protected]> wrote in message
> news:[email protected]...
> >I create sales quotes to customers in Excel. I want to be able to lookup
> >the
> > customer contact information that is already in Outlook while working in
> > Excel and automatically copy or enter that contact information into my
> > sales
> > quote in Excel. Is this possible?
>
>
>

[Frank Parker]

Hi Steve
Second question?
I inserted your language into a macro and tried to run it. I got the
following error:
Compile error. User defined type not defined.

I am sorry but I am a novice when it comes to Visual Basic.

I assume the problem is that the macro does not know where my contact info
is located. How do I resolve this problem?

"Steve Yandl" wrote:

> It's possible but you might have to do some tweaking to get exactly what you
> want.
>
> The function and subroutine below is one I use to populate sheet(1) with
> first name, last name, and email address. I keep postal address info in
> Access but this could be expanded to extract the info from any of the fields
> you use in Outlook. I also suspect that you want some sort of form with a
> drop down list of Contacts which can also be done but would be a bit of
> work, especially not knowing exactly how you want the whole thing to work.
> Hope this gives you a decent head start. I suspect some will have routines
> closer to what you want.
>
> Public olApp As Outlook.Application
> Public olNS As Outlook.NameSpace
>
>
> Function InitOutlook() As Boolean
> On Error GoTo Init_Error
> Set olApp = New Outlook.Application
> Set olNS = olApp.GetNamespace("MAPI")
>
> InitOutlook = True
>
> Init_End:
> Exit Function
> Init_Error:
> InitOutlook = False
> Resume Init_End
> End Function
>
> Sub ContactGrab()
> Dim fdContacts As Outlook.MAPIFolder
> Dim fdItems As Outlook.Items
> Dim fdItem As Object
> Dim R As Integer
>
> If olApp Is Nothing Then
> If InitOutlook = False Then
> MsgBox "Unable to initialize Outlook application or namespace"
> Exit Sub
> End If
> End If
>
> Set fdContacts = olNS.GetDefaultFolder(olFolderContacts)
> Set fdItems = fdContacts.Items
>
> Sheets("Sheet1").UsedRange.Clear
>
> R = 1
> With Sheets("Sheet1")
> .Rows("1").Font.Bold = True
> .Cells(1, 1).Value = "Contacts First Name"
> .Cells(1, 2).Value = "Contacts Last Name"
> .Cells(1, 3).Value = "Contacts Email Address"
> .Columns("A").ColumnWidth = 32
> .Columns("B").ColumnWidth = 36
> .Columns("C").ColumnWidth = 26
>
> End With
>
> For Each fdItem In fdItems
> On Error Resume Next
> R = R + 1
> With Sheets("Sheet1")
> .Cells(R, 1).Value = fdItem.FirstName
> .Cells(R, 2).Value = fdItem.LastName
> .Cells(R, 3).Value = fdItem.Email1Address
> End With
> Next
>
> End Sub
>
>
> Steve
>
>
>
> "FSTC892" <[email protected]> wrote in message
> news:[email protected]...
> >I create sales quotes to customers in Excel. I want to be able to lookup
> >the
> > customer contact information that is already in Outlook while working in
> > Excel and automatically copy or enter that contact information into my
> > sales
> > quote in Excel. Is this possible?
>
>
>