Mailto (1 Viewer)

[coolcatkelso]

Hi guys

I use Access 2007 as a front end to Mysql and would like to be able to send an automatic email somehow to a customer when data changes.

The DBase is setup with Username as the key field and every other table uses this, tables are

Users
Appointments
Invoices
Estimates
Payments
Pictures

Is there a way that I can do this? Email address is stored in the Users table and only myself will have access to the Dbase to enter data, so I was thinking something along the lines of a OnChange: Mailto: etc? But no idea how to implement something.. Just has to email and say something like - "There are changes to your online account" etc with a link to the website for them to view?

Cheers guys

 

[MarkK]

Hey coolkat, please don't post the same question twice in this forum. Thanks,

 

[coolcatkelso]

Hi Markk

Sorry I posted it in the wrong forum and thought I could delete the Macros one but couldn't

Years ago I built a DB called Pyramid Databank with help from this site and I remember some had found something for SMTP, It was 2 files that you would put into windows\system32 folder that allowed the use of SMTP etc, and I remember someone here helped me to knock up a bit of code in VB to send an email to myself on the change event, that worked perfectly. Unfortunatley, I secured the DB with a long password and also secured the VB section and I have no idea what the password was lol, so if anyone can help with re-doing this smtp part I'd b greatful

 

[MarkK]

Look for CDO, or "collaboration data objects". There should be code for that around here. Here's the sys file you'll need. If you need to register is, search for "register windows file regsvr32"
All the best,

 

[coolcatkelso]

Hi Markk

Thanks for this, will have a look at it after the kids gone to bed.

I have found something the appears to be working manually in Macros. I was able to setup "SendObject" and left most fields blank apart from the Subject and Email Message, I was able to set this as OnChange. It does send the email but I need to manually add the customers email.. Is there a way I can grab this from the Users table and have it automatically enter this? Or is your CDO better for my needs?

 

[coolcatkelso]

Hi Markk

Had a look at CDO and that looks far better for what I need.

I have several forms / tables as mentioned above but I will run the code on each form so I can add in the email message what table has been update to make it easy for customers to navigate too.

What would be the correct way to call on the email address in the Users table though?

With cdomsg
.To = "somebody@somedomain.com"
.From = "mygmail@gmail.com"
.Subject = "the email subject"
.TextBody = "the full message body goes here. you may want to create a variable to hold the text"
.Send
End With
    Set cdomsg = Nothing
End Function

I would assume something like: .To = "Select FROM Users" kinda thing?

 

[MarkK]

To get at table data in MS Access, use a "domain aggregate function", or "open a recordset."

Domain Aggregate Function
To get data out of a table, Access provides "domain aggregate functions" like DLookup(), DSum(), DMax(), so check those out.

Open a Recordset
In this case you write a query, which is the . . .

"Select [Email] FROM Users WHERE UserID = [I][someid][/I]"

. . . kind of thing. Then you use that query to open a recordset, like . . .

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT Field FROM Table WHERE Condition")
if not rst.eof then msgbox "Here's your data, in the recordset: " & rst.fields(0).value

Cheers,

 

[coolcatkelso]

That looks good.

Kinda almost identical to php and mysql scripting.

So to form the "to" part for the email it would be:

.To = "Select [Email] FROM Users WHERE Username = [Username]"

and would it put your other part of the code "open a recordset" directly under this?

 

[MarkK]

No. You might need to look at the post again.

 

[coolcatkelso]

What I am missing lol? It's been years since I last used Access.

Can you break it down further Markk?

Cheers

 

[MarkK]

Do this . . .

.To = "Select [Email] FROM Users WHERE Username = [Username]"

. . . and see what happens. Explain to me why it fails, and then I'll explain to you how to get data out a table.

 

[coolcatkelso]

Hmm ok lol

Error I get is the server rejected the address, error 553.5.1.2 or other puncuation after the email address. js5sm6331236wid.11 gsmtp

I think its failing because there is nothing in the VB telling it to get the Username from table Invoices first. then to look at User and locate the same username and find the email address?

 

[MarkK]

Right, so to get data out of the table you need to use a domain aggregate function, or open a recordset.

 

[coolcatkelso]

Right OK, so where would I put that code you gave an example off?

Also the select statement you did, was I right tibiae where username = username?

 

[coolcatkelso]

Hmm had another look

Am I right in thinking that I'm looking at the code you put up as two seperate codes and in fact they are actually one code?

Instead of me trying to put .to Select etc

It should actually be

.TO dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT Field FROM Table WHERE Condition")
if not rst.eof then msgbox "Here's your data, in the recordset: " & rst.fields(0).value
.FROM

 

[coolcatkelso]

I got a little progress after playing around with it

Full Code

Private Sub Command19_Click()
Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "*************@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"
.Update
End With
' build email parts
With cdomsg
.To = "Select [Username] FROM Invoice WHERE Username = [Username]"
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset( _
   "SELECT Email FROM Users WHERE Username = [Username]")
If Not rst.EOF Then MsgBox "Here's your data, in the recordset: " & rst.Fields(0).Value
.From = "*********@gmail.com"
.Subject = "the email subject"
.TextBody = "the full message body goes here. you may want to create a variable to hold the text"
.Send
End With
    Set cdomsg = Nothing
End Sub

Now when I click Send Button, it pops up with a message showing the 1st email address in the record but still get the 533.1.2 error

 

[coolcatkelso]

Any further help Markk m8?

Cheers

 

[MarkK]

My interest is in supporting people's learning, and you don't appear to be making much of an effort. Am I wrong? Where's the code you wrote? Tell me something about the database you are working on, and why you need to automate emails.

 

[coolcatkelso]

Hi markk

Thanks for the response.

In all honesty, I am looking through google to get info, I picked up a book from the library on Access for Dummies lol but doesn't go into detail for Emails. I am trying to sort this myself without any help as I did with the website!.

As for the database, the email doesn't have to be automatic and as I mentioned in the original post, more than happy to click a button or something to make it work but I don't want to spend more time in the office than I have to.

The original idea I had a few years ago was to setup a client side for my business. Where customers could log into the website to view their estimates / invoices and work history etc of work carried out. I have successfully done this. Now however, I need to let them know that a new invoice / estimate has been added to their online account so they can view it / print it / pay it. Because I am using PHP and MYsql online, I can't think of a way to do this via PHP as the website only pulls info from the database, nothing online in entered to the tables. So I thought I would create an ODBC link from Access and use this as my front end.

I assumed it would be straight forward and If I put "Email" into every table, then I would have had it working by now, but what I need is:

On every form / table - I need a button or something to click on that will send an email to the customer, if I am in Table-Invoices, then Onchange: Locate Username rfom current table and then find that username in Users Table and select the email address for that user only, send an email to client and then I can say "You have a new Invoice etc

The code you sorted out for me as an example seems to work, but will only select the first email address in the Users Table but sends an 533.1.2 error which according to the internet is an error in or after the email address.

 

[coolcatkelso]

Had another look and after much googling and reading from the guide, I managed to get something working. Might be rough, but is doing the job I think

Private Sub Command20_Click()

Dim stEmailRecips As String
Dim stSubject As String
Dim stSQL As String
Dim db As DAO.Database
Dim Rs As DAO.Recordset


stSQL = "SELECT Email FROM Invoices Query;"
Set db = CurrentDb
stEmailRecips = Me.Email
stSubject = ("New Invoice Added")
DoCmd.SendObject acSendNoObject, , , stEmailRecips, , , stSubject, "A new invoice has been created at RGRoofing.UK please click here http://www.RGRoofing.uk"
    End Sub
    
It's using outlook but as I said it works