• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

MS ACCESS Automatic Emails

Hello,

I have created a quality violation form, for users to manually enter quality violations. The users select an associate from an associate table that contains the associates name, their supervisor, and both of their email addresses.

Is there a way to automatically send an email to both parties immediately after a qv is entered on the qv form?

Ultimately I would like the associate to be able to check a box to report back to the qv_table that they acknowledge the qv, however sending the email is the main priority.

Thanks,
Dustin
0
dplowman
Asked:
dplowman
  • 9
  • 7
1 Solution
 
danishaniCommented:
Yes, that is possible, what will be the content of the email?

You can use the Outlook Object for this, or SendObject, but I prefer the first option.
With the Outlook Object, you can also use the Form values, to populate the content of the Email.

If you want more examples or code, let me know.

Daniel
0
 
dplowmanAuthor Commented:
Thanks for the quick reply. Once a supervisor fills out a form I have created, I would like a report to be generated to be sent in the email, so the associate can see the form in the body of their email. All of the data is available in the same table.
0
 
danishaniCommented:
Will it be a Report as Attachment, or just the same information as in Form, but then in Email format?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
dplowmanAuthor Commented:
Would there be an advantage of one over the other. Is their a way to add a check  box that the associate could check and send back to access to have it marked as read? Not sure if that is an option or not.
0
 
danishaniCommented:
Well attach information from your Form, or attach a Report to your Email is pretty straight forward and not that difficult to implement.
However when you want to use Outlook as part of your application, to send information back to Access is another.

It's all possible, but you have to do some programming though.
0
 
dplowmanAuthor Commented:
I would like to start with just have an emali automatically sent, when a form entry is saved and closed. Would I create a button and codes vba to send the email? I have the associates email address in a seperate table than the one linked to the form. The qvform is linked to the qvtable that contains the assocaites name. The associatetable contains the associates name and email address.
0
 
danishaniCommented:
Well, there are several ways to do the Automation. Its easier do it from the Form itself, so you can make use of the Form values, and encorperate those in your Email content.

You can also sending the information via opening a Recordset. Then you need some criteria selected from a Form, to make sure you sending the right Quality Violation.

Below an example, using Form values, late binding (no Outlook Library Reference needed):
Private Sub Command2_Click()

Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)

Dim strPathAttach As String

' set attachment if you have
strPathAttach = Me.YourAttachmentField


On Error GoTo err_Error_handler

'set receipient, you can use a DLookup() to retrieve your associate Email address
objMailItem.To = DLookup("YourEmailField", "tblName", "[AssociateID]=" & Me.AssoicateID)

'set subject with text and Form values
objMailItem.Subject = "Some text and Quality Violation" & Me.QualityViolationID

'set body content with text and Form values etc.
objMailItem.Body = Me.YourFieldName & vbCrLf & "some text here:" & vbCrLf & Me.YourFieldName2

'Check path and file, then set Attachment
If Len(Dir(strPathAttach)) Then
With objMailItem.Attachments
.Add (strPathAttach)
End With
End If

' display email
' objMailItem.Display

' sending mail automaticly
objMailItem.Send

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
exit_Error_handler:
 On Error Resume Next

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  'trap error 287
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler

End Sub

Open in new window


Hope this helps,
Daniel
0
 
dplowmanAuthor Commented:
receiving an error:

Error 3075 syntax error in string in query expression '[Fullname]='John Smith'

Private Sub Command226_Click()

Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)

Dim strPathAttach As String

On Error GoTo err_Error_handler

'set receipient, you can use a DLookup() to retrieve your associate Email address
objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[Fullname]='" & Me.cboAssociate)

'set subject with text and Form values
objMailItem.Subject = "Quality Violation" & Me.LOAN_CODE

'set body content with text and Form values etc.
objMailItem.Body = Me.qv_date & vbCrLf & "some text here:" & vbCrLf & Me.Entered_Date

'Check path and file, then set Attachment
If Len(Dir(strPathAttach)) Then
With objMailItem.Attachments
.Add (strPathAttach)
End With
End If

' display email
' objMailItem.Display

' sending mail automaticly
objMailItem.Send

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
exit_Error_handler:
 On Error Resume Next

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  'trap error 287
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler

End Sub
0
 
danishaniCommented:
You might try to change the line into this, as Fullname is a Text Field:

objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[Fullname]='" & Me.cboAssociate & "'")

Open in new window

0
 
dplowmanAuthor Commented:
Thanks, missed on of the quotation marks.

Now I am getting this error.

Error -2147024893 path does not exist. Verify the path is correct.
0
 
danishaniCommented:
Another note, make sure that the bound field to Me.cboAssociate is also the Fullname, else it will not match. If for example the bound field is AssociateID, then you need to change the line into:

objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[AssociateID]='" & Me.cboAssociate & "'")

Open in new window

0
 
danishaniCommented:
Oh if you dont need Attachments, leave it all out of the code.

Hope this helps,
Daniel
0
 
dplowmanAuthor Commented:
Awesome, missed that part. Can I easily add to this code to save, email, and open new form?

0
 
danishaniCommented:
Yes, you need at least run the code, when the Form values are saved and before closing the Form.
If the Form's closed you can't retrieve the values from the Form anymore, so that make sense.

Hope this helps,
Daniel
0
 
dplowmanAuthor Commented:
So right now I have a button to send an email with your event procedure, a button with a macro to close the form, and a button with a macro to save and open new form.

I am thinking about deleting the close option, and combining the save, open new form and email into one button. As it stands now, as soon as the values are entered in the form they are saved to a server, so I dont need to actually press a save button before sending the email.

How can I combine these to have one button that sends email, opens new form.

Or do you think it would be better to send and email and close the form so the user cannot press back to see previous entries?
0
 
danishaniCommented:
Oh ok, I see, your form is bound to a Table, then the user can send the Email as soon as they are done with the entry of the Quality Violation.

Its up to you what you think works best for your users. Do you want them to click email, or do you want it to send automaticly, the moment they are done. What you can do is placing the code in the OnClose Event. And see if it the email is send out correctly.

Only downside of this it will send out email everytime you close the Form. You can resolve this by putting an Extra Field in your Table, EmailSend (yes/no) or DateTime. Then when the moment you send email you fill that field to. In the OnClose Event,

If IsNull([EmailSend]) = True Then
' send your email code
Else
DoCmd.Close
End If

But as this thread is closed already, better open a new thread. So me or others can help you with your next question. Thanks! :)
 
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now