How to email form to names that were entered in records on a subform.

Hi -

I have a form that, once it gets entered, needs to be emailed to people whose names were selected in individual records on an embedded subform.  Is this possible?  

Note: The access database is used by many.  It would probably be easiest to have it email from one generic gmail address (something like NewNCR@gmail.com) .  Is this possible?
KrisYoungNYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
What do you mean by emailing a form?  Do you mean an Access form, or a custom Outlook mail message, or what?  As far as emailing to addresses from records selected on a subform is concerned, there are several possibilities.  You could have an Email Yes/No field in the table on which the subform is based, and then you could create a recordset of the records with Email = True, and send an email to each.  Or you could replace the subform with a multi-select listbox, and use the ItemsSelected collection of the listbox to send an email to each selected item.

Here is some sample code for sending an email to each member of a recordset:
Public Sub EMailAllContacts()
'Created by Helen Feddema 31-Oct-2009
'Last modified by Helen Feddema 31-Oct-2009

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim appOutlook As Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strEmail As String
   
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryContacts")
   Set appOutlook = GetObject(, "Outlook.Application")
   
   Do While Not rst.EOF
      strEmail = Nz(rst![EmailName])
      If strEmail <> "" Then
         'Create email
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Subject"
         msg.Body = "Message"
         
         'Comment out next line and uncomment Send line
         'to send automatically
         msg.Display
         'msg.Send
      End If
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

0
Helen FeddemaCommented:
And here is some code for working with the ItemsSelected collection of a multi-select listbox:
Private Sub cmdExportData_Click()

On Error GoTo ErrorHandler

   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   Set lst = Me![lstSelectContacts]
      
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   strTitle = "Information missing"
   
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
            Title:=strTitle
         GoTo NextItem
      End If
      
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
      
NextItem:
   Next varItem

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
Helen FeddemaCommented:
Sending an Access report is certainly possible -- it is best to send it as a PDF, which can be done easily in Access 2007 or higher.  Here is some code for that:
Public Sub SendPDFEmails()
'Created by Helen Feddema 24-Jan-2010
'Last modified by Helen Feddema 24-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngEmployeeCount As Long
   Dim lngID As Long
   Dim msg As Outlook.MailItem
   Dim rpt As Access.Report
   Dim rstEmployees As DAO.Recordset
   Dim strAttachmentsPath As String
   Dim strBody As String
   Dim strEmployeeName As String
   Dim strEMailAddress As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReportFile As String
   Dim strReportName As String
   Dim strSQL As String
   Dim strSubject As String
   Dim strTitle As String
   
   strAttachmentsPath = GetProperty("AttachmentsPath", "") & "\"
   strSubject = GetProperty("MessageSubject", "Your custom report")
   strBody = GetProperty("MessageBody", "Your current report is attached as a PDF")
   strReportName = "rptEmployeeInvoices"
   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset("qryEMailEmployees")
   lngEmployeeCount = rstEmployees.RecordCount
   Debug.Print lngEmployeeCount & " employees need reports"

   If lngEmployeeCount = 0 Then
      strTitle = "No reports to send"
      strPrompt = "No employees need reports; canceling"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
   Do While Not rstEmployees.EOF
      lngID = rstEmployees![EmployeeID]
      strEmployeeName = rstEmployees![Salesperson]
      strEMailAddress = rstEmployees![Email]
      strReportFile = strAttachmentsPath & "Employee Invoices" _
         & " for " & strEmployeeName & ".pdf"
      Debug.Print "PDF save name and path: " & strReportFile
      
      'Create filtered query as report record source
      strRecordSource = "qryInvoices"
      strQuery = "qryInvoicesPerEmployee"
      
      If lngID <> 0 Then
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[EmployeeID] = " & lngID & ";"
      End If
   
      Debug.Print "SQL for " & strQuery & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuery, strSQL)
      
      'Output customized report to PDF
      DoCmd.OutputTo objecttype:=acOutputReport, _
         objectname:=strReportName, _
         outputformat:=acFormatPDF, _
         outputfile:=strReportFile, _
         autostart:=False
      
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
         .Send
      End With
   
NextEmployee:
      rstEmployees.MoveNext
   Loop
   
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SendPDFEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

=======================
Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jeffrey CoachmanMIS LiasonCommented:
< It would probably be easiest to have it email from one generic gmail address (something like NewNCR@gmail.com) .  Is this possible?>
This would actually make this more difficult...

MS Office (Access) likes to see MS Outlook (running on MS Exchange Server) as the default Email client
Then you could use either "SendAs" or SendOnBehalfOf"
So this will complicate things here...
Perhaps this will need to be posted as a separate, new question

I am sure Helen can assist you with the Email issue
0
KrisYoungNYAuthor Commented:
Dear Helen -

Thank you for your thoughtful suggestions.  I think the third suggestion, 04/09/11 07:33 AM, ID: 35357901, is the closest to what I'd like to try.  Now, I just need to to carefully replace your field names for my field names and hope I don't screw the whole thing up.

Let me just make sure I know how this will work:  I'll be in a top-level Form (Nonconformance Report), filling out fields.  Embedded on this form is a subform that contains a routing of comments that need to be obtained before an agreed-up resolution can be reached.  The routing of these comments includes an automatic incrementing field, a field that shows the current Nonconformance number, a field with a drop-down menu of people, a date field, and a comment field.  The drop-down menu of people is the table that I would have the email addresses in?

I'm assuming that when the report is filled out - and the routing of names I want to email it to has been selected, I would press a [Command] button on the top-level form - and that button would have the code that you have written in it.  Is that correct?  And all I need to do is replace my fields for the ones that I think you're referencing?  

One more question.  I printed out a naming convention for Access (the Leszynski/Reddick Guidelines).  When you write something like:
            Dim lngCount As Long
            Dim lngEmployeeCount As Long
            Dim lngID As Long
                                                                  .    .    . does this mean I need fields called "Count", "EmployeeCount" and "ID" and that their properties need to be Long?

One final question.  In your 3rd example above, at line 102, the program stops.  What event would make it continue on past that line?  (I have no idea what I'm talking about - but, is that the Errorhandler section of the code?).  Thank you, again, for your time.
0
KrisYoungNYAuthor Commented:
Wow!  You're THE Helen Feddema, author of Expert One-on-One Microsoft Access Application Development.  I was just reading about that in Wikipedia.  Okay; I'm impressed.
0
Jeffrey CoachmanMIS LiasonCommented:
Am what am I, ...Chopped Liver...?

;-)

LOL

JeffCoachman
0
KrisYoungNYAuthor Commented:
Jeff Coachman -

I happen to like Chopped Liver.I do see you have the start of a website, online, with an access database that that people can view that makes several cool graphs on a report.  I can't even imagine how you did that.  So, yes, I'm impressed, too.  

Still hoping Helen might answer my question specific to the code she wrote.  
0
Jeffrey CoachmanMIS LiasonCommented:
LOL
0
KrisYoungNYAuthor Commented:
I was hoping Helen might write back - but to no avail.  I'll just close this out for now and try again later.
0
Helen FeddemaCommented:
Sorry, Kris -- I have been busy with other things.  Here are some answers:

The declaration section -- all those Dim statements -- is for declaring variables of specific data types (each has its own prefix, such as str for a String variable and lng for a Long).  

The code stops on Line 102 because that is the end of the procedure (End Sub).

For more specific advice, I would need to see your database.  Because of the subform, the code might be somewhat more complicated than mine.
0
christopherfennellCommented:
Great post!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.