Emails from Access (Followup Post)

A

Apprentice

This is an unanswered post/repost pertaining to:

Post dated 5/7/2008
Subject: Emails from Access

This post was great information and helpful but:

I am using this code successfully and want to add a few bells and whistles.

1. I want the email to go out to only the current record, and not the
entire query. I think I can do this by just modifing the query.... Is that
the best way to approach this. I have several email address under one record.

2. I would also like to use this code in conjuction with an automatic email
based on a date field. Any suggestions?

Here is my current code:

Dim db As Database
Dim rs As Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar,,,"Charter Notification","Your
Charter has been recently updated",True
 
P

pietlinden

This is an unanswered post/repost pertaining to:

Post dated 5/7/2008
Subject:  Emails from Access

This post was great information and helpful but:

I am using this code successfully and want to add a few bells and whistles.

1. I want the email to go out to only the current record, and not the
entire query. I think I can do this by just modifing the query.... Is that
the best way to approach this. I have several email address under one record.

2. I would also like to use this code in conjuction with an automatic email
based on a date field. Any suggestions?

Here is my current code:

Dim db As Database
Dim rs As Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar,,,"Charter Notification","Your
Charter has been recently updated",True
Click to expand...

If you want to send to a single e-mail address at a time, then you
would put your DoCmd.SendObject *inside* your Do loop.

Do Until rs.EOF
DoCmd.SendObject acSendNoObject, , , rs!Team_Leader_Email,,,
"Charter Notification","Your Charter has been recently updated",True
rs.MoveNext
Loop
 
A

Apprentice

Thanks to both of you!....

The "Where" clause will probably do the trick, but now its hanging on this
line. I tried to comment the line out... no luck. Here is the line its
hanging on:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Here is the whole code.

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader Where Charter_ID
=" & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True

End Sub
 
D

Douglas J. Steele

Change your declaration to

Dim rs As DAO.Recordset

I'm assuming you've got a reference set to both ADO and DAO, with the ADO
reference being higher in the order. Recordset is an object in both the ADO
and DAO models, and if the ADO reference is higher, it gets precedence.
 
A

Apprentice

Thanks Douglas, I only have DAO referenced. Do I need ADO?

It's still hanging on this line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

I get this error:
Run-time Error '3061'
"Too few perameters. Expected 1"

Here is the whole code:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader Where Charter_ID
=" & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True

End Sub

Thanks Douglas.
 
D

Douglas J. Steele

Is Charter_ID a text field? If so, you need quotes around the value:

sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader " & _
"Where Charter_ID ='" & Me.Charter_ID & "'"

Exagerated for clarity, that second line is

"Where Charter_ID = ' " & Me.Charter_ID & " ' "
 
A

Apprentice

That worked great Doug, yes it was a text field.... Thanks!

If its not to much to ask, Is any of this possible?

1. I'd like to include a "memo field" from the same query, in the body of
the email. Not sure if its possible or how to go about it.

2. I have another query that is a date counter from specific due dates
(Date - 30, Date- 10 etc.). I'd like to send an automatice email from this
query to a list of recipiants when these dates occurr. Again I'm not sure
how to go about it.

Here is the successful/completed code you just helped me with:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader " & "Where
Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True

End Sub

Thanks Doug
 
D

Douglas J. Steele

For the first question, try something like:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strMemo As String

sql = "SELECT Team_Leader_Email, MyMemoField FROM EmailTestCharterHeader "
& _
"Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.EOF = False Then
strMemo = rs!MyMemoField
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , _
"Charter Notification", "Your Charter has been recently updated" &
strMemo, True
End If

End Sub

Not really sure what you're asking for in the second point.
 
R

RD

Since your OpenRecordset looks ok I would suspect a problem in the
SQL. What data type is Charter_ID? Is EmailTestCharterHeader a table
or a query?

What happens if you rewrite your Sub like this:
Private Sub Command57_Click()
Me.Dirty = False
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT [EmailTestCharterHeader].[Team_Leader_Email] "
sql = sql & "FROM EmailTestCharterHeader "
sql = sql & "Where [EmailTestCharterHeader].[Charter_ID] ="
sql = sql & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter
Notification",
"Your Charter has been recently updated", True

End Sub


Thanks Douglas, I only have DAO referenced. Do I need ADO?

It's still hanging on this line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

I get this error:
Run-time Error '3061'
"Too few perameters. Expected 1"

Here is the whole code:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email FROM EmailTestCharterHeader Where Charter_ID
=" & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True

End Sub

Thanks Douglas.
Click to expand...

** Posted from http://www.teranews.com **
 
A

Apprentice

Doug, thanks alot!

I should have asked in the 1st post.

1. What if I want to use a Test Field insead of a Memo. I assume that I
would just replace "Dim strMemo As String" with "Dim strText As String".

2. What if I wanted to include multiple "memo" and / or "text" fields in
the body of the email.

Here is what I have from your last post:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strMemo As String

sql = "SELECT Team_Leader_Email, Title FROM EmailTestCharterHeader "
& _
"Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.EOF = False Then
strMemo = rs!Title
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , _
"Charter Notification", "Your Charter has been recently updated" &
strMemo, True
End If

End Sub


Almost there..... thanks again.
 
