Problem emailing query results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've asked for help on this in the Word and Outlook forums as well as here,
but in ove a week I have receive NOT EVEN a response.

I have a report based on a query that very nicely groups my results.

I've recently posted for help on the syntax of my query because it's pretty
complex and I can't get it to work from VBA code.

I hope that if I can get it to work in code then I can use the results to do
an emailing.

I just posted the SQL under the subject title SQL syntax.
 
If you have created a query, you can attach it to an email with this:

DoCmd.SendObject acSendQuery, "MyQuery", acFormatTXT , _
"(e-mail address removed)", , , "Here's your query", _
"See attached blah blah", True

If you need to use a dynamic SQL statement, create one for this job, and
assign it to the SQL property of the QueryDef before calling SendObject,
e.g.:
CurrentDb.QueryDefs("MyQuery").SQL = strSql
 
Cool, thanks. I hope this will work. Right now I'm trying to make the query
work from code, and as soon as I get the syntax right so it'll stop erroring
out, I'll try your suggestion.
 
Allen,

I am trying the SendObject Action, but I need to supply my password...where
in the Action do I do that?
 
OOPS...Never mind about the password question...I figured that out...

I still have the problem of grouping...

Simple case:

Table:
FN
LN
PH

Query:
Cleans MI from FN field and concatenates (e-mail address removed) as Email field

Simply entering two records with same FN and LN but different PHs will
illustrate.

When I SendObject I have to replace "(e-mail address removed)" in the
example below with each instance of (e-mail address removed), and also make sure
that the attached query shows FN LN and the TWO (or more, up to seven)
grouped PHs.

I'd like the body or my emails to be basically:

Dear <<FN>>,

You have the following phone numbers:
PH
PH
PH
PH
(the result of the on LN grouped query)

Please, blah blah

Thanks,
Ray S.
 
In query design, add just the FN and LN fields to the query design grid, and
depress the Total button on the toolbar. This query generates only one
record for each person.

You can now write code that concatenates the various phone numbers, so you
can read this string and put them into the body of the email. That code is
beyond a newsgroup posting, but this link might help:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

The article does assume you have a normalized data structure, which involves
2 tables:
- Person: one record for each person, with a PersonID primary key.
- PersonPhone: one record for each phone number, with a PersonID foreign
key.
 
Thanks Allen,

As it turns out I had just found the link you suggest at the Access
Web...I'm studying the code example from Dev Ashish now.
 
I made sure my data was normalized in the structure you suggested. I also
studied Dev Ashish's sample code. I got it to work with my data. The function
concatenates into one field the telephone data that would be grouped in a
report format, but it requires the user to enter or select a particular
individualized ID on which to return the concatenated values. Do you have any
tip on how I could simply course through the entire recordset of
individualized IDs to return the concatenated values for the entire table. I
could then have the sort of flat view that the mail merge feature can handle.
 
The function takes an argument that is the ID value, but you don't have to
supply it by hand. If you use it in a query, you can pass in the value of
the field in the table.

The query will then generated the concatenated string for each value passed
in.
 
You guys ARE simply the best...This group and your totally invaluable help
have turned me from a reasonably smart but bungling absolute novice in Access
and the use of VBA into a moderately gaining in confidence user.

You're right...of course...and now I do have exactly what I think I need to
be able to do that mass emailing...WOW! Thanks. You can't imagine how useful
and timesaving this little project has been for my company.
 
Back
Top