automated email Access to Outlook - how do I get email address?

[bricklebrit]

Hello,

I've cut together a short script to send automatic email messages from an order form in access (through Outlook). It is:



Private Sub EmailThem_Click()

DoCmd.SendObject , , acFormatRTF, "example@email.com", , , "Hello, " _
& Me![CustomerNumber].Value, "Dear " & Me![CustomerNumber].Value _
& "," & vbCrLf & vbCrLf & "Thanks for your order. It will be shipped out right away to " _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value _
& " " & vbCrLf & vbCrLf _
& "Please let me know if you have any questions." & vbCrLf & vbCrLf & "Thanks." & vbCrLf, False


End Sub


I would like to replace the "example@email.com" with the looked up value of customer's email address which doesn't exist on this form, but would be on the table [Customers].

Do I need to run a dynamic lookup to obtain thsi email address or is there another function I can use to access this?

Thanks in advance for any help!

 

[jimjones]

the following code will access the table directly, search for the customer number in the customers table and return the email in the table to the variable strCustomerEmail


dim dbs as database
dim rs as recordset
dim fiCustomerNumber as field
dim fiCustomerEmail as field
dim strCustomerEmail as string

set dbs = currentdb
set rs = dbs.openrecordset("Customers&quot

set fiCustomNumber = rs.fields("CustomerNumber&quot
set fiCustomerEmail = rs.fields("CustomerEmail&quot


rs.movefirst

do while not rs.eof

if fiCustomerNumber = Me![CustomerNumber].Value then

strCustomerEmail = fiCustomerEmail

end if


rs.movenext

loop



then add your code replacing email with the variable strCustomerEmail as below

DoCmd.SendObject , , acFormatRTF, strCustomerEmail, , ,

then close the recordset

rs.close

hope this helps
jim
ps i haven't tested this code

 

[bricklebrit]

Thanks for your prompt reply.

I'm getting a error:
Run-time '91' Object variable or With block varialbe not set

at the line:

If fiCustomerNumber = Me![CustomerNumber].Value Then

Any ideas what the problem is?

Thanks!
Jon

---

My complete code is:
Private Sub EmailThem_Click()

Dim dbs As Database
Dim rs As Recordset
Dim fiCustomerNumber As Field
Dim fiCustomerEmail As Field
Dim strCustomerEmail As String

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Customers&quot

Set fiCustomNumber = rs.Fields("CustomerNumber&quot
Set fiCustomerEmail = rs.Fields("Email&quot


rs.MoveFirst

Do While Not rs.EOF

If fiCustomerNumber = Me![CustomerNumber].Value Then

strCustomerEmail = fiCustomerEmail

End If


rs.MoveNext

Loop


DoCmd.SendObject , , acFormatRTF, strCustomerEmail, , , "Hello, " _
& Me![CustomerNumber].Value, "Dear " & Me![CustomerNumber].Value _
& "," & vbCrLf & vbCrLf & "Thanks for your order. It will be shipped out right away to " _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value _
& " " & vbCrLf & vbCrLf _
& "Please let me know if you have any questions." & vbCrLf & vbCrLf & "Thanks." & vbCrLf, False

rs.Close


End Sub

 

[KenReay]

Hi

1. Change you Subroutine to:

Private Sub EmailThem_Click(strEMail As String)

and in the line where the EMail address is used replace you literal with strEMail.

To look up the EMail address you need a variation on the folloing (using your column names, table table etc)

Dim Db As Database
Dim Rs as Recordset

Set Db = CurrentDb()
Set Rs = db.OpenRecordset("SELECT EMail FROM tblCustomer WHERE CustId = " & lngCustId & ";&quot
If Rs.Recordcount = 0 Then
msgbox "Not Found ... etc what ever you want to do in that situation
Else
EmailThem strEMail
End If

 

[bricklebrit]

Hi,

Thanks for the additional input. Unfortunately I'm getting an error:

The expression on click you entered as the event property setting produced the following error: procedure declaration does not match description of event or procedure having the same name.

The script I've modified is now:

Private Sub EmailThem_Click(strEMail As String)



Dim Db As Database
Dim rs As Recordset

Set Db = CurrentDb()
Set rs = Db.OpenRecordset("SELECT email FROM customers WHERE CustId = " & CustomerNumber & ";&quot
If rs.RecordCount = 0 Then
MsgBox "Email Address not found"
Else
EmailThem strEMail
End If


DoCmd.SendObject , , acFormatRTF, strEMail, , , "Hello, " _
& Me![CustomerNumber].Value, "Dear " & Me![CustomerNumber].Value _
& "," & vbCrLf & vbCrLf & "Thanks for your order. It will be shipped out right away to " _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value _
& " " & vbCrLf & vbCrLf _
& "Please let me know if you have any questions." & vbCrLf & vbCrLf & "Thanks." & vbCrLf, False

rs.Close


End Sub


=====

The values I want are from the table [customers] linked by the common value [customerNumber]

Any thoughts on how to resolve this are most appreciated!

Thanks again for your help!

 

[KenReay]

Hi

I did not immediately realise you had the EMail routine embedded in the button click event,

try

Private Sub EmailThem_Click()



Dim Db As Database
Dim rs As Recordset

Set Db = CurrentDb()
Set rs = Db.OpenRecordset("SELECT email FROM customers WHERE CustId = " & CustomerNumber & ";&quot
If rs.RecordCount = 0 Then
MsgBox "Email Address not found"
End If


DoCmd.SendObject , , acFormatRTF, rs!EMail, , , "Hello, " _
& Me![CustomerNumber].Value, "Dear " & Me![CustomerNumber].Value _
& "," & vbCrLf & vbCrLf & "Thanks for your order. It will be shipped out right away to " _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & vbCrLf _
& " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value & " " & Me![CustomerNumber].Value _
& " " & vbCrLf & vbCrLf _
& "Please let me know if you have any questions." & vbCrLf & vbCrLf & "Thanks." & vbCrLf, False

rs.Close


End Sub