Access/Outlook 2000 email question

R

robert

Here is my script:

Dim email As String
email = Me.emailADDRESS

Dim contactID As String
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert " & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf
body = body & " Amount Paid: " & amountPAID & vbCr & vbLf ***Here
is the error*****

DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the Business Office", body, True
End Sub

Issue:
The query pulls from 2 tables contact_TBL and payment_TBL, the
'amountPAID' column is in the payment table, everything else in the
script/email above comes from the contact_TBL, the email works when
you try to send it but the last line is just blank it says:

.....Regluar membershp valid through 1/1/07
Amount Paid:

....

And thats it it is just blank, I cannot figure out how to tell access
to pull the information from the payment_TBL (amountPAID)

Any suggestions? Thanks in advance.
 
R

robert

Here is my script:

Dim email As String
email = Me.emailADDRESS

Dim contactID As String
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert " & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf
body = body & " Amount Paid: " & amountPAID & vbCr & vbLf ***Here
is the error*****

DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the Business Office", body, True
End Sub

Issue:
The query pulls from 2 tables contact_TBL and payment_TBL, the
'amountPAID' column is in the payment table, everything else in the
script/email above comes from the contact_TBL, the email works when
you try to send it but the last line is just blank it says:

....Regluar membershp valid through 1/1/07
Amount Paid:

...

And thats it it is just blank, I cannot figure out how to tell access
to pull the information from the payment_TBL (amountPAID)

Any suggestions? Thanks in advance.
Click to expand...

Just one addition, the 'amountPAID' field is in the query as are other
fields from the payment_TBL but the email will not send any
information from that table, it just leaves it blank.
 
G

Guest

