Unanswered: Sending Reports via Email Through Access to Multiple Addresses
I have more specifics on this one. I understand this probably can't be done through a macro as their abilities are limited. Well, I've created a command that pulls records from my "Claims" Query and will email the results to the email address in my code.
However, that is fine and all, but depending on what I type in the "Region" part of my query, I would like the report to be emailed to a different address.
For instance, I click "Email Report" and type in "West" for the region, I want the report to get emailed to firstname.lastname@example.org. When I click Email report a second time and type "North" for region, I want the report to get emailed to email@example.com.
Does anyone know the proper coding or basics to get me started on this?
It depend on exactly what you wanted to do, but options include If/Then, Select/Case, a table with the related items, etc. It might help to see your code so far and a better description of the options (just those 2, might they change, etc).
Well my code is pretty basic as of now, with only the send object sending to one specific email. I'm looking to click the button, and after the query prompts me to enter my date ranges, company, and region, depending on what region I enter will determine who it is emailed to.
I believe I already asked for more detail, but I'll take a shot in the dark with this air-code:
Dim strEmailAddy As String
Select Case Me.ComboName
strEmailAddy = "firstname.lastname@example.org"
strEmailAddy = "email@example.com"
The SendObject line would use strEmailAddy for the address argument. You may notice I would also use a form to gather the user input for the query instead of prompts, which will enable you to get this value more easily.
I'd use a region table and hang information like target email addresses off that table. Easier to maintain and much more flexible moving forward. Then you get simple options like using a DLOOKUP or even including email in the data source for a combo box.
Thanks, would there be a way to add some code to the code I have above that will recognize the "region" that I type in the query prompts to send to the different emails depending on what the "REgion" is?
You would have to open a recordset on the query or grab it from the report. The first would likely trigger the prompt again, and the second not applicable to what you're trying to do (it would only be available when the report was open). Using prompts is handy, but doesn't lend itself to a "professional" application. You have a lot more control when you gather user inputs with a form.
By the way, I would probably also use a table as Teddy described.
Presuming you switch to using a form, then as Teddy described you would have a combo that listed the regions and also included the email addresses (they could be hidden). Then in your code you get the email address like:
Right now, more specifically, I have a form called "Claims Tracking System" where all data is entered. That is linked to a table called "Claims Tracking System"
I've created a query called "Email - Weekly Claim Report" and that query is accessed when I click the "Email Report" button and that then prompts me to enter in COMPANY, REGION, and DATE RANGE. Once I enter that data, an email pops up for me to edit and send.
As mentioned I am trying to code and simplify the processes to make reports for different regions be emailed to the approprite email address .
I think I understand what you mean that a query may be the wrong way to go about this.
This being what I currently have going, what is the best suggestion? Thanks!