none
Macro, Access or VSTO to access custom search columns including categories? RRS feed

  • Question

  • While I'm doing my diligence not to use user-defined field mainly for mail merging purposes and similarly to Lexis Nexis Time Matters, I am trying to determine how to best approach exporting a list of selected contact (up to 20000) to an excel spreadsheet.  Firstly I've been told that while I can use the Advanced Search and customize my columns, I cannot save this format and it's all gone once I close the search dialog.  We don't want our users to complain they have to keep doing too many steps everytime they need to perform an export to excel, so I thought I could do this in a macro, but I can't seem to record my keystrokes no matter what I tried.

    Next I'm trying to do in Access a link to Outlook and while I can see most columns, the one I can't see is for "Categories", so I wonder if in my custom form if there's any way I can use say "User Field 3" to automatically calculate as the Categories value so I can at least use in my form and query through access.  If I can't, I suppose I will have to resort to using Visual Studio c# to run a program that exports the selected contacts to excel with the desired columns. 

    Lastly whatever way I am able to accomplish, I will need to add a button on ribbon somewhere to perform this task.

    I'd appreciate any helpful advice here to better determine how I can approach this to satisfaction, thanks.

    Thursday, October 11, 2012 5:49 PM

Answers

  • I'm not sure what you mean by calling an Excel or Access automation program.
     
    You can certainly use code to automate Excel or Access from your addin. I do that all the time. All you need it a reference to either or both and code to launch Excel.Application or Access.Application. From there you just use the object model for whatever program it is.
     
    From an addin you can launch an existing program using a Shell command, but communication with the program won't be there. As far as calling into existing Excel or Access macro code I don't think you can do that, but I'm far from an expert on that sort of thing. I just use the object model I want and develop all my own code.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:f16625f4-be31-45d4-8735-5a202ec26829...
    With a COM AddIn, should I be able to call an Excel or Access automation program?  The less my end-user needs to press the better which is all they care about.

    Ken Slovak MVP - Outlook
    Friday, October 12, 2012 6:31 PM
    Moderator