Robert, I am only guessing but do you need to tell access where the field
is coming from "Me.amountPAID" like you told it where the other fields were
coming from? (If Me. is the source for the other fields? otherwise the query
name?
Hopefully one of the MVP's will let us both know what the correct answer is.
But until they answer try using a message box to display the field,
MsgBox "amountPAID = " & Me.amountPaid, vbOKOnly
First try to display one of the fields that is working and then the field
that isn't.

--
Thanks for your Help and Patience.


robert said:
Here is my script:

Dim email As String
email = Me.emailADDRESS

Dim contactID As String
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert " & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf
body = body & " Amount Paid: " & amountPAID & vbCr & vbLf ***Here
is the error*****

DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the Business Office", body, True
End Sub

Issue:
The query pulls from 2 tables contact_TBL and payment_TBL, the
'amountPAID' column is in the payment table, everything else in the
script/email above comes from the contact_TBL, the email works when
you try to send it but the last line is just blank it says:

....Regluar membershp valid through 1/1/07
Amount Paid:

...

And thats it it is just blank, I cannot figure out how to tell access
to pull the information from the payment_TBL (amountPAID)

Any suggestions? Thanks in advance.
Click to expand...

Just one addition, the 'amountPAID' field is in the query as are other
fields from the payment_TBL but the email will not send any
information from that table, it just leaves it blank.
Click to expand...
 
A

Arvin Meyer [MVP]

Robert, you've looped to nowhere and you've closed the recordset. Take this
out:

Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

And set a reference to DAO and change your code to this (simpler):

Dim rst1 As DAO.Recordset
rst1 = CurrentDb.OpenRecordset("Select * From member_QRY Where ContactID ="
& Me.contactID)

and change this:

body = body & " Amount Paid: " & amountPAID & vbCr & vbLf

to:

body = body & " Amount Paid: " & rst1!amountPAID & vbCr & vbLf
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

robert said:
Here is my script:

Dim email As String
email = Me.emailADDRESS

Dim contactID As String
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert " & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf
body = body & " Amount Paid: " & amountPAID & vbCr & vbLf ***Here
is the error*****

DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the Business Office", body, True
End Sub

Issue:
The query pulls from 2 tables contact_TBL and payment_TBL, the
'amountPAID' column is in the payment table, everything else in the
script/email above comes from the contact_TBL, the email works when
you try to send it but the last line is just blank it says:

....Regluar membershp valid through 1/1/07
Amount Paid:

...

And thats it it is just blank, I cannot figure out how to tell access
to pull the information from the payment_TBL (amountPAID)

Any suggestions? Thanks in advance.
Click to expand...

Just one addition, the 'amountPAID' field is in the query as are other
fields from the payment_TBL but the email will not send any
information from that table, it just leaves it blank.
Click to expand...
 
T

Tom Wimpernads

SQL Server is much more powerful with xp_sendmail


and you're not going to open a security hole on 100 different desktops if
you use secure email on a database SERVER

and you can email query results without writing VB like this


I mean get real kids



robert said:
Here is my script:

Dim email As String
email = Me.emailADDRESS

Dim contactID As String
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst1.Open "member_QRY", cnn, adOpenKeyset, adLockOptimistic

Do Until rst1.EOF
contactID = rst1![contactID]
rst1.MoveNext
Loop

rst1.Close

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert " & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf
body = body & " Amount Paid: " & amountPAID & vbCr & vbLf ***Here
is the error*****

DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the Business Office", body, True
End Sub

Issue:
The query pulls from 2 tables contact_TBL and payment_TBL, the
'amountPAID' column is in the payment table, everything else in the
script/email above comes from the contact_TBL, the email works when
you try to send it but the last line is just blank it says:

....Regluar membershp valid through 1/1/07
Amount Paid:

...

And thats it it is just blank, I cannot figure out how to tell access
to pull the information from the payment_TBL (amountPAID)

Any suggestions? Thanks in advance.
Click to expand...

Just one addition, the 'amountPAID' field is in the query as are other
fields from the payment_TBL but the email will not send any
information from that table, it just leaves it blank.
Click to expand...
 
R

robert

Something is still missing from the code, I may have typed it wrong,
I'm getting 'compile error: User-defined type not defined'

Private Sub Command97_Click()
Dim email As String
email = Me.emailADDRESS

Dim contactID As String


Dim rst1 As DAO.Recordset
rst1 = CurrentDb.OpenRecordset("Select * From member_QRY Where
ContactID =" & Me.contactID)

Dim body As String
body = "Dear " & Me.firstNAME & ","
body = body & vbCr & vbLf & vbCr & vbLf & "ILTA has received your
membership dues payment. Please verify the information below and send
any changes or updates to (e-mail address removed)"
body = body & vbCr & vbLf & vbCr & vbLf
body = body & "Regards," & vbCr & vbLf
body = body & "Robert Ranieri" & vbCr & vbLf
body = body & "Account Manager" & vbCr & vbLf
body = body & "International Language Testing Association" & vbCr
& vbLf & vbCr & vbLf
body = body & "P: 001-205-823-6106" & vbCr & vbLf
body = body & "F: 001-205-823-2760" & vbCr & vbLf
body = body & "W: www.iltaonline.org" & vbCr & vbLf
body = body & "E: (e-mail address removed)" & vbCr & vbLf & vbCr &
vbLf
body = body & "Contact Information:" & vbCr & vbLf
body = body & Me.firstNAME & " " & Me.lastNAME & vbCr & vbLf
body = body & Me.address1 & vbCr & vbLf
body = body & Me.address2 & vbCr & vbLf
body = body & Me.address3 & vbCr & vbLf
body = body & Me.cityNAME & " " & Me.stateNAME & " " &
Me.zipPOSTAL & " " & Me.countryNAME & vbCr & vbLf & vbCr & vbLf
body = body & "Phone: " & Me.workPHONE & vbCr & vbLf
body = body & "Email: " & Me.emailADDRESS & vbCr & vbLf & vbCr &
vbLf
body = body & Me.memberTYPE & " Membership Valid Through: " &
dateEXP & vbCr & vbLf


DoCmd.SendObject acSendNoObject, , , email, , , "A message from
the ILTA Business Office", body, True
End Sub
 
D

Douglas J. Steele

What line of code gives you the error? If it's the "Dim rst1 As
DAO.Recordset" line, make sure you have a reference set to DAO.

While in the VB Editor, go to Tools | References on the menu. Examine the
selected references (they're the checked ones at the top of the list). If
"Microsoft DAO 3.6 Object Library" doesn't appear, scroll through the list
until you find it, check it to select it, then click on OK to close the
dialog.
 
R

robert

Thanks for your help all, getting closer, I've checked the specified
box on references and now I get a 'compile error: invalid use of
property' error on this line:

rst1 = CurrentDb.OpenRecordset("Select * From member_QRY Where
ContactID = " & Me.contactID)
 
Top