UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Send Email After New Record Is Created, Access 2010    
 
   
Brandi
post Apr 14 2018, 01:03 PM
Post#1



Posts: 1,655
Joined: 24-June 04



When a record is added to a form (by various users), my user who administers the database, would like an email message alerting them that a record has been created (this means they will have to order some criminal evidence).

Would I create some sort of code to do this and what would be the event to choose?

Thank you.
Brandi
Go to the top of the page
 
Doug Steele
post Apr 14 2018, 01:31 PM
Post#2


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If you only need to this when a new record is created (and not when a record is updated), you could add code to the form's AfterInsert event.

--------------------
Go to the top of the page
 
Brandi
post Apr 14 2018, 02:18 PM
Post#3



Posts: 1,655
Joined: 24-June 04



OK. I had this code from thedbguy but it was in a mdb database.

When I try to run it on my 2010 version I get the following message. "2293 Microsoft cannot send this message."
The email address is definitely correct.


Here is the code. Does the code need to be modified for 2010?
Thanks.
Brandi

Private Sub cmdSend_Click()
'theDBguy
'4/29/2010

On Error GoTo errHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strTo As String
Dim strBody As String

If Me.cboInterpreter > "" Then
strTo = Me.cboInterpreter.Column(3)
strBody = "Your assignment will be:" & vbCrLf & vbCrLf
strSQL = "SELECT * FROM qryAssignments WHERE InterpreterID = " & Me.cboInterpreter

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do While Not .EOF
strBody = strBody & "On " & !AssignmentDate & " from " & !BeginTime & " to " & !EndTime & " to " & !Description
strBody = strBody & ". Please contact " & !Contact & "." & vbCrLf
.MoveNext
Loop
End With
DoCmd.SendObject acSendNoObject, , , strTo, , , "Your Assignment", strBody, True
Else
MsgBox "Please select an interpreter. Thank you.", vbInformation, "Select an Interpreter"
End If
Set rs = Nothing

errExit:
Exit Sub

errHandler:
If Err.Number = 2501 Then
'email was cancelled
Else
MsgBox Err.Number & ": " & Err.Description
Resume errExit
End If

End Sub
Go to the top of the page
 
Doug Steele
post Apr 14 2018, 02:39 PM
Post#4


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Do you have a default email client defined on your computer? AFAIK, you cannot use SendObject with web-based mail clients.

--------------------
Go to the top of the page
 
Brandi
post Apr 14 2018, 07:19 PM
Post#5



Posts: 1,655
Joined: 24-June 04



hmm. not sure exactly. Actual emails go to bluehost.com but I use Outlook to receive and send.
Is there another command in that case?
Go to the top of the page
 
DanielPineault
post Apr 14 2018, 08:08 PM
Post#6


UtterAccess VIP
Posts: 5,865
Joined: 30-June 11



What Doug is asking, your Outlook app is functional? You can send and receive normally?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Brandi
post Apr 15 2018, 09:25 AM
Post#7



Posts: 1,655
Joined: 24-June 04



Yes, I send and receive my mail via Outlook all the time.
Brandi
Go to the top of the page
 
Doug Steele
post Apr 15 2018, 09:40 AM
Post#8


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It's not a case of whether or not you can use Outlook. It's a case of whether or not Outlook has been set as the default mail client on your computer.

Without knowing what operating system you're using, I can't tell you how to check. Try looking up default mail client and checking your status.

--------------------
Go to the top of the page
 
Brandi
post Apr 15 2018, 10:55 AM
Post#9



Posts: 1,655
Joined: 24-June 04



I went to Settings, Default Apps and Microsoft Outlook is listed as my default email.
Go to the top of the page
 
projecttoday
post Apr 15 2018, 11:26 AM
Post#10


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


So it's failing on Docmd.SendObject? I can't tell you what's wrong with that but if you still can't find it then you could switch it to an Outlook object.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Brandi
post Apr 15 2018, 05:52 PM
Post#11



Posts: 1,655
Joined: 24-June 04



I am not extremely proficient with writing code but I can usually follow it.
Not quite sure what you mean by switch it to an Outlook object.
Thanks.
Brandi
Go to the top of the page
 
DanielPineault
post Apr 15 2018, 06:39 PM
Post#12


UtterAccess VIP
Posts: 5,865
Joined: 30-June 11



What about attempting an Office Repair to see if that might help?




As for Outlook Object model, see http://www.devhut.net/2010/09/03/VBA-send-...ook-automation/ for a function to get you going.


--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Brandi
post Apr 15 2018, 07:01 PM
Post#13



Posts: 1,655
Joined: 24-June 04



Thank you. I have looked at your code and it appears to be a bit much for me but I will see what I can do and let you know.
Outlook is definitely my default email. I ran a full scan on my computer today (Kaspersky) and nothing appeared as wrong.
I am not sure how to repair Office. Office 2010 was loaded on my computer originally.
Thanks.
Brandi
Go to the top of the page
 
WildBird
post Apr 16 2018, 02:12 AM
Post#14


UtterAccess VIP
Posts: 3,322
Joined: 19-August 03
From: Perth, Australia


Try a quick test

sub TestEmail
DoCmd.SendObject , , , "youremail@somewhere", , , "Subject", "Body", False
end sub

I tried this and got security warnings, but with my outlook code, it is fine.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Brandi
post Apr 16 2018, 01:22 PM
Post#15



Posts: 1,655
Joined: 24-June 04



Actually it did work.
Very interesting! First a message came up that said "A program is trying to send a message on your behalf." click Accept or Deny.

So I clicked Accept and I did get the message in Outlook.
So there must be something different about the code that you suggested that is different.
I notice you have FALSE at the end of your statement.
So this works: DoCmd.SendObject , , , "youremail@somewhere", , , "Subject", "Body", False

Here is the statement I was using before which does not work: BTW what is acSendNoObject in the statement below?

DoCmd.SendObject acSendNoObject, , , strTo, , , "Your Assignment", strBody, True


I will see if I can figure this out. Ultimately, I hope the User doesn't have to do anything at all and when they create a new record, I will send the email when the new record is created.

At least I did get an email this way. Thank you so much.
Brandi
This post has been edited by Brandi: Apr 16 2018, 01:26 PM
Go to the top of the page
 
DanielPineault
post Apr 16 2018, 01:48 PM
Post#16


UtterAccess VIP
Posts: 5,865
Joined: 30-June 11



You may like to look over http://www.devhut.net/2018/02/16/ms-access...-send-an-email/ (the sample at the bottom may help as well)

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Brandi
post Apr 16 2018, 04:09 PM
Post#17



Posts: 1,655
Joined: 24-June 04



Thank you. I did download your sample db. I am making progress and will let you know if I have any questions.
Brandi
Go to the top of the page
 
Brandi
post Apr 16 2018, 06:25 PM
Post#18



Posts: 1,655
Joined: 24-June 04



OK. I used the SendObject with False on the end and it appears to be working for me in testing.
My Outlook still bothers me with a message that I need to Allow or Deny the sending of the message.
I am having my User test now and see if their Outlook email has the same message.

Question: Does it matter if Outlook is open when Access sends the email?

I did use the code in the AfterInsert event and it seems to do the trick.

Thank you all so much.
Brandi
Go to the top of the page
 
WildBird
post Apr 16 2018, 06:54 PM
Post#19


UtterAccess VIP
Posts: 3,322
Joined: 19-August 03
From: Perth, Australia


CODE
Function SendEmail2(ByVal strEmailAddress As String, ByVal strBody As String) As Boolean
'Date:          Thursday, 01 February 2018 3:22:09 PM
'Author:        Stephen Cooper
'Email:         stephen.cooper@XXXXXXX
'Ph:
'In parameters
'Output
'Description:   Sends email
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

SendEmail2 = True

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim oApp As Object
Dim oMail As Object
Dim strAttachment As String
Dim strSubject As String

intMouseType = Screen.MousePointer

DoCmd.Hourglass True

strSubject = "Your Assignment "

strErrorMsg = "Sending email to " & strEmailAddress

Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(0)
oMail.Subject = strSubject & Format(Date, "dddd, dd mmm yyyy")
oMail.body = strBody
oMail.To = strEmailAddress
'oMail.Display   'Will get the signature added, comment this out to suppress it
oMail.Send

ExitHere:
On Error Resume Next
'Close all recordsets etc here
Screen.MousePointer = intMouseType
Set oMail = Nothing
Set oApp = Nothing
Exit Function

HandleError:
Select Case Err.Number
Case Else
    'LogError "SendEmail2|" & CurrentProject.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    SendEmail2 = False
    Resume ExitHere
End Select

End Function


Try this. Just use it instead of
DoCmd.SendObject acSendNoObject, , , strTo, , , "Your Assignment", strBody, True
Use
SendEmail2 strTo, strBody

Using SendObject here for me brings up warning, but using this code is fine - i.e. no warning

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Brandi
post Apr 17 2018, 01:38 PM
Post#20



Posts: 1,655
Joined: 24-June 04



Since the code above said Function, I thought I should copy it to a module.
I created a module and named it SendEmail2.

I changed the DoCmd line to SendEmail2 strTo, strBody

When the code runs now I am getting an error that says "Expected variable or procedure, not module".

Thanks.
Brandi
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:48 AM