All replies

  • Why not loop through the contacts (either MAPIFolder.Items collection or the Explorer.Selection collection) and expliiclty read the required properties from each ContactItem object?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.4 is now available!

    Thursday, October 11, 2012 8:05 PM
  • I'm not sure I quite understand as it sounds like performing a macro.  Is it even possible through macro vb to copy/paste to a blank excel spreadsheet or something similar to how access can export a query to excel or VSTO C# using excel automation?
    • Edited by jfalberg Friday, October 12, 2012 1:39 PM forgot few terms
    Friday, October 12, 2012 1:38 PM
  • There is no such thing in Outlook as a macro recorder. You code your macros from scracth in Outlook using VBA code. Macros and the Outlook VBA project are intended for personal use and for prototyping, not for code distribution. It is strongly recommended that you not consider VBA/macros for distribution for Outlook.
     
    The Access linking is limited, is missing some fields, cannot be customized to add the fields, and also can trigger the security prompts.
     
    Outlook does not support handling custom ribbon controls or ribbon button clicks in anything other than a COM addin.
     
    Based on all of that, I would recommend creating and coding an Outlook COM addin as the most supportable solution.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:3edcc676-b4b8-40cb-9810-9318e358f3d1...

    While I'm doing my diligence not to use user-defined field mainly for mail merging purposes and similarly to Lexis Nexis Time Matters, I am trying to determine how to best approach exporting a list of selected contact (up to 20000) to an excel spreadsheet.  Firstly I've been told that while I can use the Advanced Search and customize my columns, I cannot save this format and it's all gone once I close the search dialog.  We don't want our users to complain they have to keep doing too many steps everytime they need to perform an export to excel, so I thought I could do this in a macro, but I can't seem to record my keystrokes no matter what I tried.

    Next I'm trying to do in Access a link to Outlook and while I can see most columns, the one I can't see is for "Categories", so I wonder if in my custom form if there's any way I can use say "User Field 3" to automatically calculate as the Categories value so I can at least use in my form and query through access.  If I can't, I suppose I will have to resort to using Visual Studio c# to run a program that exports the selected contacts to excel with the desired columns. 

    Lastly whatever way I am able to accomplish, I will need to add a button on ribbon somewhere to perform this task.

    I'd appreciate any helpful advice here to better determine how I can approach this to satisfaction, thanks.


    Ken Slovak MVP - Outlook
    Friday, October 12, 2012 3:34 PM
    Moderator
  • You can use Excel automation (or Access automation) to put any Outlook data you want into an Excel sheet or Access table or whatever. You can read Outlook data and put it into any target that supports automation code, in fact.
     
    It's all roll your own, but it's not all that hard to do. I've often used code to put Outlook data into Excel, Access, Word and other programs.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:39ffa227-8cb7-4ffc-8045-de177c17b014...
    I'm not sure I quite understand as it sounds like performing a macro.  Is it even possible through macro vb to copy/paste to a blank excel spreadsheet or something similar to how access can export a query to excel or VSTO C# using excel automation?

    Ken Slovak MVP - Outlook
    Friday, October 12, 2012 3:34 PM
    Moderator
  • With a COM AddIn, should I be able to call an Excel or Access automation program?  The less my end-user needs to press the better which is all they care about.
    Friday, October 12, 2012 6:21 PM
  • I'm not sure what you mean by calling an Excel or Access automation program.
     
    You can certainly use code to automate Excel or Access from your addin. I do that all the time. All you need it a reference to either or both and code to launch Excel.Application or Access.Application. From there you just use the object model for whatever program it is.
     
    From an addin you can launch an existing program using a Shell command, but communication with the program won't be there. As far as calling into existing Excel or Access macro code I don't think you can do that, but I'm far from an expert on that sort of thing. I just use the object model I want and develop all my own code.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:f16625f4-be31-45d4-8735-5a202ec26829...
    With a COM AddIn, should I be able to call an Excel or Access automation program?  The less my end-user needs to press the better which is all they care about.

    Ken Slovak MVP - Outlook
    Friday, October 12, 2012 6:31 PM
    Moderator
  • Are there any 3rd party applications that can better assist me with doing add-ins?  I'm currently looking at add-in express which I see has their office located in Belarus so I'm asking my pre-sales questions by email at least.

    As a newcomer to working with Outlook add-ins, I am frankly overwhelmed with the requirements imposed on me as I recently was told the NavigationPane SolutionModule cannot link with a public folder according to this article: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._solutionsmodule.addsolution.aspx while I have a boss that will not take "no" for an answer and it seems the best I could do is have yet another add-in to have a ribbon that opens up the root of the public folder while still requiring the user to click on the public contact folder.  I foresee I will have yet other requirements that will involve custom reporting and exporting from this special contact and a special tasks folder as well and if there's a way to have one add-in instead of multiples then hopefully I can handle that, and deploy to all the users on this exchange server as well.

    Tuesday, October 16, 2012 1:40 PM
  • Both VSTO and Addin Express are tools that provide templates, COM shim loaders and various designers to assist you on top of the basic shared addin model where you are responsible for shimming, loading, deploying and designing everything yourself on top of the Extensibility framework.
     
    Addin Express is more comprehensive than VSTO and provides more flexibility in terms of the value added designers and controls they provide. I have no hesitation in using Addin Express when the project calls for it. You are however then dependent on their loaders and so on once you commit to a project using Addin Express.
     
    Whether or not your boss takes no for an answer is irrelevant to the real world and how things work with Outlook and Office. I feel sorry for you, but he cannot have anything that can't be done, even if he holds his breath until he turns blue.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:8f8b2096-70ba-4ab2-b9f1-9759c5fa0ef0...

    Are there any 3rd party applications that can better assist me with doing add-ins?  I'm currently looking at add-in express which I see has their office located in Belarus so I'm asking my pre-sales questions by email at least.

    As a newcomer to working with Outlook add-ins, I am frankly overwhelmed with the requirements imposed on me as I recently was told the NavigationPane SolutionModule cannot link with a public folder according to this article: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._solutionsmodule.addsolution.aspx while I have a boss that will not take "no" for an answer and it seems the best I could do is have yet another add-in to have a ribbon that opens up the root of the public folder while still requiring the user to click on the public contact folder.  I foresee I will have yet other requirements that will involve custom reporting and exporting from this special contact and a special tasks folder as well and if there's a way to have one add-in instead of multiples then hopefully I can handle that, and deploy to all the users on this exchange server as well.


    Ken Slovak MVP - Outlook
    Tuesday, October 16, 2012 9:05 PM
    Moderator