Problem trying to email a query in excel format

[cantona]

Hi,

Ive set up a macro to email a query out in excel format. Ive used the SendObject command. When the email is recieved certain information in a particular field is missing. The field in question holds address information. On some rows the address seems to be trimmed. For example, it may display avenue. when it should really display the full street name, eg, church avenue. Other rows appear fine. If i export the query to excel manually by right clicking on the query and choosing excel, it all works ok? What can i do to resolve the problem?

 

[Remou]

Is the particular field a memo field?

 

[cantona]

Hi Remou,

No the field is a text field with a length of 32.

Thanks

 

[Remou]

I can only guess. Does the field contain carriage returns and / or line feeds? Can you send the table on which the query is based without problems? Can you export the query to Excel without problems (Transferspreadsheet)?

 

[Remou]

... Have you installed all the updates for the Jet engine and for Access?

 

[cantona]

Hi Remou,

I get the same results if i send the table rather than the query?! Im not really sure on the updates to be honest. My machine is quite locked down by our IT department. It would be them who would take care of any updates. Im not sure how often this is done, if at all!!

 

[Remou]

In that case, we may need to look at a work-around. Have you tried transferspreadsheet, does that work? Have you looked at the addresses that truncate and those that do not - what is the difference? Length? Carriage returns?

 

[cantona]

Yes, ive had a look at the rogue records and it appears to be happening where the length of the address exceeds 20 characters. 40 King George Square for example becomes Square.Transferspreadsheet seems to work fine. I was just hoping to use SendObject so that i could automatically generate emails with the query attached.

 

[Remou]

That sounds quite strange and more like alignment than truncation. Have you tried making the column in Excel wider?

 

[cantona]

and the prize for the worlds biggest idiot goes out to me!! your right, it was an alignment problem. extending the column did indeed display the full address. Couldnt see the wood for the trees! thank you very much!

 

[Remou]

We all have off days.

 

[LondonJohn]

I find it easier to email queries as text documents. I first change the query view to sql, then copy and paste the text into Word.
The recipient can then paste the text into a new query in sql view and change to the normal access design view.
From a support point of view, it's nice and easy for the user.