emailing the output of a query

[TechieJr]

Hello All,

I am trying to figure out a way to e-mail the results of a daily status query in text format as the body of the message. I can do it by creating a report based on the query and emailing the report as an attachment. However, no matter what format the report is emailed in (text, html, snp, etc.) it goes as an attachment.

The text format is needed as some recipients will be getting it as a text message on their blackberry. Others only have Access 2000 which does not support the snapshot (snp) format.

I know you can export the result of a query to a text file. I've considered that but I'm not sure how to do it in vba code, nor do I know how to import the resulting text file as the body of an email.

Anyone have any thoughts?

Thanks in advance.
TechieJr.

 

[dRahme]

Hi, I do not know how to do exactly what you want, but if your status query is relatively small in terms of rows and columns you can use a number of dlookups to obtain the data and html formatting to give an attractive look.

something like:

dim Item1, Item2, Item3, Item4 as string

item1 = dlookup("[item1]","statusQuery","[id] = 1")'Id being the row identifier and item1 being the field.

..and so on.

Hope that Helps.

 

[TechieJr]

Hi dRahme,

Thanks for the reply. I need to stay away from html because of the text messaging capability it needs. Any way of doing this so it becomes the body of an email in text-only format?

As far as the size goes, the data is 4 columns wide and anywhere from 5 to about 12 rows (this varies). How would your suggestion work with a varying number of rows?

Hope this helps.

 

[buddafish]

i did this a few years back.

1st: run the report and export it as a .rtf file to a local directory.

2nd: hook exchange using the mapi control. set the parmeters as needed.

3rd: then using the .AttachmentPathName property, attach the needed report in .rtf format.

this worked really well. i also had a form that would load the exchange recipients, then you can select who you want to send it to.

if you need some code examples, let me know. if you are not up on mapi programming, check it out. back then, i used a book called cdo & mapi programming with visual basic.

take note: today's service pack and security fixes can (and probably will) present challenges when attempting to manipulate outlook and exchange. there are alot of workarounds published on the web.

search for "outlook redemption objects"

best of luck

scottie

 

[TechieJr]

Hi buddafish/scottie,

I've never used mapi programming before but I'm game to jump into it. And yes, the security aspects are providing some extra hurdles to jump over. Thanks for the suggestions on overcoming them.

If I understand your suggestion, I would be attaching an rtf document to the email instead of including the query results as a text part of the body of the email. Am I reading this correctly?

Access 2003 allows this to be done natively. It allows me to do create a report and save it as an rtf, html, xls, snp, txt, and a couple of other file types, but then send it only as an attachment, never as the body of an email message.

Let me know if I'm mistaken about how your suggestion works, but the word is that I need to send the query results as the body of a text-only e-mail.

Thanks for the suggestions. I'll investigate them a bit more.

TechieJr.

 

[buddafish]

TechieJr,

i believe you are correct about access sending only as an attachment. this is why you would use MAPI. one of the properties of a MAPI Message is the .NoteText = "the body of the message". as well as all the other properties you will need. Subject, Recipient, Address, ect.

sooo... my approach would be to generate the body of the message as a string variable, then create a MAPI Session, create a MAPI Message within that session, fill in the needed info (recip address, display name, note text, ect) then send.

once you get into the syntax of MAPI, it is pretty straight forward. fun too. CDO has replaced MAPI, but you should still be able to work with the MAPI contols. (easier to work with ..)

good luck

scottie