SendObject with parameters

[Guest]

I'm trying to send an Access query to a bunch of email addresses, with each address receiving a different subset of the data.

I've been trying to do this from code using the SendObject method, but I can't find a way around hand-entering the parameters for each message that's sent. Anybody know of a way to feed the parameters to the query in a way that SendObject can deal with?

Or, for that matter, something completely different I should try?

Thanks!

Dave S.

 

[Guest]

Howdy

Yeah, I could do either to get the parameters into the query, but I'm still stuck with how to call the query with SendObject.

One consideration is that the SQL for my real query is quite long (it's a big union query), so it'd be a pain to write in code -- I'd prefer to just call the query from the database.

But even if I do hard-code the SQL, it seems that all SendObject can manage is to call a permanent query that has a name in the database without changing it at all. I may be able to use CreateQueryDef to create a permanent query named "Temp" every time I want to change a parameter, but this seems very stone-age and I'm not even sure it'll work.

This thread's getting pretty long/old, but is there anybody who knows some other way to send large chunks of Access data via email? Some Outlook properties or something?

Thanks for the help,

David S.

hi
Did u try storing the vale of the parameters in a global variable?
Or u can give the parameters a value by using a form:
Putting Form![name of the form]![name of the textbox] in the query criteria

The comma for the text as the # for the date

Ex 1:
Dim Client_name as string

“select [something] from

where [client name]=’†& client_name & “’;â€


ex 2:
Dim client_no as long

“select [something] from

where [client name]=†& client_no & “;â€

Yeah, that's more or less what I wanted to do with the query, but I'm running into trouble when I get to the actual sending part.

I think the hitch is that the SendObject method seems to need the name of a permanent query object in the database instead of a RecordSet that I've opened up and fed parameters to in code. No problem getting the query to run for each email, but I can't manage to actually send the things.

The code line I'm trying goes something like this (air-code-reproduction):

DoCmd.SendObject objecttype:=acSendQuery, objectname:=myQuery, outputformat:=acformatxls, to:=myEmailAddress

As long as myQuery is a query with a name in the database, this works fine. But if myQuery has a parameter, it'll prompt me for it every time. And I can't figure out how to get objectname to refer to a recordset that I've opened along with the InputParameters property.

Incidentally (and straight off the topic), why do you need the single quotes?

Thanks for the help,

Dave S.

 

[Guest]

Hey Tim,

Yeah, that makes sense -- I guess declaring the variable on the Global level makes it available to the email program -- but still, the problem I'm having is not getting the parameter into the query. I now have about six different ways of doing that (thanks in part to the very good ideas in this thread)

The problem is getting the email program (in this case, Outlook) to send the programatically-manipulated query as an attachment. I've been trying to do this from Access with the SendObject method, but as that method doesn't seem to like programatically adjusted objects, maybe I should check out some Outlook code instead?

Do we know any other ways of emailing queries as attachments? Might be time to try a post on the Outlook board here...

Thanks for all the help / sharing my frustration!

Cheers

Dave S.