Automatically email a from based on query

[timmyjc18]

Good afternoon



I am trying to have a report that is automatically created when a button is pressed to be emailed to a distribution list that will be based on a column in another form. I am not able to upload the database because it has a bit of sensitive info in it. In other words, I have a form that has user names, user ID's and passwords and a certain codes that is specific to different cities around the country. What I am trying to do is to create button that will do the following: create a report that has all of the usernames and passwords for a single city and then send it out via email to a distribution list. Can this be done?

[Monterey_Manzer]

Good Afternoon Timmy,

This is actually fairly easy. My first question is how would you like this report to be formatted?

You could export the actual Report object using the DoCmd.OutputTo command, maybe include a little code for format it so it looks pretty(most of my users like Excel spreadsheets so that's where my experience lies), then use Outlook automation code (http://support.microsoft.com/kb/161088) to generate the email you want. In the Outlook code you can specify files to attach to the email so that would be how you would include the report. Just use the full file name, including path, in .Attachments property of the Outlook message.

On the other hand, if you just want this list in the body of the email that is easily done too. You could just create a DAO recordset and loop through it to create a list and then use the outlook automation code I referenced the create the email and use a couple string variables to include the list in the text of the email. Here's a quick sketch of what I think that would look like:

Dim rst as DAO.Recordset, strSQL as String, emailText as String, distList as String, City as String

City = Me.lblCity.Caption ' Determining which city to use in filtering the recordset

'  Selecting the userNames and Passwords for users in the city determined above
strSQL = "SELECT myTable.userName,myTable.Password FROM myTable WHERE myTable.City = '" & City & "';"
' Get the records
Set rst = CurrentDb.OpenRecordset(strSQL)

' Starting off our email text variable
emailText = "Below are the userNames and Passwords for " & City & ":" & vbCr & vbCr

'Collecting the list of userNames and passwords
Do Until rst.EOF
   emailText = emailText & rst![userName] & Chr(9) & rst![Password] & vbCr
   rst.MoveNext
Loop

Then, when you use the Outlook automation code, you set the Body property equal to your emailText variable and include any explanatory text you want. I tend to write tons of comments in my own code so that when I finally get back to looking at it I can figure out what I was thinking at the time. I hope that helps!

Monterey_Manzer

[timmyjc18]

I kind of understand this. Here is a follow up question, each city has its own distribution list, how can I have the email that is created to be able to send to that city DL based on the city name? And, all this code, I place it in the ON CLICK event inside the button?

[Monterey_Manzer]

Not a problem, I do this kind of stuff fairly often.

If you were to take the DAO.Recordset approach I would simply split up the code. For readability and modularity, I like to write relatively basic functions and public subroutines in modules and then call them from my events along with the necessary specifications. If you take the code I referenced above, I would just include another DAO.Recordset that selects the email addresses of the people on the distribution list (another SQL statement should take care of that) and then add them to a String variable (I included distList in my code above but forgot to use it, sorry) and pass that in to your email subroutine as the email recipients. Something kinda like this (assuming this is added on to the code I first posted):

strSQL2 = "SELECT emailAddress FROM DistributionTable WHERE DistributionCity = '" & City & "';"

rst2 = CurrentDb.OpenRecordset(strSQL2)

Do Until rst.EOF
     distList = distList & ";" & rst![emailAddress]
     rst.MoveNext
Loop

'  I like to remove the first semicolon the above loop will generate
distList = = Right(distList, Len(distList) - 1)

Call Emails(distList,emailText)

This assumes you have already coded a public subroutine that generates an email with the first variable passed in being used as the recipients and the second as the body text. I've included a sneaky little email code I have written into my Access db which automatically sends me error reports when an error is generated.

Sub ErrorEmail(ErrNum, ErrDesc, Object, R)
' The purpose of this subroutine is to send the database admin an email any time an error occurs.
' This email will contain the date/time the error occured along with the error number and description
' and the form/report being used when the error occured.
    Dim objOL As Outlook.Application, objMsg As Outlook.MailItem, objRecip As Outlook.Recipient, V As String

    V = "v2.8" ' @ UPDATE THIS WITH EACH VERSION @

    Set objOL = CreateObject("Outlook.Application")
    Set objMsg = objOL.CreateItem(olMailItem)
    With objMsg
        Set objRecip = .Recipients.Add("myemail@me.net")
        objRecip.Type = olTo
        .Subject = "Error Occured in myDb" & V & ": " & Err.Number & " - " & Err.Description
        .Body = "An error occurred in the Access application myDb" & _
        V & " at the following time:  " & Now() & " with the following number and description.  " & _
        vbCr & ErrNum & ": " & ErrDesc & vbCr & vbCr & _
        "The error occurred in the exectution of " & R & _
        " while the user was using the " & Object & " object in SMART Trackers."
         
    For Each objRecip In .Recipients
        objRecip.Resolve
    Next
    .Send '<- If set to Send will send email automatically.  If set to Display, will open the Outlook window and the user will have to press Send
    
    End With
    
End Sub

As part of my error handling code I include a call to this sub. Example: Call ErrorEmail(Err.Number, Err.Description, Object, "Report_Open"). In this case I use the caption of the main report label to set the Object variable. This is an example of how you could write an email automation sub to accept different distribution lists (Set objRecip = .Recipients.Add(Your Variable Here) ) as well as the message text (.Body = Whatever you want). This way, if your users like this functionality, you can easily build it in to any number of events without having to re-code the entire thing.

[Monterey_Manzer]

Oops, forgot to include something important

Whenever you are using DAO recordsets you want to be sure to clean up after yourself. That is, you want to close out the recordset once it's no longer needed so that each time your code runs, the only instance of your recordset is the most current one. The code is pretty simple. Just rst.Close followed by Set rst = Nothing.

Cheers!