button to send automated email

[bricklebrit]

Hello,

I was interested in programming a button in an order form sends an automated email to the customer through Outlook. The customer's email address are already stored in a linked table. Are there any pre-programmed basic scripts that could send a message like:

"
Hello [firstName],

Thanks your [check/payment] arrived safely.

"

Thanks in advance for any advice or assistance!

 

[Paul_Bricker]

Function openoutlook()
Dim txtTO As String
Dim strValue As String
Dim strName As String
Dim strMessage As String
Dim frm As Form
Set frm = Forms![Work Order]
txtTO = frm![emailaddress]
strMessage = frm![FirstName] & ", the Work Request you submitted " & vbCr & "'" & frm![Nature of work to be performed] & "'" & vbCr & "has been completed on " & frm.[Date Work Completed] & vbCr & "by " & frm![Work Assigned To].Column(2) & " " & frm![Work Assigned To].Column(1) & vbCr & "let me know if you have any question" & vbCr & "Paul"

Set myOLApp = CreateObject("Outlook.Application")
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailItem)
myOLItem.To = txtTO
myOLItem.Body = strMessage
'myOLItem.Display
myOLItem.Send
myOLApp.Quit
End Function

This is a routine I use to notify people that submitted work orders have been completed. I have the email addresses on the form so I just pick that up there. You might have to adjust the txtTO to look at your Table for the address. Post back with specific questions.

Paul

 

[bricklebrit]

Thanks for your prompt response and assistance. I modified the code, but am having problems --

is the txtTO the name of the table with the email value?

I'm not exactly sure how to use the function coding as I am trying to attach the code to a button on an order form. So far I have:


Private Sub PaidEmailButton_Click()

Function openoutlook()
Dim txtTO As String
Dim strValue As String
Dim strName As String
Dim strMessage As String
Dim frm As Form
Set frm = Forms![Orders]
txtTO = [Customers]!
strMessage = frm![FirstName] & ", the order you submitted " _
& vbCr & "has been shipped out on " & frm.[ShipDate] _
& "Let me know if you have any question."

Set myOLApp = CreateObject("Outlook.Application")
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailItem)
myOLItem.To = txtTO
myOLItem.Body = strMessage
myOLItem.Display
myOLItem.Send
myOLApp.Quit
End Function


End Sub

I'm getting an automatic 'compile error - expected end of sub' right now as I don't believe I've put the function coding in the right place.

If you have any further guidance, I would greatly appreciate it!

Thanks again,
Jon

 

[rich.barry]

There are lots of ways of emailing things.
For what you are after, the most simple will probably be:

txtTO = [Customers]!
strMessage = frm![FirstName] & ", the order you submitted " _
& vbCr & "has been shipped out on " & frm.[ShipDate] _
& "Let me know if you have any question."

DoCmd.SendObject , , , txtto, , , , strmessage, False


Your Function didn't work because it is a stand alone section of code and does not belong inside a Sub, but the above may make it un-necessary.

 

[bricklebrit]

Hi Paul,

I appreciate your help. I pasted in the coding:

Private Sub PaidEmailButton_Click()

Dim txtTO As String
Dim strValue As String
Dim strName As String
Dim strMessage As String
Dim frm As Form

txtTO = [Customers]!
strMessage = frm![FirstName] & ", the order you submitted " _
& vbCr & "has been shipped out on " & frm.[ShipDate] _
& "Let me know if you have any questions."

DoCmd.SendObject , , , txtTO, , , , strMessage, False


End Sub

but I'm getting an error:
Run-time error '438':
Object doesn't support this property or method

and it's highlighting the line:
txtTO = [Customers]![Email]

Do you know why its returning this error to me or how I might avoid this?

Thanks again for your help.
Jon

 

[rich.barry]

You are probably pointing txtTO to the source table, where instead you must point it to the control on your form that is bound to the email field in your table.

Select the part of the form that has the email address, and look at the properties to see what the name is i.e. Text1
then set:

txtTo=Text1

 

[bricklebrit]

Thanks -- that was what is was. I've simplified the script to its bare minimum. I can get it to send an email message to values in the form (such as the OrderDate), but I'm having some problems with the syntax of trying to recover the value:
in the table [customers]

I thought the syntax would be:

Dim txtTo As String
Dim strValue As String
Dim strName As String
Dim strMessage As String

txtTo = [Customers].[Email]
strMessage = "Thanks for your order. It has been shipped out on " & [ShipDate]

DoCmd.SendObject , , , txtTo, , , , strMessage, False


--

I appreciate all your help and patience. Do you have any idea what the syntax should be for obtaining this email address?

Thanks again,
Jon

 

[rich.barry]

Persumably you have a table containing lots of customers, each with an email address.

The email you are sending is to a specific customer, for whom you persumably have the customer record active on your form when you go to send the email.

If the email address is also displayed on the form, then set txtTo=email control name

If you don't have the customer email on the form, then use dlookup to find it i.e. txtTo=dlookup("", "Customers", "[Customer] ="'" & me!CustomerControlName & "'")

or you could add an email control (textbox) to your form and set visible to false so you don't have to look at it.

Richard

 

[mzwaterski]

Problem using code

I'm trying to use the code that was posted, but I'm having trouble with the line

Set myOLApp = CreateObject("Outlook.Application")

The compiler is telling me that the variable myOLApp is not defined. Is there something special that I need to do. I also tried doing:

Dim myOLApp as Outlook.Application

This told me that the user defined type is not defined. Can you help?

Thanks,
Mike