Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    24

    Answered: Email reports to specific groups

    Hello,

    I have a database in which I enter various data, in the data is a category drop down. After I enter the data I would like to email all the data to a specific email based on the category. So if I put in 10 records, and 5 are categorized as "Tech" and 5 "Admin" I want the 5 tech records sent to the email addresses I have listed for owners of the Tech category, and the 5 admin to the admin owners. I have it hard coded now to do so, but this requires building a separate report for each category and a lot of vba code. I am just starting on the database and only have those 2 options in it, but in thinking ahead, I will be adding more categories. I would like to simplify this, and have my code look at my query, see which ones are labeled whatever category, and then email those to the corresponding email of that category that I have stored in the Category table. I am having a hard time figuring out the syntax to get this done and any help would be greatly appreciated. Here is the code I am currently using.

    Code:
    Private Sub cmdSendEmail_Click()
       Dim rsData As Recordset
    
       Dim sAddr As String, sSubj As String, sFor As String
       Dim sAddr2 As String
       Dim sAddr3 As String
       Dim aAddr As String
       Dim aAddr2 As String
       Dim aAddr3 As String
       If IsNull(lstReports) Then Exit Sub
        
        Set rsData = CurrentDb.OpenRecordset("qur_CI")
    
        sAddr = DLookup("[Owner Email]", "Qur_CI", "Category = 'Tech'")
        If IsNull(DLookup("[Alt Email]", "Qur_CI", "Category = 'Tech'")) Then aAddr2 = "" Else sAddr2 = DLookup("[Alt Email]", "Qur_CI", "Category = 'Tech'")
        If IsNull(DLookup("[Alt2 Email]", "Qur_CI", "Category = 'Tech'")) Then sAddr3 = "" Else sAddr3 = DLookup("[Alt2 Email]", "Qur_CI", "Category = 'Tech'")
        sSubj = "New CI Ideas"
        sFor = Left(sAddr, InStr(1, sAddr, "@") - 1)
    
            
            DoCmd.SendObject acSendReport, "rptTech", acFormatXLS, _
                                        sAddr & ";" & sAddr2 & ";" & aAddr3, , , sSubj, "Attached is a copy of the latest CI Idea(s) for your Category "
            DoEvents
    
        aAddr = DLookup("[Owner Email]", "Qur_CI", "Category = 'admin'")
        If IsNull(DLookup("[Alt Email]", "Qur_CI", "Category = 'admin'")) Then aAddr2 = "" Else aAddr2 = DLookup("[Alt Email]", "Qur_CI", "Category = 'admin'")
        If IsNull(DLookup("[Alt2 Email]", "Qur_CI", "Category = 'admin'")) Then aAddr3 = "" Else aAddr3 = DLookup("[Alt2 Email]", "Qur_CI", "Category = 'admin'")
        sSubj = "New CI Ideas"
        sFor = Left(aAddr, InStr(1, aAddr, "@") - 1)
    
            
            DoCmd.SendObject acSendReport, "rptAdmin", acFormatXLS, _
                                        aAddr & ";" & aAddr2 & ";" & aAddr3, , , sSubj, "Attached is a copy of the latest CI Idea(s) for your Category "
            DoEvents
            
    
    End Sub
    I realized I need some error checking for when the query does not pull any data for the category, but hoping to get this into one vba string instead of the 2 (or more in the future) that just looks at what the query does pull and email based on that.

    Also this is just a bonus and not important, I am using IBM Lotus notes for my email (switching to Outlook soon, but not sure the timeline) this code opens the email and I have to click send, not a big deal, but if anyone know how to tell lotus note, and outlook to just send it, that would be awesome as well.

    Thanks in advance for any assistance. I should add that I am using Access 2007
    Last edited by tharless; 01-22-16 at 10:22. Reason: Should mention this is access 2007

  2. Best Answer
    Posted by ranman256

    "in a form, put a list box, and combo box.
    The combo box would have the groups, TECH, ADMIN..
    afterupdate the list box would fill with the query in the combo box
    lstEmails.rowsource = cboGrp

    a combo/list box would have the 'report' to send.
    now click a button to scan the list and send the email.

    Code:
    '------------
    Public Sub ScanAndEmail()
    '------------
    Dim vTo, vSubj, vBody, vRpt
    Dim vFilePath
    dim i as integer
    
    vRpt = lstRpt
    vBody = "body of email"
    vSubj = "subject"
    
         'scan the list box
    For i = 0 To lstEAddrs.ListCount - 1
       vTo = lstEAddrs.Column(2)
         
       DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
    Next
    End Sub
    "


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    in a form, put a list box, and combo box.
    The combo box would have the groups, TECH, ADMIN..
    afterupdate the list box would fill with the query in the combo box
    lstEmails.rowsource = cboGrp

    a combo/list box would have the 'report' to send.
    now click a button to scan the list and send the email.

    Code:
    '------------
    Public Sub ScanAndEmail()
    '------------
    Dim vTo, vSubj, vBody, vRpt
    Dim vFilePath
    dim i as integer
    
    vRpt = lstRpt
    vBody = "body of email"
    vSubj = "subject"
    
         'scan the list box
    For i = 0 To lstEAddrs.ListCount - 1
       vTo = lstEAddrs.Column(2)
         
       DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
    Next
    End Sub

  4. #3
    Join Date
    Apr 2011
    Posts
    24
    Sorry, I got pulled off the project for a bit to work on something else. You will have to forgive me sometimes I am dense , so what you are suggesting, is a combo box that the control source is a query that pulls all the available categories, and then my query that pulls the emails to be sent would populate the list box based on a selection made in the combo box, and then the button would send those available reports? So I would choose admin in the combo box, my list box would fill with all available admin records, and then I would click the button to send those? is that what you are getting at?

  5. #4
    Join Date
    Apr 2011
    Posts
    24
    OK, so thanks to ranman256 getting me headed in the right direction, I was able to get it done. Putting my solution out there incase it helps someone else.

    I created a combo box that pulled my categories base on a query that was set to filter on a false yes/no box. Told my vba to select the first item in the combo, created a query that filtered based on the selection in that combo box and false yes/no box. Emailed the query to all listed email addresses associated with the records, which my query pulled from my category owner table. Used Dlookup to pull the email addresses from the query. Used an update query to change the yes/no box to true, this took it out of my combo box, and off my query. Looped until the combo box was cleared out and closed the form.

    Code:
    Private Sub Command14_Click()
       
       Dim sAddr As String, sSubj As String, sFor As String
       Dim sAddr2 As String
       Dim sAddr3 As String
      
       
       On Error GoTo errhandler:
       Do
       DoCmd.SetWarnings False
       Refresh
       Me.cboGrp = Me.cboGrp.ItemData(0)
       Refresh
       
       sAddr = DLookup("[Owner Email]", "qur_CI")
    
        If DLookup("[Alt Email]", "qur_CI") = "" Then sAddr2 = "" Else sAddr2 = DLookup("[Alt Email]", "qur_CI")
       If DLookup("[Alt2 Email]", "qur_CI") = "" Then sAddr3 = "" Else sAddr3 = DLookup("[Alt2 Email]", "qur_CI")
       sSubj = "New CI Ideas"
       
       DoCmd.SendObject acSendQuery, "qur_CI", acFormatXLS, _
                                         sAddr & ";" & sAddr2 & ";" & sAddr3, , , sSubj, "Attached is a copy of the latest CI Idea(s) for your Category "
    
       DoCmd.OpenQuery "updateemail"
       DoCmd.Close acQuery, "updateemail"
    
       DoCmd.SetWarnings True
       Refresh
        Loop Until DLookup("[Category]", "qur_CI") = ""
        
       
    errhandler:
        Exit Sub
        
      DoCmd.Close acForm, "frmEmail"
    End Sub
    I am most certainly a code novice so if anyone sees any issues that could arise from this please code please feel free to point it out. Also as of now, it only opens a new email for each category in my lotus note email, not a big deal I just have to click send on around 8 different emails right now, but if anyone knows how to make it open and send you would be my hero

Posting Permissions

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