 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Outlook / Programming VBA / June 2006Extract Outlook Address book properties into Excel
Hi All, How is it possible to extract all Addressbook properties like phone, email address, full name, business contact details & Customer user fields like EmpID & put them each in 1 column after another in an Excel sheet, 1 record (Row) for each mail received in Outlook. i.e. each Row would contain each mail sender's address book properties & each property would be in a Column. I want to especially get the EmpID (Employee ID) for each Sender collected in an Excel column. How do i do that? Warm Regards, Junoon Am 20 Jun 2006 11:53:57 -0700 schrieb Junoon: This sample shows how to insert data in a new Worksheet: Dim xl as Excel.Application Dim wb as Excel.Workbook Dim ws as Excel.Worksheet Dim rn as Excel.Range Set xl=New Excel.Application Set wb=xl.Workbooks.Add Set ws=wb.Worksheets(1) ' Write data into first column, second row Set rn=ws.Range("a2") rn.Value = "something" ' Write data into next column rn.Offset(0,1).Value="more" Now simply loop through your contacts, read whatever you´re interested in and write it into the worksheet.  SignatureViele Gruesse / Best regards Michael Bauer - MVP Outlook -- www.VBOffice.net -- > Hi All, > [quoted text clipped - 14 lines] > > Junoon Hi Michael, My main concern is not the code to get the data into the worksheet columns, but HOW TO access (Loop Thru) ALL the Outlook Contact properties one by one. Warm Regards, Junoon > Am 20 Jun 2006 11:53:57 -0700 schrieb Junoon: > [quoted text clipped - 42 lines] > > > > Junoon Hi Michael, What i am trying to do is to access the Mail Senders properties (the Office people -belonging to our Global Address List), who have sent me mails & all mails are having a Unique Subject. I am trying to scan each mail & then access the Mail senders Employee ID (EmpID, i guess custom defined) & other properties also like his phone # or mobile # etc. Then dump them one by one in columns in Excel. But how to access these mail item properties, especially a Custom-defined type like EmpID. The reason why i am trying to get the EmpID is because its a Unique ID given to an Employee & would help in a VLOOKUP with my Depts HeadCount.xls file for storing correct Mailitem data under the correct Employee ID in the HeadCount.xls file which we have to update on a Daily basis. Hope this Helps! Warem Regards, Junoon > Am 20 Jun 2006 11:53:57 -0700 schrieb Junoon: > [quoted text clipped - 42 lines] > > > > Junoon Am 21 Jun 2006 01:53:03 -0700 schrieb Junoon: You know how to loop through folder items, you did it in a previous thread (Find...FindNext). All custom properties are available via the UserProperties collection: Dim v as Variant v=Item.UserProperties("EmpID").Value SignatureViele Gruesse / Best regards Michael Bauer - MVP Outlook -- www.VBOffice.net -- > Hi Michael, > [quoted text clipped - 67 lines] >>> >>> Junoon Thanks will try that & let you know. Warm Regards, Junoon --------------- > Am 21 Jun 2006 01:53:03 -0700 schrieb Junoon: > [quoted text clipped - 82 lines] > >>> > >>> Junoon Hi Michael, Tried the solution you gave, but did not seem to work. I think since the Employee names & Emp Code belongs to a Global Address list, so they donot come under User defined properties. What i tried to do was search for the Emp Code (Emp ID) by double-clicking on a mail address. The Emp Code shows under the "General" Tab but when i search for it in Contacts, Journals, User-defined fields etc, i am unable to locate it. I am stumped! How do i get access to it in such a scenario. Warm Regards, Junoon > Thanks will try that & let you know. > [quoted text clipped - 89 lines] > > >>> > > >>> Junoon Am 26 Jun 2006 14:37:02 -0700 schrieb Junoon: You asked me how to access user properties, and the sample works. Please ask your administrator, or whoever is responsible, where your data is stored. SignatureViele Gruesse / Best regards Michael Bauer - MVP Outlook -- www.VBOffice.net -- > Hi Michael, > [quoted text clipped - 107 lines] >>> >>> >>> >>> Junoon Data in the GAL is not Outlook contact data. You can use Outlook objects to get only the bare basics, like name and address (and not even the SMTP address at that). For other properties, you must use CDO 1.21 or, to avoid security prompts, Redemption. See http://www.outlookcode.com/codedetail.aspx?id=594 for a sample that shows how to return the mobile phone number from a GAL user entry. SignatureSue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx Hi Michael, Tried the solution you gave, but did not seem to work. I think since the Employee names & Emp Code belongs to a Global Address list, so they donot come under User defined properties. What i tried to do was search for the Emp Code (Emp ID) by double-clicking on a mail address. The Emp Code shows under the "General" Tab but when i search for it in Contacts, Journals, User-defined fields etc, i am unable to locate it. I am stumped! How do i get access to it in such a scenario. > Thanks will try that & let you know. > [quoted text clipped - 91 lines] > > >>> > > >>> Junoon Hi Sue, I had downloaded & installed Outlook Spy on my Office PC, but am still unable to Locate the "Emp.Code" field. Can you please provide some guidelines as to where to look for it on the Toolbar thats installed in MS Outlook now. Warm regards, Junoon > Data in the GAL is not Outlook contact data. You can use Outlook objects to get only the bare basics, like name and address (and not even the SMTP address at that). For other properties, you must use CDO 1.21 or, to avoid security prompts, Redemption. See http://www.outlookcode.com/codedetail.aspx?id=594 for a sample that shows how to return the mobile phone number from a GAL user entry. > [quoted text clipped - 115 lines] > > > >>> > > > >>> Junoon 1) IAddrBook 2) Switch to the GetSearchPath tab. Select GLobal Address Book and click OpenEntry. 3) Switch to the GetContentsTable tab. Select an entry that you has known data in the property you're interested in. Click OpenEntry. 4) Browse the Value column until you find the known data. That will be the property you want. Make note of that property's "Tag num" value at upper right. That is the MAPI property tag you need to use to extract that value programmatically from the AddressEntry.Fields collection. SignatureSue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx Hi Sue, I had downloaded & installed Outlook Spy on my Office PC, but am still unable to Locate the "Emp.Code" field. Can you please provide some guidelines as to where to look for it on the Toolbar thats installed in MS Outlook now. Warm regards, Junoon Sue Mosher [MVP-Outlook] wrote: > Data in the GAL is not Outlook contact data. You can use Outlook objects to get only the bare basics, like name and address (and not even the SMTP address at that). For other properties, you must use CDO 1.21 or, to avoid security prompts, Redemption. See http://www.outlookcode.com/codedetail.aspx?id=594 for a sample that shows how to return the mobile phone number from a GAL user entry. > "Junoon" <sifar786@gmail.com> wrote in message news:1151357822.934834.196750@i40g2000cwc.googlegroups.com... > [quoted text clipped - 5 lines] > "General" Tab but when i search for it in Contacts, Journals, > User-defined fields etc, i am unable to locate it. Thanks Sue, Will try that & let you know. > 1) IAddrBook > [quoted text clipped - 36 lines] > > "General" Tab but when i search for it in Contacts, Journals, > > User-defined fields etc, i am unable to locate it. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.