sending email from form

[schalkse]

Hi guys,

i want to send an email to several people when someone else enters a complaint in my database.
I have a form which displays the complaint with a reference number, recordsource is "tblKlachten". i have query that is set to return only those emails i want it to send to called "qerMailBeheerder". In there is a field called "email".

Now, whenever i a user closes the form after adding a complaint, i want at the same time a mail sent to those ppl in the query with the subject the reference number of the complaint (field called "Referentienummer") and in the body other information from the fields on the form. Like the field "complaintdecription" and "clientname".

Now, i know about the sendobject thing, but i have tried alot and non would do what i want. Can anybody help me with the code.

Many thanks

 

[Ron_dK]

These might be some pointers for your consideration :

http://www.access-programmers.co.uk/forums/showthread.php?t=66251&highlight=notification+mail

or

http://www.access-programmers.co.uk/forums/showthread.php?t=49713&highlight=notification+mail

If not, search this forum for e-mail notification.

Hope this helps.

 

[schalkse]

got it working but...

I managed to got it working, but i want to send the mail without the mail popping open and me pressing send in outlook

here is my code, which calls a function for getting the adresses. When i add false or true, it gives a compilation error saying expecting: instruction.
What do i do wrong, or how can i get to send it automaticly?
defined parameter.

Dim ref As String
Dim nm As String
Dim cl As String
Dim om As String
Dim dat As String

ref = Me.tekstmail.Value
nm = Me.tblKlachten_Klantnummer.Value
cl = Me.Aard_klacht.Value
om = Me.Omschrijving_klacht.Value
dat = Me.Datum_ontvangst.Value



MsgBox " >>> " & fConList
DoCmd.SendObject , , , _
To:=fConList, Subject:="QAS Notificatie Complaint number " & ref & "; " & cl & "", MessageText:="Nieuwe klacht werd geregistreerd: " & vbCrLf & vbCrLf & ref & vbCrLf & "Klant: " & nm & vbCrLf & vbCrLf & "Omschrijving Klacht: " & om & vbCrLf & "Type klacht: " & cl & vbCrLf & vbCrLf & dat

 

[Ron_dK]

Take a look at this example.
It's a pretty good example of the e-mail automation process.


http://www.microsoft-accesssolutions.co.uk/send_email.htm

 

[schalkse]

Thanks

Thanks Rak, was a great help.

The insight i needed to have. But, maybe it is my access 2000, but the first time i press the button, it mails like it should, but when i press again, or even close access and open again and press, it says that it mails, but it does not.

Is this a bug or is there a fix?

 

[Ron_dK]

Did you include something like this in your code :

'Count of unsent e-mails
intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")
'If count of unsent e-mails is zero then the procedure will not run
'If count of unsent e-mails is greater than zero, msgbox will prompt
'to send mail.

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub
Else

rst.MoveFirst
Do Until rst.EOF

If not, paste it in and see if this resets the send object action.

 

[schalkse]

You lost me there Rak. Here is the code i use, fConlist is the function for gathering all the adresses. Do i need to make a table keeping track of the emails that have been sent? Is there no other way to reset that SendObject?

Private Sub Knop267_Click()
On Error GoTo Err_Knop267_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim ref As String
Dim kn As String
Dim ak As String
Dim om As String
Dim dat As String
Dim ko As String

ref = Me.Keuzelijst_met_invoervak292.Value
kn = Me.tblKlachten_Klantnummer.Value
ak = Me.Aard_klacht.Value
om = Me.Omschrijving_klacht.Value
dat = Me.Datum_ontvangst.Value
ko = "Me.Omschrijving"

DoCmd.SendObject , , , fConList, , , "QAS Notificatie; Nieuwe klacht " & ref, dat & vbCrLf & "Nieuwe klacht werd geregistreerd: " & ref & vbCrLf & vbCrLf & "Klant: " & kn & " " & ko & vbCrLf & vbCrLf & "Omschrijving Klacht: " & om & vbCrLf & "Type klacht: " & ak, False, False

MsgBox " Bericht werd verzonden naar: " & fConList


DoCmd.Close

Exit_Knop267_Click:
Exit Sub

Err_Knop267_Click:
MsgBox Err.Description
Resume Exit_Knop267_Click

End Sub

 

[Ron_dK]

Schalkse,

I assumed you rebuild your application in line with :
http://www.microsoft-accesssolutions.co.uk/send_email.htm
This example submits the e-mail on the basis of Query.
( "qrysendmail").

Also note that the example is a Public sub and not a private sub.
So, you might consider rebuilding the app in line with cited example.

 

[sylviah]

Send Email from Query Field

Hi,

I am relatively new to Access and am not very experienced in it. I just read this post and it is exactly what I wanted to accomplish as well. However, I am confused on the steps, such as the VB coding.

I have a macro that brings up a query with the field called "a_email", which is a list of people's email addresses. Once this field comes up on the screen, I want the macro to send an email out to all the people in the "a_email" field. I am using the SendObject action, however, I haven't been successful with it. I'm following the format of '=& [Forms]![FormName]![FormField] &' in the 'To' line. Is this the wrong approach?

Is there a way to do this without coding? Can it be done through SendObject?

Please help.

Sylvia