OUTOOK Contact Items Paste to Excel

[terro]

Dear All,

As a newbie I try to paste some outlook contact items (name , tel, etc..) in excel cells.

I created some outlook "customs fileds "

Apparently autoit window info does not grap information on the the basic fields as Name , address etc.

any Idea how to do.

I thought getting the contact Name by mouse position with mouseclick...

any idea welcome

Guillaume

Thank by advance

[MrMitchell]

Hello and welcome,

You could benefit from the use of the Outlook and Word UDFs. Word is built-in and Outlook info can be found

[water]

paste some outlook contact items (name , tel, etc..) in excel cells.

I created some outlook "customs fields"

The specified Outlook UDF will soon be superseded by the new OutlookEX (like extended) UDF.

An alpha version is already available (for download please see my signature) but at the moment it is not as "user friendly" as it should be.

If you can tell me in which folder you want to search for the contacts, how you want to filter the contacts ("only take the contacts from company ...") and the info you want from each contact I can provide a working example.

[terro]

The specified Outlook UDF will soon be superseded by the new OutlookEX (like extended) UDF.

An alpha version is already available (for download please see my signature) but at the moment it is not as "user friendly" as it should be.

If you can tell me in which folder you want to search for the contacts, how you want to filter the contacts ("only take the contacts from company ...") and the info you want from each contact I can provide a working example.

Dear Water Great your code is powerfull and ready to be used with the examples you give.

To answer to your question

My idea is to work on a single open contact (double click on a contac to open it) then drop his name in an new excel file in cell A1.

Company name in A2 cell

Tel in D1

For the moment I don't need to work on a full list of contacts only single contact

Thank you for your answer

Guillaume

[water]

Something like this:

#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include  <OutlookEX.au3>
#include <Excel.au3>

Global $oOutlook = _OL_Open()
; Access contact item displayed in an active inspector
Global $oOL_Item = $oOutlook.ActiveInspector.CurrentItem
If Not IsObj($oOL_Item) Then Exit MsgBox(16, "Test Script", "No Contact selected")
If  $oOL_Item.Class <> $olContact Then Exit MsgBox(16, "Test Script", "Selected item is not a contact")
; write data to excel
Global $oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, $oOL_Item.LastNameAndFirstName, "A1")
_ExcelWriteCell($oExcel, $oOL_Item.Company, "A2")
_ExcelWriteCell($oExcel, $oOL_Item.PrimaryTelephoneNumber, "D1")

The properties for Outlook 2007 can be found here.

Edited March 15, 2011 by water

[terro]

Something like this:

#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include  <OutlookEX.au3>
#include <Excel.au3>

Global $oOutlook = _OL_Open()
; Access contact item displayed in an active inspector
Global $oOL_Item = $oOutlook.ActiveInspector.CurrentItem
If Not IsObj($oOL_Item) Then Exit MsgBox(16, "Test Script", "No Contact selected")
If  $oOL_Item.Class <> $olContact Then Exit MsgBox(16, "Test Script", "Selected item is not a contact")
; write data to excel
Global $oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, $oOL_Item.LastNameAndFirstName, "A1")
_ExcelWriteCell($oExcel, $oOL_Item.Company, "A2")
_ExcelWriteCell($oExcel, $oOL_Item.PrimaryTelephoneNumber, "D1")

The properties for Outlook 2007 can be found here.

Dear Water

you're a star and your code works perfectly

THANK you very much

I will continu and let you know

Guillaume

[water]

Glad it works

[terro]

Glad it works

Hi Water it's me again sorrrry

I've noticed last july you wrote to MYTEAM ::

Posted 06 July 2010 - 07:23 AM

I think it should read:

[ autoIt ] ( Popup )

$sSolution = $oNewItem.UserProperties("TextBox17").Value msgbox(0,"","Solution: " & $sSolution)

In my contact form I created a custom field called "Textbox17" and could not pop it's value as mentioned above

In the same way I want to drop my customs fields in EXCEL

lets say my custom field is a date format called ""next call date""

how to paste it's content in excel file let say in E1 cell

thanks

Guillaume

[water]

MSDN mentiones to check the existence of a costums field like this:

