Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Sending Reports via Email Through Access to Multiple Addresses

    Hi All,

    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 west@west.com. When I click Email report a second time and type "North" for region, I want the report to get emailed to north@north.com.

    Does anyone know the proper coding or basics to get me started on this?

    Thanks for any help!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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).
    Paul

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    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.

    Region = WEST would be to west@west.com for example and so on.
    Region = North would be to north@north.com

    I don't think this can be done with a macro and a condition.

    If this is too general please let me know. Perhaps I can give more detail.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I believe I already asked for more detail, but I'll take a shot in the dark with this air-code:

    Code:
    Dim strEmailAddy As String
    
    Select Case Me.ComboName
      Case "West" 
        strEmailAddy = "west@west.com"
      Case "North" 
        strEmailAddy = "north@north.com"
    End Select
    
    DoCmd.SendObject...
    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.
    Paul

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2007
    Posts
    214

    Question

    This all makes sense so far, but I guess my question would be, since when I click the "send email" button I am prompted to enter in "Date Range", "Employer" and "Region"...

    Well that is all fine, however, depending on what region the user types in will determine where the final report is emailed.

    How would I code this accordingly? Is there a way to code this that will recognize the user typed "east", "west", and "north" into the query then send to the appropriate email accordingly?

    below is the simple code below that just creates the report and email. I'd also like to have the report mailed as excel.

    Thanks for all your help!

    Private Sub Command48_Click()
    On Error GoTo Err_Command48_Click

    Dim stDocName As String

    stDocName = "E-Mail Claim Detail by Disposition Regional"
    DoCmd.SendObject acReport, stDocName

    Exit_Command48_Click:
    Exit Sub

    Err_Command48_Click:
    MsgBox Err.Description
    Resume Exit_Command48_Click

    End Sub

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by pbaldy View Post
    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.
    You should also look at SendObject in VBA help to see the other options available for it.
    Paul

  8. #8
    Join Date
    Oct 2007
    Posts
    214
    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?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  10. #10
    Join Date
    Oct 2007
    Posts
    214
    This makes sense. I do like the idea of a table better. So basically make a table that houses the emails and regions? And write additonal code to the above to pull the emails from the table?

    I'm still learning quite a lot about vb and access I've accomplised a lot but still learning the details. Thanks!

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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:

    strEmailAddressVariable = Me.ComboName.Column(1)

    presuming the address was in the second column.
    Paul

  12. #12
    Join Date
    Oct 2007
    Posts
    214
    If I throw a copy of my front end database setup out there, would you mind taking a look? I can easily do this. If you'd like compensated for your time I can do a paypall donation

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you post it here, one of us will take a look at it. Have you tried using a form? It's pretty easy. Create a form with the necessary textboxes on it. Instead of a bracketed criteria in your query:

    [Enter region]

    you'd have

    Forms!FormName.ComboOrTextboxName
    Paul

  14. #14
    Join Date
    Oct 2007
    Posts
    214
    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!

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not saying a query is the wrong way, just the method the query uses to get the user input.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •