auto emails in access 2007

Question

Hi,

I have a database for jobs what i want to do is send an automated email to a person who i assign a job to, i clearly have no idea what is the best way of doing this. Please some one help.

Thanks

Answer 1

You can either use a Macro or VBA in the After Update Event of a Control or of the Form, to send the email. How are you wanting this to work? Are you wanting a report to be generated and from the Report to send the email or you simply want an email sent with only the underlying data in it?

---

Chris Ward

Answer 2

Here's one way but with a report...

http://www.access-diva.com/vba16.html

---

--
Gina Whipp
Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 3

i want to do it with the vba code but have no idea how to even start with, what i want it do is, when a new jobs is opened and assigned to a name it sends an alert email to that person telling that a job has been assigned to them (names are all in the combo box).

Answer 4

Do you want to send a report or just an eMail?  Do you want a predefined message or do you want to be able to add to the message?

---

--
Gina Whipp
Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 5

just email to the person selected in the fireld "Assigned to" with predefined  message.

Answer 6

Huh? No message or subject, just a blank eMail?  Okay, well you can use the DoCmd.SendObject.  Have a look at...

http://www.fmsinc.com/microsoftaccess/email/sendobject.html

---

--
Gina Whipp
Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 7

Hi k124,

Welcome to the Access forum.

I built a simple solution as follow for your reference:

tbName: ID | pName | pEmail

tbJob: ID | JobName | pID

Add a combo box based on the tbName table onto the form based on tbJob. 

On the After Update event of the combo box, add the following code:

Private Sub cboPerson_AfterUpdate()
If Me.cboPerson = "" Or Me.JobName = "" Then
    Exit Sub
End If

Dim sSubject As String
Dim sEmailAdd As String
Dim sBody As String

sSubject = "Assign the job： " & Me.JobName.Value
sEmailAdd = Me.cboPerson.Column(2)
sBody = "Hi " & Me.cboPerson.Column(1) & vbNewLine & sSubject
'Edit the message before send. 
DoCmd.SendObject acSendForm, Me.Name, acFormatPDF, sEmailAdd, , , sSubject, sBody, True
' Send without editing.
' DoCmd.SendObject acSendForm, Me.Name, acFormatPDF, sEmailAdd, , , sSubject, sBody, False

End Sub

I've uploaded the database via skydrive:http://sdrv.ms/S6L9k0

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 8

Thanks for the replies, Gina i mentioned that i want to send predefined message,

Yoyo Jiang thanks for the reply but when i use your given code it gives me error message saying

"the format in which you are attempting to putput the current object is not available".

What its doing wrong?

Answer 9

Test with Yoyo's Test db, it does work. However it also sends a pdf file within the email. To send without the pdf file attachment make the following change to the code;

From: DoCmd.SendObject acSendForm, Me.Name, acFormatPDF, sEmailAdd, , , sSubject, sBody, True

To: DoCmd.SendObject acSendNoObject, , , sEmailAdd, , , sSubject, sBody, True

What trouble are you having? Have you applied the code to your database? If so post your modified code so we can review for changes. Also is your database in a trusted location? It needs to be to function correctly.

Thank you!

@ Yoyo, should you include Option Explicit?

---

Chris Ward

Answer 10

Hi, thanks for the reply much apreciated

at the moment i am running Yoyo's database and its giving me error on his created database

"the format in which you are attempting to putput the current object is not available".

Answer 11

oKay,

Try changing the code as mentioned above.

From: DoCmd.SendObject acSendForm, Me.Name, acFormatPDF, sEmailAdd, , , sSubject, sBody, True

To: DoCmd.SendObject acSendNoObject, , , sEmailAdd, , , sSubject, sBody, True

If you need assistance changing that, please let us know.

Also, is the database in a trusted location?

---

Chris Ward

Answer 12

The reason for the error is that the code is trying to output a .pdf object. I suspect you do not have the addin installed so if you change the line of code it should work without attempting to output the .pdf

Also, what email program are you using? Outlook, I hope?

---

Chris Ward

Answer 13

Hi everyone, thanks for the responses, it has been really helpful, i have managed to get it working wich is great with this code:

Private Sub Assigned_To_AfterUpdate()
If Me.Assigned_To = "" Or Me.Request = "" Then
Exit Sub
End If
Dim sSubject As String
Dim sEmailAdd As String
Dim sBody As String
sSubject = "You have been assigned a job AD0" & Me.AdHoc_No.Value
sEmailAdd = Me.Assigned_To.Column(2)
sBody = "Hi " & Me.Assigned_To.Column(1) & vbNewLine & sSubject & vbNewLine & vbNewLine & "Many Thanks" & vbNewLine & Me.Opened_By.Column(1)
DoCmd.SendObject acSendNoObject, , , sEmailAdd, , , sSubject, sBody, True
End Sub

I have another problem now that when it opens the email in outlook and i decide not to send an email and try to close it, it gives run time error "2501" "the sendobject action was canceled"

how can i make it flexible without having to have an error.

Many Thanks

Answer 14

Add this to your code where indicated

On Error GoTo HandleError
HandleError:
    If Err.Number = 2501 Then Resume Next

Private Sub Assigned_To_AfterUpdate()
    If Me.Assigned_To = "" Or Me.Request = "" Then
        Exit Sub
    End If
Dim sSubject As String
Dim sEmailAdd As String
Dim sBody As String
sSubject = "You have been assigned a job AD0" & Me.AdHoc_No.Value
sEmailAdd = Me.Assigned_To.Column(2)
sBody = "Hi " & Me.Assigned_To.Column(1) & vbNewLine & sSubject & vbNewLine & vbNewLine & "Many Thanks" & vbNewLine & Me.Opened_By.Column(1)
On Error GoTo HandleError
HandleError:
    If Err.Number = 2501 Then Resume Next
DoCmd.SendObject acSendNoObject, , , sEmailAdd, , , sSubject, sBody, True
End Sub

hth

---

Chris Ward

Answer 15

>> Yoyo, should you include Option Explicit?

@ Chris: Thanks for pointing it out. I will take care in the future.

@ K124:

Now how is it going with the help of Chris? Please feel free to let us know if you need any help.  

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 16

perfect - thank you so much i have learnt completely something new and thanks to you guys for helping me out. :-)