By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Sending a report via email

P: 14
I have created the tables, queries, forms and the reports, everything works great.
My question is, how do you set up a way to create an email from the report you generated in Access?
BTW - Happy Thanksgiving!!!!!!!
Nov 22 '06 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I have created the tables, queries, forms and the reports, everything works great.
My question is, how do you set up a way to create an email from the report you generated in Access?
BTW - Happy Thanksgiving!!!!!!!
Hi Richard,

The syntax is:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.SendObject acSendReport, "ReportName", acFormatRTF, "ToEmailAddress", "ccEmailAddress", , "Subject", "Message"
  3.  
  4.  
This will send the report in an rtf format if you are using MS Outlook or Outlook Express. There is no format for Access reports so you have to decide the format, e.g. Rich Text File, snapshot, etc. If you are not using MS Outlook or Outlook Express it's more complicated.

Mary
Nov 22 '06 #2

P: 14
Hey Mary...
Thanks for the code. But is there a way to actually add the report to the body of the email instead of adding it as an attachment?
Nov 22 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey Mary...
Thanks for the code. But is there a way to actually add the report to the body of the email instead of adding it as an attachment?
Not that I'm aware of, but someone else may know of something.

Mary
Nov 22 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Not that I'm aware of, but someone else may know of something.

Mary
Just thinking ...

You could in theory, pass the report data to a string for the message field but formatting, etc. would be problematic.

Mary
Nov 22 '06 #5

P: 14
Mary,
In the meantime, can you help with this one.

Is there a way to add all the data that the query created and store it into a table without using the "Create-Table" function.
I created the query which pulls information from different tables (up to 4 different tables).
I also created a new table which includes the same fields as to what i used for the query plus additional fields like "email sent". How can I get the info from the query to be added to the new table?
Nov 22 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary,
In the meantime, can you help with this one.

Is there a way to add all the data that the query created and store it into a table without using the "Create-Table" function.
I created the query which pulls information from different tables (up to 4 different tables).
I also created a new table which includes the same fields as to what i used for the query plus additional fields like "email sent". How can I get the info from the query to be added to the new table?
What you are looking for is an Append rather than a Make Table query.

In sql:

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO NewTableName (Field1, Field2, Field2) SELECT Field1, Field2, Field3 FROM QueryName;
  3.  
Nov 22 '06 #7

P: 14
Where do I add it to?
I put it at the end and i get an error, then I put it after the DCount statement and i still get an error.

Heres the sql info

SELECT CMN_PEOPLE.FULL_NAME, CMN_PEOPLE.PERSON_IDENTIFIER01, [Required Classes].[Course Name], [Required Classes].[Course ID], SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID, SSS_STUDENT_GRADES.GRADE_TYPE, DCount("[Course Name]","qry_Required_Courses-Students_with_low_grades","[PERSON_IDENTIFIER01]='" & [PERSON_IDENTIFIER01] & "'") AS CourseCount
FROM (([Required Classes] INNER JOIN (SSS_STUDENT_ENROLLMENTS INNER JOIN SSS_SECTIONS ON SSS_STUDENT_ENROLLMENTS.SECTION_ID = SSS_SECTIONS.SECTION_ID) ON [Required Classes].[Course ID] = SSS_SECTIONS.COURSE_NAME) INNER JOIN CMN_PEOPLE ON SSS_STUDENT_ENROLLMENTS.PERSON_ID = CMN_PEOPLE.PERSON_ID) INNER JOIN SSS_STUDENT_GRADES ON SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID = SSS_STUDENT_GRADES.ENROLLMENT_ID
WHERE (((SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID)<72) AND ((SSS_STUDENT_GRADES.GRADE_TYPE)="Final") AND (([Required Classes].[Required or Elective])="Required"));
Nov 22 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Something like this. I'm assuming the table names for the new table.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO (FULL_NAME, PERSON_IDENTIFIER01, [Course Name], [Course ID], GRADE_WEIGHTS_ID, GRADE_TYPE, CourseCount) 
  2. SELECT CMN_PEOPLE.FULL_NAME, CMN_PEOPLE.PERSON_IDENTIFIER01, [Required Classes].[Course Name], [Required Classes].[Course ID], SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID, SSS_STUDENT_GRADES.GRADE_TYPE, DCount("[Course Name]","qry_Required_Courses-Students_with_low_grades","[PERSON_IDENTIFIER01]='" & [PERSON_IDENTIFIER01] & "'") AS CourseCount
  3. FROM (([Required Classes] INNER JOIN (SSS_STUDENT_ENROLLMENTS INNER JOIN SSS_SECTIONS ON SSS_STUDENT_ENROLLMENTS.SECTION_ID = SSS_SECTIONS.SECTION_ID) ON [Required Classes].[Course ID] = SSS_SECTIONS.COURSE_NAME) INNER JOIN CMN_PEOPLE ON SSS_STUDENT_ENROLLMENTS.PERSON_ID = CMN_PEOPLE.PERSON_ID) INNER JOIN SSS_STUDENT_GRADES ON SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID = SSS_STUDENT_GRADES.ENROLLMENT_ID
  4. WHERE (((SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID)<72) AND ((SSS_STUDENT_GRADES.GRADE_TYPE)="Final") AND (([Required Classes].[Required or Elective])="Required"));
Nov 23 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
Another, perhaps easier way for you to work with this is :
1. Create a new query in design mode.
2. Switch to SQL view.
3. Paste in your original SQL (the same as the source of your report).
4. Convert to design view again.
5. Change query type to Append Query (enter new table details etc).

You now have a query you can save as a QueryDef (saved Access query) or get the new SQL from (SQL View again).
Nov 23 '06 #10

PEB
Expert 100+
P: 1,418
PEB
Just thinking ...

You could in theory, pass the report data to a string for the message field but formatting, etc. would be problematic.

Mary
About the body of the report if he can get to format it like html? The procedure will be very long and complicated...

or maybe...

if the report is saved as image...

And this image attached.. sometimes the simply images the most of mails open them... if there is html mail with link to the image!

Only idea!
Nov 27 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.