Merging from Outlook Categories (2000 SR-1, Win 2000 Prof)

Topic

New Reply

Been banging my head on this and maybe it’s not possible *but*

I have designed a custom form to a public Outlook folder and ported info from 4 different folders to this new form. Now I need to generate rosters and other things from the folder. Here’s the problem: i have 254 custom categories (1 for each county in Texas) and 179 contacts in the folder. Many contacts are assigned to more than one category. When I merge and use “Categories” as merge field, I always get the multi-county assignments, as applicable. Quite often the *same* record merges in, once for each County/Category assigned.

Now, the frustrating thing is I can go to Views and group my info to create a nice-looking table view with contacts listed by each seperate Category. Also, when going to View | By Category, Outlook displays the info on a single county basis, and if someone is assigned more than one County they appear in each individual County/Category field. This is what I want to display in a merge document, but what I get when grabbing Categories in a mail merge are the ‘combined’ Categories list for each record.

So, somehow Outlook is able to list things the way i want in a the View by Cartegries or a grouped listing, but any contact assigned multiple Categories is listed with all categories assigned in a mail merge with Categories selected. I’m thinking I need to do a little programming to pull the actual information needed from Outlook to Word and that’s not a big deal but what Outlook Reference, etc. is needed?

I’d like to avoid going thru a cut/paste to excel or a link to Access –> Word if possible.

Thanks!

Reply | Quote

Replies

You’re right that a straight merge won’t do what you want. I think code to do this would be pretty nasty because the categories field is just a long string field with delimited items stored there. If this is a process you need to repeat frequently it might be worth it; otherwise an alternative might work.

You could modify your categories view to show the columns you are interested in. Choose Edit | Select All, then copy. Paste the contents into Word or Excel. You’ll want to paste special in word, otherwise you’ll get signature card files.

Reply | Quote

well after receiving your (admittedly) depressing response I went exploring in the outlook object model and, well, nada. if you install Outlook Spy from this site (http://www.dimastr.com/outspy/) you get a lot of interesting info including a reference to something called ‘Keywords’ on an opened contact list that, in the Outlook Spy sw at least, loads the contegory string in a drop-down box with the proper one selected for each Categories group (the Categories title on the Group By bar in View by | Categories.) which is as close as I have gotten to seeing how Outlook parses out the Categories string for each record. Also, in vb for Outlook Help the Keywords field is mapped to the Category field (not Categories – singular).

However, adding a custom textbox to my form and tying it to the Keywords field gives me the whole list of categories. changing it to anything other than Value (well I haven’t tried all the options) doesn’t display anything. Adding a drop-down box and assigning it to Keywords likewise doesn’t work. Doing similar things with the Category field also displays no data. I suppose i should just give up on it, as, even if i managed somehow to ferret out the right combination of controls and links, there’s no guarantee *at all* that the new field will show up in a mail merge — as a matter of fact, it probably won’t as the only way to expose new fields to Word in a mail merge is to create User-defined fields and these fields I am trying to access are pre-defined and pretty well hidden.

However, I don’t want to cut-and-paste as when you do this from table format you’re not getting anything much different from a full Mail Merge from Outlook and I can get more control and automation by using the Outloook mail merge feature. I might be able to put something together in word that can wap out the records per each comma-seperated value in the Categories field, but that’s asking more than one can expect from a word-processor. I am working instead on classing this problem as a software limitation and disabuse the boss of the whole idea of getting the report requested. **Sigh**

Reply | Quote

Yet another instance showing how Outlook has yet to grow into its enterprise shoes.

It may be that Crystal Reports can access the data you need; it partly depends on whether the data is stored in the folder or the forms. I know it will format output the way you need.

Otherwise, I think you’re looking at VB automation. You’d have to build an array of categories and use it in conjuction with a series of restricts while iterating through the items in the folder. That’s not too tough. But I’m not sure how best to build the array. I should think the master list would be easiest, but I can’t find documentation of it in the object model. Anybody out there have anything on this?

Reply | Quote

There’s a couple of things I can try on the automation side of things. one would be a SQL query that builds a recordset using a look-up table of county names and the LIKE operator to write the records out (LIKE can find matches in a string if you use the wildcard character) – but I haven’t figured out how to write the module in Access to do this yet. Access queries don’t support LIKE from the query grid and I’ll need to build a module that creates a recordset based on two existing tables. doesn’t sound too difficult, but I was trying to find a solution just between Outlook and Word (Excel would be ideal, btw). I’d also like to make whatever method something easy for other people to do, and porting data from Outlook to Access is a pain.

It is easy to get the data I need from an Exchange Public folder into Word and then paste the data into Excel for ‘washing’. so, I am planning on writing a macro in Excel that copies out a new record for each county. This should work for a number of reasons: it’s easy to show someone how to merge from Outlook to Word to a template and show them how to pop the data into Excel and, when ready, click on the Macro button/command to do everything else.

Outlook seems to like giving me a duplicate record for every contact with more than one county listed, but even here it is inconsistent — doesn’t always happen — so is not suitable for an auto-run macro until I can find out why some records don’t behave this way. I’d prefer having the dups as that makes it really easy to compile the by-contact information, since you’ll just have to delete and move county cells, not the rest of the contact info. well there’s a little progress at least…

I tried to find a reference to the Mater Category list as well, thinking it would be a good place to start an array-type operation but didn’t find anything applicable in the Outlook model so far. I can’t even find the event trigger on the Categories… button that brings up the Categories list.

Reply | Quote

Storing the counties in Excel or Access is a nice work around. btw – Access does support like (ex: “Smith*” “*Smith” or “*Smith*”) though SQL itself uses % as the mask.

A sketch of the code to drive Access from Outlook to get the master list might look like this if you use a DSN:

Set objADOConn = CreateObject(“ADODB.Connection”)
objADOConn.Open “DSN=TXCounties” ‘ the dsn name
strSQL = “SELECT COUNTYNAME FROM COUNTIES ORDER BY COUNTYNAME”
set objRSCounties = Createobject(“ADODB.Recordset”)
objRSCounties .Open strSQL, objADOConn
If Not objRSCounties.EOF Then
‘
‘ Loop the recordset and build the array
‘
End If

Hope this helps!

Reply | Quote

On the whole an Access-based solution is probably the way to go. there’s several filters I’m going to need and anyhow Access is used to support a web-based search function for the same contact information. My only problem with Access, and this is really silly, but I am puzzled by how to set up a module to loop thru the contacts list and generate a table that lists each contact by LIKE county match from another table. on the web search I have this:

SQLstring = “SELECT * FROM LocalCRCGContacts where Categories LIKE ‘%” & County & “%’ ;”

Where County is input from a drop-down. Of course, to do this on the web one has to set up a db connection (I use DNS-less) to display records. However, we’re talking about a table-to-table comparison in Access, so it would seem a connection doesn’t need to be made. The query grid doesn’t support LIKE in the joins properties, which is why I think it’ll have to be a module (or one of the more complex SQL queries) to wap out the records. In the SQL window in Access I have this:

SELECT * FROM LocalCRCGContacts WHERE LocalCRCGContacts.Categories LIKE ‘%” & CNTYCODE.County Name & “%’;

which gives me no records but no syntax error either. It looks alright in the main but there’s no looping going on, so perhaps that is why nothing results from it.

How, then, to compare the two tables and generate a new list by iterating thru one table against another table field? this should be easy but i’ve tried before and got stuck.

Reply | Quote