Sign in to follow this  
Followers 0
terro

OUTOOK Contact Items Paste to Excel

18 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Glad it works :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Glad it works :P

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi Guillaume,

this is easy as well :)

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Quick answer: StringSplit is your friend.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

#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 by terro

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0