?
Solved

access 2010 and email form data

Posted on 2013-12-05
6
Medium Priority
?
460 Views
Last Modified: 2013-12-23
I have to create a form in access that a user fills out from a shared library using their own access clients.  When they complete the form it must go into the database and also get emailed to  a group email address at the same time.  Is there a way to submit the data to the database and at the same time email that to the group of folks who need it.  They must see every new initial entry quickly.  I am not a programmer so I won't be able to use visual basic for this unless it simple.  I saw the macro feature but did not see where it would do this from a form just from a table or a report.
0
Comment
Question by:kdschool
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 85
ID: 39699381
You can't do this without programming, and you can't do this unless the users are entering data through a form (i.e. if they're entering data directly in a Table or Query, then you're pretty well out of luck).

Submitting to the database is done automatically if you have bound Access forms (i.e. a form that is based on a table).

Macros can be called from Forms and Reports, so you can use those. To send an email, you use the SendObject method. I haven't used macros in a very long time, but essentially you create a New Macro, and then select the SendObject action. Select the item you want to send, fill in the details (i.e. Subject, email address, etc) and then set the Macro to be run when a certain Form event occurs (like AfterInsert, which runs when a new record is added).
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39701541
I would put a command button on the form where the users enter the data, to create an email containing the data and send it to a predefined Distribution List.  Here is some code for a similar process (it would have to be adapted to your needs):

Private Sub cmdSendEMail_Click()
'Created by Helen Feddema 23-Mar-2012
'Last modified by Helen Feddema 6-Dec-2013

On Error GoTo ErrorHandler

   Dim strToEMail As String
   Dim strMessageSubject As String
   Dim strCustomerName As String
   Dim strCompany As String
   Dim strTaskSubject As String
   Dim strBody As String
   Dim dteSend As Date
   Dim frm As Access.Form
   Dim strMessage As String
   Dim strSendTime As String
   Dim strSendDate As String
   Dim strCoverSheet As String
   Dim msg As Outlook.MailItem
   Dim tsk As Outlook.TaskItem
   Dim att As Outlook.Attachment
   
   'Test for required fields
   strToEMail = Nz(Me![txtToEMail].Value)
   If strToEMail = "" Then
      MsgBox "Please enter an email address"
      GoTo ErrorHandlerExit
   End If
   
   Set pappOutlook = GetObject(, "Outlook.Application")
   Debug.Print "Email: " & strToEMail
   strMessageSubject = Me![txtMessageSubject].Value
   strTaskSubject = Me![txtTaskSubject].Value
   If strTaskSubject = "" Then
      MsgBox "Please enter a subject"
      Me![txtSubject].SetFocus
      GoTo ErrorHandlerExit
   End If
   
   strBody = Me![txtBody].Value
   If strBody = "" Then
      MsgBox "Please enter the message text"
      Me![txtBody].SetFocus
      GoTo ErrorHandlerExit
   End If
   
   'Send email
   'Create new mail message and send it now
   Set msg = pappOutlook.CreateItem(olMailItem)
   With msg
      .To = strToEMail
      .Subject = strMessageSubject
      .BodyFormat = olFormatPlain
      .Body = strBody
      .Save
      'Use this line to display the message
      .Display
      'Use this line to send the message automatically
      '.Send
   End With
   
ErrorHandlerExit:
   Set msg = Nothing
   DoCmd.Close objecttype:=acForm, objectname:=Me.Name
   Exit Sub

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

Open in new window

0
 

Author Comment

by:kdschool
ID: 39708735
Helena on this code trying to figure out what  I modify ?

The DIM would be each variable or field in my form that I want to be sent through email instead of the ones you have listed.

Not clear on what I should modify on this part. Would TO = StrToEmail be my distribution email address?
'Create new mail message and send it now
   Set msg = pappOutlook.CreateItem(olMailItem)
   With msg
      .To = strToEMail
      .Subject = strMessageSubject
      .BodyFormat = olFormatPlain
      .Body = strBody
      .Save
      'Use this line to display the message
      .Display
      'Use this line to send the message automatically
      '.Send

would I then add this code to the button as a vb script or a macro that executes on submit?  Thank you so much.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:kdschool
ID: 39717081
could you provide a link that would show me how to do the sendobject action option
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39717958
The Access help file has a good writeup on SendObject. Here's the online version of that:

http://msdn.microsoft.com/en-us/library/office/ff197046.aspx

If you want to just send an email, with no attached item, then you'd do this:

DoCmd.SendObject acSendNoObject, , , "bob@bob.com", , "Email Subject", "Here's the email"

Obviously, you'd need to replace the various bits to reflect what you need. If you have a dedicated email address you will be sending this to, then you can hardcode that.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39736889
If you use my technique, then you need to set a variable from a control on your form for each piece of information you need to use in the email, then set the appropriate email properties with the variables.  Using variables lets you check whether they have appropriate values before creating the email.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question