$oProperty = $oItem.UserProperties.Find("Textbox17")        
If isobj($oProperty) then MsgBox(0,"", "Value of property Textbox17: " & $oProperty.Value

At the moment I'm sitting at my Ubuntu PC - so I can't test it.

Could you please try and report if it works?

As UserProperties is a collection it's easy to list all custom UserProperties.

[terro]

MSDN mentiones to check the existence of a costums field like this:

$oProperty = $oItem.UserProperties.Find("Textbox17")        
If isobj($oProperty) then MsgBox(0,"", "Value of property Textbox17: " & $oProperty.Value

At the moment I'm sitting at my Ubuntu PC - so I can't test it.

Could you please try and report if it works?

As UserProperties is a collection it's easy to list all custom UserProperties.

Dear Water

I've checked I got a error message

"""==> Variable used without being declared.:

$oProperty = $oItem.UserProperties.Find("Textbox16")

$oProperty = ^ ERROR"""

I suspect I missed something like declaring a variable but how

For sure the answer is obvious for you

I'm afraid i need your help again

Thank you

Guillaume

[water]

Sorry, my bad. Based on my previous script you have to use:

$oProperty = $oOL_Item.UserProperties.Find("Textbox17")        
If isobj($oProperty) then MsgBox(0,"", "Value of property Textbox17: " & $oProperty.Value)

Edited March 15, 2011 by water

[terro]

Sorry, my bad. Based on my previous script you have to use:

$oProperty = $oOL_Item.UserProperties.Find("Textbox17")        
If isobj($oProperty) then MsgBox(0,"", "Value of property Textbox17: " & $oProperty.Value)

Dear Water

Bingo you're right again.

In case I would like to reverse this process, could you please help

I mean dropping the excel cells values to a new contact form including custom fields

so if A1 cell content is "BOND" then copy this "BOND" value in a new contact form in Name field

so if A2 cell content is "height" the copy this "height" value in the previous contact form in the custom field declared as "Textbox17"

Thank you Champion

Guillaume

[water]

Hi Guillaume,

this is easy as well

I have no Windows system available at the moment - I will reply with more information tomorrow.

[terro]

Hi Guillaume,

this is easy as well

I have no Windows system available at the moment - I will reply with more information tomorrow.

Dear Water,

Waiting for your support 2morrow

If we keep an eye on business address street we can notice it is a [concatenated chain] containing [street 1 CHR13 street 2]

_ExcelWriteCell($oExcel, $oOL_Item.BusinessAddressstreet, "A3")

How to deconcatenate these 2 streets in [street 1] and [street 2]

thank you Water

[water]

How to deconcatenate these 2 streets in [street 1] and [street 2]

Quick answer: StringSplit is your friend.

[water]

so if A1 cell content is "BOND" then copy this "BOND" value in a new contact form in Name field

so if A2 cell content is "height" the copy this "height" value in the previous contact form in the custom field declared as "Textbox17"

#include <OutlookEX.au3>
#include <Excel.au3>
; Get data from Excel
$oExcel = ExcelBookOpen("C:\temp\test.xls")
$sName = _ExcelReadCell($oExcel, "A1")
$sUserProperty = _ExcelReadCell($oExcel, "A2")
_ExcelBookClose($oExcel)
; Create a new contact
$oOutlook = _OL_Open()
$oFolder = _OL_FolderAccess($oOutlook, "*\Contacts")              ; <== needs to be changed
$oItem = _OL_ItemCreate($oOutlook, $olContactItem, $oFolder, "", "Name=" & $sName)
$oItem.UserProperties.item("Textbox17").value = $sUserProperty    ; <== not sure about that

[terro]

#include <OutlookEX.au3>
#include <Excel.au3>
; Get data from Excel
$oExcel = ExcelBookOpen("C:\temp\test.xls")
$sName = _ExcelReadCell($oExcel, "A1")
$sUserProperty = _ExcelReadCell($oExcel, "A2")
_ExcelBookClose($oExcel)
; Create a new contact
$oOutlook = _OL_Open()
$oFolder = _OL_FolderAccess($oOutlook, "*\Contacts")              ; <== needs to be changed
$oItem = _OL_ItemCreate($oOutlook, $olContactItem, $oFolder, "", "Name=" & $sName)
$oItem.UserProperties.item("Textbox17").value = $sUserProperty    ; <== not sure about that

Dear Water;

you are right with StringSplit it works, thanks a lot

I had a look in your UDF example _OL_FolderExists.au3 and picked this line of code

$oItem =_OL_ItemCreate($oOutlook, $olContactItem, "*\Contacts", "", "FirstName=" & $sName)

It works on a standard contact but I can't figure out how to fill a "custom contact form"

Maybe "$olContactItem" should take the name of the new "custom contact form" I made.

This "custom contact form" has some custom fields.

probably "$olContactItem" work only with the standard IPM.contact.

Water, Any Idea how to fill a "custom contact form" ?

Thank you

Guillaume

Edited March 16, 2011 by terro

[water]

Any Idea how to fill a "custom contact form"?

Not yet, will have to do some research.

Edit: Maybe sheds some light on the subject.

I will have a look at this tomorrow!

Edited March 16, 2011 by water