Emailing record in plain text

[Merdok]

Hi guys,

I have made a call log for my database and when the user clicks on save and close it saves the form and closes it.

However I also want to add an email function so if the user who the call is for has an email address then they will be notified by email... this cannot be with an attachment though as I work for a company full of luddites and convicing them to use a database system at all was hard enough! The to, subject and body text all must be filled in by this one button.

Also not all employees have email addresses (cant convince everyone) so I only want it to email employees with an email address in their user profile

I have had a look through the messages before but they either dont explain it clearly enough or end up using attachments.

cheers peeps!

 

[BukHix]

Ok lets start with this. Say you have four fields in your table like this.

name - (text)
email - (text)
send - (yes/no)
problem - (text)

For the sake of this thread we will assume your table is called TableName and that you want to use a command button to trigger the app. After you have it working the way you want you can put it in the OnExit event or what ever is appropriate for your situation.

Create a new form put a command button on it and put this code on the OnClick event.

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strMess As String

Set rsEmail = CurrentDb.OpenRecordset("TableName")

Do While Not rsEmail.EOF
    
    If rsEmail.Fields("Send") = True Then
        strEmail = rsEmail.Fields("Email").Value
        strMess = rsEmail.Fields("Problem").Value
        DoCmd.SendObject , , , _
            strEmail, , , "Subject", strMess
    End If

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

Now put some email addresses in the table, at least three, and then check two of them. Those will represent your users who have email. Put some text in the problem boxes so that you can see how the message will look to the recipients (text in the body and not as an attachment). Run the procedure. The email addresses with checks should get an email with the problem in the body of the email.

There are a lot of options or alterernatives to doing what you want. I have provided one. If that will not work in your specific situation or you need to modify the behaviour just post back to this thread and I will try and help you further.

 

[Merdok]

Ok that looks like what I need. I tried modifying it for my needs but I cant do it... just an endless stream of errors

Anyway, I've attached a cut-down version of my database for you/anyone else to have a gander at.

Any help would be apprieciated.

 

[Hayley Baxter]

I added in the field names to your table that Bukhix provided in the example ie send / problem but you can always change this to what you need. I created a query to run this from with 2 of my own email addresses with one of them set to send = true and the other = false (just to ensure that I din't email anybody else in the table had the test not worked - but it seems to be ok)

 

[Merdok]

Wow thanks! I just expected a point in the right direction!

Lemme give this a try

 

[Merdok]

aww... it works in your version but as soon as I import it into mine I get errors.

I thought of doing this but I'm not much of a coder, what changes would have to be made to make it work?

------------------------------------------------------------------
Option Compare Database

Private Sub svnclose_Click()

Dim rsEmail As DAO.Recordset
Dim StrEmail As String
Dim strMess As String
Dim Subject As String

Set rsEmail = CurrentDb.OpenRecordset("TblCallLog")
Set rsmess = CurrentDb.OpenRecordset("TblCallLog")
Set Subjct = "Message from" = DLookup("[LastLoggedIn]", "tblSystem", "[SysID]=1")

Do While Not rsEmail.EOF

If rsEmail.Fields("Referred") = "*none*" Then

With DoCmd
.save
.close

Else

StrEmail = rsEmail.Fields("Referred").Value
strMess = rsCalllog.Fields("*").Value
DoCmd.SendObject , , , _
StrEmail, , , "Subject", strMess
End If

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

DoCmd.save
DoCmd.close

End Sub

----------------------------------------------------------------

 

[Merdok]

Just as a side thought, it might be handy to have a slightly bulkier version of my database with the user data on there as well.

I've cut it down as small as I can but because of this some things no longer work. however in development mode everything should still be correct (code/data/etc..)

 

[Hayley Baxter]

I don't see a fileld called "Referred in your tblLogTable, the only field you have close to this is referred to which is the persons name, this field "Referred" you have here in the code needs to be the name of the field containing the email addresses. Like you say it may be because half of the fields are missing from the one you posted.

Tell me the names of all the fields yon need to take over ie fieldname that contains email address, field name that holds the notes you need to take over to the email and I'll see if I can help.

 

[Merdok]

basically in frmcalllog there is a referred to combo box which ideally would just display the persons name.

I want this to somehow insert the employees email address in the 'rsemail' string.

so selecting alex ward would put alex@dpc-designs.co.uk as the email receipient.

Also I need the entire record emailing to the receipient as plain text in the body of the email with "Call log notification from [current user] (thats where the dlookup thing comes in if it will work)" as the subject line.

However as I said I'm not very good at programming at all I'm really just taking baby steps in to the whole thing. Six weeks ago was the first time I'd ever used access to make anything more than a mobile phone number database!

 

[Merdok]

I can make a bulkier version if you will find it useful however it will be about 4/500kb.

Let me know if you need it.

 

[Hayley Baxter]

You could use something similar to what Mile O posted in this thread. If you have a look at the last example posted on the last page of the thread I have added an email button to this which takes the information accross to outlook based on who you select.

Example

I'm not sure about your dlookup bit, perhaps Bukhix can help you with that.

 

[Merdok]

will that be automatic though... all I want the user to do is click on save and close and the entire email process be generated automatically, generating and sending the email without the user even being aware of it.

Nearly all of the employees here are complete luddites and getting them to have a database running things for them panics them enough without making things pop up as well. I want to avoid allowing them to use anything but the nexus.

 

[Hayley Baxter]

No, that example would not be automatic because you would need to first select who to email. Going back to the original code first posted by Bukhix (that would work for you) you could put the code on close and that should do it.

 

[Merdok]

sorry to sound awkward but I dont know how to integrate that into my existing FrmCallLog Form. Thats what I need help with.

 

[Hayley Baxter]

Do you want to send me your db I'll I'll see what I can do?

Email

 

[Merdok]

erm.... yeah sure... its nearly two MB I hope thats ok.

Sorry about the design inconsistencies, you caught me in the middle of a design change.

 

[Hayley Baxter]

That's fine just send it to my email.

 

[BukHix]

I am not sure where you are at with the email thing but did you get the Dlookup sorted out?

 

[Hayley Baxter]

I think you're better off helping out with that Buck, now that you're here. I was just trying to help out since you weren't online but I'm not sure about that bit anyway - over to you.

 

[Merdok]

I really appreciate your help guys... I'm ok with access but when it comes to VB I'm completely at a loss.