D
Douglas J. Steele
Remove the line
sql = sql & Me.Charter_ID
sql = sql & Me.Charter_ID
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Apprentice
Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens up the
email with no To:
This is what I have:
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
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens up the
email with no To:
This is what I have:
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
A
Apprentice
Sorry I left off some information:
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
Apprentice said:Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens up the
email with no To:
This is what I have:
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
--
Your guidance is greatly appreciated!
Douglas J. Steele said:Remove the line
sql = sql & Me.Charter_ID
D
Douglas J. Steele
Are you trying to have wildcards in there? (You've got the two question
marks in what's in the text box.)
If so, you need
& "Where Charter_ID Like '" & Me.Charter_ID & "'"
instead of
& "Where Charter_ID ='" & Me.Charter_ID & "'"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
marks in what's in the text box.)
If so, you need
& "Where Charter_ID Like '" & Me.Charter_ID & "'"
instead of
& "Where Charter_ID ='" & Me.Charter_ID & "'"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Apprentice said:Sorry I left off some information:
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
Apprentice said:Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens up
the
email with no To:
This is what I have:
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
--
Your guidance is greatly appreciated!
Douglas J. Steele said:Remove the line
sql = sql & Me.Charter_ID
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
--
Your guidance is greatly appreciated!
:
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?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
--
Your guidance is greatly appreciated!
:
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.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
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
--
Your guidance is greatly appreciated!
:
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
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Doug, thanks alot!
I should have asked in the 1st post.
A
Apprentice
Sorry Doug, I mean I'm really sorry, but its still does not pull the "Select
Team_Leader_Email.... It has not email addresses in the to:
The email comes up, with the subject line.... but nothing pertaining to the
Charter_ID.
I made the last change as suggested. I normally will not have a wild card,
but I made the change anyway. I have been doing alot of testing and happened
to use ?? incorrectly. But I made the change anyway.
Is there something else I could be missing. I feel like I have messed this
up. We started with some good code that worked..... until I wanted to add
some other fields to the body of the email.
I'm determined to get this right..... with you help of course. Thanks for
hanging in there with me, I'm sure this is frustrateing to you as well.
Here is where we are:
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 Like '" & 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
The "Immediate window looks like this:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where Charter_ID
='CH2006??1'SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
Where Charter_ID Like 'CH2006??1'
Thanks for your time Doug.
-
Your guidance is greatly appreciated!
Team_Leader_Email.... It has not email addresses in the to:
The email comes up, with the subject line.... but nothing pertaining to the
Charter_ID.
I made the last change as suggested. I normally will not have a wild card,
but I made the change anyway. I have been doing alot of testing and happened
to use ?? incorrectly. But I made the change anyway.
Is there something else I could be missing. I feel like I have messed this
up. We started with some good code that worked..... until I wanted to add
some other fields to the body of the email.
I'm determined to get this right..... with you help of course. Thanks for
hanging in there with me, I'm sure this is frustrateing to you as well.
Here is where we are:
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 Like '" & 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
The "Immediate window looks like this:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where Charter_ID
='CH2006??1'SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM EmailTestCharterHeader
Where Charter_ID Like 'CH2006??1'
Thanks for your time Doug.
-
Your guidance is greatly appreciated!
Douglas J. Steele said:Are you trying to have wildcards in there? (You've got the two question
marks in what's in the text box.)
If so, you need
& "Where Charter_ID Like '" & Me.Charter_ID & "'"
instead of
& "Where Charter_ID ='" & Me.Charter_ID & "'"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Apprentice said:Sorry I left off some information:
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
Apprentice said:Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens up
the
email with no To:
This is what I have:
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
--
Your guidance is greatly appreciated!
:
Remove the line
sql = sql & Me.Charter_ID
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
--
Your guidance is greatly appreciated!
:
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?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
--
Your guidance is greatly appreciated!
:
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.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
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
The email comes up, with the subject line.... but nothing pertaining to
There's nothing in your code that tells it to put the Charter_ID in the
e-mail!
You need something like
Private Sub Command57_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Me.Dirty = False
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year, " & _
"Program_Reviewed, Expected_Review_Completion_Date " & _
"FROM EmailTestCharterHeader " & _
"WHERE Charter_ID LIKE '" & Me.Charter_ID & "'"
Debug.Print sql
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 " & Me.Charter_ID, _
"Your Charter has been recently updated", True
End Sub
Note that if you DO use a wildcard, you'll end up sending one e-mail to all
addresses, with the wildcard appearing in the subject. I don't know whether
that's what you intended...
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
the
Charter_ID.
There's nothing in your code that tells it to put the Charter_ID in the
e-mail!
You need something like
Private Sub Command57_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Me.Dirty = False
sql = "SELECT Team_Leader_Email, Review_Title, Fiscal_Year, " & _
"Program_Reviewed, Expected_Review_Completion_Date " & _
"FROM EmailTestCharterHeader " & _
"WHERE Charter_ID LIKE '" & Me.Charter_ID & "'"
Debug.Print sql
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 " & Me.Charter_ID, _
"Your Charter has been recently updated", True
End Sub
Note that if you DO use a wildcard, you'll end up sending one e-mail to all
addresses, with the wildcard appearing in the subject. I don't know whether
that's what you intended...
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Apprentice said:Sorry Doug, I mean I'm really sorry, but its still does not pull the
"Select
Team_Leader_Email.... It has not email addresses in the to:
The email comes up, with the subject line.... but nothing pertaining to
the
Charter_ID.
I made the last change as suggested. I normally will not have a wild
card,
but I made the change anyway. I have been doing alot of testing and
happened
to use ?? incorrectly. But I made the change anyway.
Is there something else I could be missing. I feel like I have messed
this
up. We started with some good code that worked..... until I wanted to add
some other fields to the body of the email.
I'm determined to get this right..... with you help of course. Thanks for
hanging in there with me, I'm sure this is frustrateing to you as well.
Here is where we are:
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 Like '" & 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
The "Immediate window looks like this:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'SELECT Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
Where Charter_ID Like 'CH2006??1'
Thanks for your time Doug.
-
Your guidance is greatly appreciated!
Douglas J. Steele said:Are you trying to have wildcards in there? (You've got the two question
marks in what's in the text box.)
If so, you need
& "Where Charter_ID Like '" & Me.Charter_ID & "'"
instead of
& "Where Charter_ID ='" & Me.Charter_ID & "'"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Apprentice said:Sorry I left off some information:
It also does not "Select Team_Leader_Email, Review_Title, Fiscal_Year,
Program_Reviewed, Expected_Review_Completion_Date FROM
EmailTestCharterHeader
" & "Where Charter_ID ='" & Me.Charter_ID & "'"
On the Dbug.Print sql...... it shows in the "Immediate" window of the
SQL
screen:
SELECT Team_Leader_Email, Review_Title, Fiscal_Year, Program_Reviewed,
Expected_Review_Completion_Date FROM EmailTestCharterHeader Where
Charter_ID
='CH2006??1'
--
Your guidance is greatly appreciated!
:
Thanks Doug, that cleared my error. Not it does not pull the
[Team_Leader_Email]address that relates to the [Charter_ID]. It opens
up
the
email with no To:
This is what I have:
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
--
Your guidance is greatly appreciated!
:
Remove the line
sql = sql & Me.Charter_ID
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
--
Your guidance is greatly appreciated!
:
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?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
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
--
Your guidance is greatly appreciated!
:
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.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
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
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.