D

Douglas J. Steele

The name of the variable is irrelevant.

SendObject only allows a single value to be passed for the body of the
report, so to include the results from multiple fields, you'd need to
concatenate them all together.

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strMemo As String
Dim strVariable1 As String
Dim strVariable2 As String

sql = "SELECT Team_Leader_Email, Title FROM EmailTestCharterHeader "
& _
"Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.EOF = False Then
strMemo = rs!Title
strVariable1 = rs!Field1
strVariable2 = rs!Field2
Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , _
"Charter Notification", "Your Charter has been recently updated" &
strMemo & " " & strField1 & " (" & strField2 & ")", True
End If

End Sub
 
A

Apprentice

Ok thanks, I'm getting it.

1. I'm not sure as to how the 4th field should look in the DoCmd Line.
2. Also I'm not sure if the SQL Select statement looks right. I added all
of the field names I'm trying to select.

Here is now what I have. I haven't tried the code yet, but I think I'm
getting closer.

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strTitle As String
Dim strFY As Integer
Dim strPR As String
Dim strDate As Date

sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.EOF = False Then
strTitle = rs!Review_Title
strFY = rs!Fiscal_Year
strPR = rs!Program_Reviewed
strDate = rs!Expected_Review_Completion_Date

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated" & strTitle & " " & strFY & " (" &
Program_Reviewed & ")", True
End If

End Sub
 
D

Douglas J. Steele

By "the 4th field", are you talking about where you've got ToVar? It should
be fine. The only change you might consider is removing the semi-colon from
the end of the string. In other words, between the Loop statement and the
DoCmd.SendObject statement, put

ToVar = Left(ToVar, Len(ToVar)-1)

I don't see anything wrong with the syntax of your SQL statement (I'm
assuming there's a space between EmailTestCharterHeader and the double
quote), but I can't comment whether it's correct. Only you know whether
you've got the correct names there.
 
A

Apprentice

Thanks Doug,

I am trying to add 4 fields in the email body:
Review_Title
Fiscal_Year
Program_Reviewed
Expected_Review_Completion_Date

As referenced like this:
strTitle = rs!Review_Title
strFY = rs!Fiscal_Year
strPR = rs!Program_Reviewed
strDate = rs!Expected_Review_Completion_Date

Here is the DoCmd Line that you suppled, and I'm not sure how to put the 4th
field (Expected_Review_Completion_Date) in this line. You used some
puntuation that I'm not familiar with.

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated" & strTitle & " " & strFY & " (" &
Program_Reviewed & ")", True
 
D

Douglas J. Steele

Sorry, the string I provided was simply intended to show options. My
original text was

"Your Charter has been recently updated" &strMemo & " " & strField1 & " (" &
strField2 & ")"

That would add the contents of strMemo immediately after the words "Your
Charter has been recently update" (with no space between), followed by a
space, followed by the contents of strField1, followed by a space and an
opening parenthesis, followed by the contents of strField2, followed by a
closing parenthesis.

If you don't want the parentheses, leave them out. If you want additional
fields, concatenate them using & (don't forget to include spaces between
them, or you won't be able to read them!)
 
A

Apprentice

Oh noooo!

What did I do now? I think my SQL SELECT statement is wacked.

Nothing happens when I click.

Sorry, can you help?

Here is the code:

Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strTitle As String
Dim strFY As String
Dim strPR As String
Dim strDate As Date

sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.EOF = False Then
strTitle = rs!Review_Title
strFY = rs!Fiscal_Year
strPR = rs!Program_Reviewed
strDate = rs!Expected_Review_Completion_Date

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

ToVar = Left(ToVar, Len(ToVar) - 1)

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated: Review Title:" & strTitle & "
Fiscal Year:" & strFY & " Program Reviewed:" & strPR & " Review Due Date:" &
strDate, True
End If

End Sub
 
D

Douglas J. Steele

Are you sure that you're searching for a valid Charter_ID? In your code,
after you set the value for sql, put the line

Debug.Print sql

Once your code runs (and nothing happens), go to the Immediate window
(Ctrl-G) and check the SQL. Copy it, and go to create a new query. Rather
than select a table or anything, select the SQL View of the query and paste
the SQL you got from the Immediate window. Run the query. Do you get
anything?
 
A

Apprentice

Well I'd like to, but now I get another error. I swear Doug, I'm not
changing things before you tell me too.

I put the Debug.Print sql where you said.

Now when I run the code, I get a syntex error:
Run-time error 3075:
Syntax error (missing operator) in query expression
'Charter_ID='CH2006GGG1'CH2006GGG1'.

The CH2006GGG1 is the current Charter_ID.

I'm trying Doug.
 
A

Apprentice

Oh here is the whole code once again:


Private Sub Command57_Click()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
Debug.Print sql
sql = sql & Me.Charter_ID
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!Team_Leader_Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "Charter Notification",
"Your Charter has been recently updated", True

End Sub

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto Email Help 3
Concencate Emails, Error type declaration does not match 8
Email from subform 2
Timer event does not fire 1
Need to create a dynamic recordset using generic field names 8
TransferSpreadsheet construction 5
Upsize to SQL ODBC error on rs!update 3
My string does not want to execute 5

Top