Using "Create Email" Function

[jerryh]

I'm trying to set up the "Create Email" function to send out a form (can be either html or InfoPath) to update information within the database. I don't need to send the entire database to the manager, I only want to send a handful; however, Access generates an email with all of the manager's records. Is there a way to select only the records I want updated instead of all the records? I've tried doing it through a Query, but it still sends out all of the records.

[ItsMe]

Perhaps you should include a WHERE clause within your Query.

[jerryh]

The WHERE clause causes the Query to become Read-Only...

[June7]

That's odd.

Post the SQL statement.

[jerryh]

I figured out what was making it "Read-Only" and corrected that; however, I'm still getting all records in the Create Email instead of just the few I want. Here is the SQL statement for my Query, which gives me what I want, it's just giving me the same results in the generated email form.

SELECT [All Vendors].[ID], [All Vendors].[Vendor], [All Vendors].[Service Provided / Usage / Contract], [All Vendors].[Department], [All Vendors].[Max Allow Vendor Downtime], [All Vendors].[Financial Impact to Bank], [All Vendors].[Amt / Type of Sensitive Info], [All Vendors].[Next Review Month], [All Vendors].[Responses], [All Vendors].[Email]
FROM [All Vendors]
WHERE ((([All Vendors].[Next Review Month])="01 - Jan"));

[June7]

Did you mean it is NOT giving the same results in the generated email form?

What process are you using? I just tested the E-mail option on the Export ribbon tab and it worked. I opened a filtered query object then selected the E-mail export.

[jerryh]

Correct, it's not giving me the same results. It's as if the Create Email is ignoring the WHERE Clause.

[June7]

Again, what process are you using? I could not replicate the issue.

[jerryh]

Sorry about that...

For the Query:
Create>Query Wizard>Simple Query Wizard
I then go through the steps to pick out the table (only using one table), and fields I want
I then go into design mode, and under the field labeled "Next Review Month" I type in "01 - Jan" (as I only want the items due in January).
I hit "Run" and it gives me all the records I want, filtered by "Next Review Month"

For Create Email:
Right Click on the Query in the Objects pane and click on Collect and Update Data via Email
Select HTML
Update Existing Information
I select all fields available
"Automatically Process Replies" is not checked
Under "The Current Table or query" I select "Email"
Then just hit "Next" until I get to the list of email addresses and pick the one I want and click "Send"

I check my Sent box in Outlook pull up the email that was sent out; however, instead of just the records that were pulled and filtered in the query, it's sending all records from the original table.

[June7]

Okay, using Collect Data.

I tried those steps and no records are sent in the E-mail, just blank boxes.

[jerryh]

Maybe I'm going about it wrong... My table has information on our vendors. We have a LOT of vendors, and each vendor is "owned" by the department manager who initiated the contract with that vendor. Periodically I need to ask each manager if their vendors are still active and if there are any updates. I've been using Excel, but with the evolving compliance required with vendor management, I moved to Access to try to pull things together and create meaningful reports, etc... Seeing how I don't want to inundate managers with a long list of vendors to review all at once, I break them out throughout the year. If I could have Access generate an email to the applicable manager asking them to update their vendor's information and then upload it back up to Access so I don't have to cut and paste or manually type them in is my goal.

I hope I'm making sense, and if there's an easier way to do this, I'm all ears!

Thanks!

[June7]

I've never used this utility, only tried to help forum members. Another thread actively working with https://www.accessforums.net/access/...ail-47977.html

As far as I know, Collect Data is supposed to allow the collection of new and edited data.

At this point, I can't even replicate transmitting records with the email. The Create E-mail option isn't available unless I have a table or query open or click on in Navigation pane.

[jerryh]

OK, I figured it out... I was reading some info on Lookup fields and how it's not advised to use them. So, I removed that field and set it up as a drop down field instead and removed the relationships that I didn't really need and voila!