Not finding email address to send to

[Rain]

I am fairly new at VBA for Access. I have created codes to send emails. One
code picks a specific person to send an email to - I am getting a runtime
error that says "There must be at least one name or distribution list in the
To, Cc, or Bcc Box." Here is the code that looks for the email address:

Private Function HRRecip() As String

'Variable to define the recordset.
Dim myRS As DAO.Recordset

'Fill recordset with data from SQL.
Set myRS = CurrentDb.OpenRecordset(strSQLResult)
'Test if the recordset contains records.
If myRS.EOF And myRS.BOF Then
'No records found.
MsgBox "Your selection gave no rows, please try again."
GoTo exit_btnEmail_Click
End If

myRS.MoveFirst

'Loop through all records til end of file.
Do While Not myRS.EOF

If (myRS!Title) = "Admin Assistant" And (myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ")
End If

myRS.MoveNext
Loop

exit_btnEmail_Click:

'Close and clean up object variables, then done.
myRS.Close
Set myRS = Nothing

End Function

This is where I am having the problem:

With OlkHRMsg
'Make the "To" address of message.
Dim OlkHRRecip As Outlook.Recipient
Set OlkHRRecip = .Recipients.Add(HRRecip) <<<<<This line here says
"nothing"
OlkHRRecip.Type = olTo
.Subject = "Attendance Report"
'Need to specify HTML for body format.
.BodyFormat = olFormatHTML
'Must have Microsoft HTML Object Library checked in Tools/References.
.HTMLBody = strBodyHR
.send
End With

Please tell me what I am doing wrong. Thanks

 

[Damon Heron]

What happens when you put a breakpoint right here:

If (myRS!Title) = "Admin Assistant" And (myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ") ' is there a value?
End If

Damon

 

[Rain]

Give me myRS!Title = "Maintenance"

What happens when you put a breakpoint right here:

If (myRS!Title) = "Admin Assistant" And (myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ") ' is there a value?
End If

Damon

 

[Damon Heron]

Step thru the code using F8 key and check value of HRRecip when myRS!Title =
Admin Assistant
If there is a value for HRRecip then it should pass that value on.

Damon

 

[Rain]

I ran it again and it gave me a different answer. Both times the answer came
after Admin Assistant in the table. There is only one Admin Assistant listed.
It is not looking for that answer at all. What am I missing?

 

[Rain]

I don't think it is searching through the whole table. It is only looking at
a few of the items and they come at some point AFTER the one I am looking for
- regardless of how I sort the table.

 

[Damon Heron]

Oops! The Line :
HRRecip = (" & myRS!Email & ") should be
HRRecip = "(" & myRS!Email & ")"
if you want an answer like (emailaddress).
If you want something without the parens, try HRRecip = myRS!Email

I can't see your strSQLResults so I don't know how the fields are formatted.
If you go to the immediate window (with a breakpoint at the start of the
function) and type Call HRRecip, you can step thru the code and look at the
value as it reads the above line.- myRS!Email


Damon

 

[Rain]

i did Call HRRecip and got this error: Compile Error Sub or function not
defined

 

[Rain]

Here is my code for the sql results:
Private Function strSQLResult() As String

Dim strLog As String
strLog = "tblAttendanceLog"
Dim strEmp As String
strEmp = "tblEmployees"
Dim strCode As String
strCode = "tbllkupAttendanceCode"
Dim strCompName As String
strCompName = "tblCompanyInfo"

'SQL query to retrieve data.
strSQLResult = "SELECT [" & strLog & "].AttendanceCode, [" & strLog &
"].AttDate, [" & strLog & "].EmployeeID, [" & strLog & "].Count, [" & strLog
& "].TravelTo,"
strSQLResult = strSQLResult & " [" & strEmp & "].[Company Name], [" &
strEmp & "].[Office Location], [" & strEmp & "].[DateTerminated], [" & strEmp
& "].[Title], [" & strEmp & "]., [" & strEmp & "].[First Name], [" &
strEmp & "].[Last Name], [" & strEmp & "].reportto,"
strSQLResult = strSQLResult & " [" & strCode & "].attendancecode AS
viewcode, [" & strCode & "].catagory, [" & strCode & "].note"
strSQLResult = strSQLResult & " FROM [" & strCode & "] INNER JOIN ([" &
strEmp & "] INNER JOIN [" & strLog & "] ON [" & strEmp & "].EmployeeId = [" &
strLog & "].EmployeeID) ON [" & strCode & "].autonumber = [" & strLog &
"].AttendanceCode"
strSQLResult = strSQLResult & " WHERE (([" & strLog & "].AttDate = #" &
Date & "#)"
strSQLResult = strSQLResult & " AND ([" & strLog & "].AttendanceCode <>
17))"
strSQLResult = strSQLResult & " ORDER BY ([" & strEmp & "].[Company
Name])"

Debug.Print strSQLResult

End Function

It works fine for giving me all the data I need to put into the body of the
email, but it will not give me the email address. It did once before, but now
it doesn't. And I tried several ways for the myRS!Email too.

Every time I try to run the code, it starts at Maintenance and goes through
a bunch of other positions in the field Title. It does not see all of them.
And it definately does not see Admin Assistant. I am at a loss today on how
to procede. Everything works fine until it has to look for that one email
address. Thanks

 

[Damon Heron]

When you copy your strSQL code to the query window and run it, do you get
expected results?

Damon

Here is my code for the sql results:
Private Function strSQLResult() As String

Dim strLog As String
strLog = "tblAttendanceLog"
Dim strEmp As String
strEmp = "tblEmployees"
Dim strCode As String
strCode = "tbllkupAttendanceCode"
Dim strCompName As String
strCompName = "tblCompanyInfo"

'SQL query to retrieve data.
strSQLResult = "SELECT [" & strLog & "].AttendanceCode, [" & strLog &
"].AttDate, [" & strLog & "].EmployeeID, [" & strLog & "].Count, [" &
strLog
& "].TravelTo,"
strSQLResult = strSQLResult & " [" & strEmp & "].[Company Name], [" &
strEmp & "].[Office Location], [" & strEmp & "].[DateTerminated], [" &
strEmp
& "].[Title], [" & strEmp & "]., [" & strEmp & "].[First Name], ["
&
strEmp & "].[Last Name], [" & strEmp & "].reportto,"
strSQLResult = strSQLResult & " [" & strCode & "].attendancecode AS
viewcode, [" & strCode & "].catagory, [" & strCode & "].note"
strSQLResult = strSQLResult & " FROM [" & strCode & "] INNER JOIN ([" &
strEmp & "] INNER JOIN [" & strLog & "] ON [" & strEmp & "].EmployeeId =
[" &
strLog & "].EmployeeID) ON [" & strCode & "].autonumber = [" & strLog &
"].AttendanceCode"
strSQLResult = strSQLResult & " WHERE (([" & strLog & "].AttDate = #" &
Date & "#)"
strSQLResult = strSQLResult & " AND ([" & strLog & "].AttendanceCode <>
17))"
strSQLResult = strSQLResult & " ORDER BY ([" & strEmp & "].[Company
Name])"

Debug.Print strSQLResult

End Function

It works fine for giving me all the data I need to put into the body of
the
email, but it will not give me the email address. It did once before, but
now
it doesn't. And I tried several ways for the myRS!Email too.

Every time I try to run the code, it starts at Maintenance and goes
through
a bunch of other positions in the field Title. It does not see all of
them.
And it definately does not see Admin Assistant. I am at a loss today on
how
to procede. Everything works fine until it has to look for that one email
address. Thanks

[QUOTE="Damon Heron"]
Oops! The Line :
HRRecip = (" & myRS!Email & ") should be
HRRecip = "(" & myRS!Email & ")"
if you want an answer like (emailaddress).
If you want something without the parens, try HRRecip = myRS!Email

I can't see your strSQLResults so I don't know how the fields are
formatted.
If you go to the immediate window (with a breakpoint at the start of the
function) and type Call HRRecip, you can step thru the code and look at
the
value as it reads the above line.- myRS!Email


Damon
[/QUOTE][/QUOTE]

 

[Rain]

Ok maybe i'm not seeing it but which window is the query window? The
immediate window? Like I said I am fairly new. I know that I get the expected
results for the data put into the body of the email - employee, notes, etc. -
but when it comes to the function HRRecip it can't seem to look through the
whole table. Sometimes I have to sleep on the problem but I thought maybe you
might see something I'm not seeing. I will check back tomorrow on this.
Thanks hon.

When you copy your strSQL code to the query window and run it, do you get
expected results?

Damon

Here is my code for the sql results:
Private Function strSQLResult() As String

Dim strLog As String
strLog = "tblAttendanceLog"
Dim strEmp As String
strEmp = "tblEmployees"
Dim strCode As String
strCode = "tbllkupAttendanceCode"
Dim strCompName As String
strCompName = "tblCompanyInfo"

'SQL query to retrieve data.
strSQLResult = "SELECT [" & strLog & "].AttendanceCode, [" & strLog &
"].AttDate, [" & strLog & "].EmployeeID, [" & strLog & "].Count, [" &
strLog
& "].TravelTo,"
strSQLResult = strSQLResult & " [" & strEmp & "].[Company Name], [" &
strEmp & "].[Office Location], [" & strEmp & "].[DateTerminated], [" &
strEmp
& "].[Title], [" & strEmp & "]., [" & strEmp & "].[First Name], ["
&
strEmp & "].[Last Name], [" & strEmp & "].reportto,"
strSQLResult = strSQLResult & " [" & strCode & "].attendancecode AS
viewcode, [" & strCode & "].catagory, [" & strCode & "].note"
strSQLResult = strSQLResult & " FROM [" & strCode & "] INNER JOIN ([" &
strEmp & "] INNER JOIN [" & strLog & "] ON [" & strEmp & "].EmployeeId =
[" &
strLog & "].EmployeeID) ON [" & strCode & "].autonumber = [" & strLog &
"].AttendanceCode"
strSQLResult = strSQLResult & " WHERE (([" & strLog & "].AttDate = #" &
Date & "#)"
strSQLResult = strSQLResult & " AND ([" & strLog & "].AttendanceCode <>
17))"
strSQLResult = strSQLResult & " ORDER BY ([" & strEmp & "].[Company
Name])"

Debug.Print strSQLResult

End Function

It works fine for giving me all the data I need to put into the body of
the
email, but it will not give me the email address. It did once before, but
now
it doesn't. And I tried several ways for the myRS!Email too.

Every time I try to run the code, it starts at Maintenance and goes
through
a bunch of other positions in the field Title. It does not see all of
them.
And it definately does not see Admin Assistant. I am at a loss today on
how
to procede. Everything works fine until it has to look for that one email
address. Thanks

[QUOTE="Damon Heron"]
Oops! The Line :
HRRecip = (" & myRS!Email & ") should be
HRRecip = "(" & myRS!Email & ")"
if you want an answer like (emailaddress).
If you want something without the parens, try HRRecip = myRS!Email

I can't see your strSQLResults so I don't know how the fields are
formatted.
If you go to the immediate window (with a breakpoint at the start of the
function) and type Call HRRecip, you can step thru the code and look at
the
value as it reads the above line.- myRS!Email


Damon


I ran it again and it gave me a different answer. Both times the answer
came
after Admin Assistant in the table. There is only one Admin Assistant
listed.
It is not looking for that answer at all. What am I missing?

:

What happens when you put a breakpoint right here:

If (myRS!Title) = "Admin Assistant" And (myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ") ' is there a value?
End If

Damon


I am fairly new at VBA for Access. I have created codes to send
emails.
One
code picks a specific person to send an email to - I am getting a
runtime
error that says "There must be at least one name or distribution
list
in
the
To, Cc, or Bcc Box." Here is the code that looks for the email
address:

Private Function HRRecip() As String

'Variable to define the recordset.
Dim myRS As DAO.Recordset

'Fill recordset with data from SQL.
Set myRS = CurrentDb.OpenRecordset(strSQLResult)
'Test if the recordset contains records.
If myRS.EOF And myRS.BOF Then
'No records found.
MsgBox "Your selection gave no rows, please try again."
GoTo exit_btnEmail_Click
End If

myRS.MoveFirst

'Loop through all records til end of file.
Do While Not myRS.EOF

If (myRS!Title) = "Admin Assistant" And
(myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ")
End If

myRS.MoveNext
Loop

exit_btnEmail_Click:

'Close and clean up object variables, then done.
myRS.Close
Set myRS = Nothing

End Function

This is where I am having the problem:

With OlkHRMsg
'Make the "To" address of message.
Dim OlkHRRecip As Outlook.Recipient
Set OlkHRRecip = .Recipients.Add(HRRecip) <<<<<This line here
says
"nothing"
OlkHRRecip.Type = olTo
.Subject = "Attendance Report"
'Need to specify HTML for body format.
.BodyFormat = olFormatHTML
'Must have Microsoft HTML Object Library checked in
Tools/References.
.HTMLBody = strBodyHR
.send
End With

Please tell me what I am doing wrong. Thanks
[/QUOTE][/QUOTE]
[/QUOTE]

 

[Rain]

Thank you Damon for all your help!!! I knew if I slept on it that today I
might think better!! I got it!! I created a second SQL specifically looking
for the answer based on searching for the specifics. When the function ran,
it looked to see if it matched what the SQL found and there was my answer. I
could not put the specifics into the original SQL or it would mess up the
data that that SQL was searching for. Many thanks for your help!!

When you copy your strSQL code to the query window and run it, do you get
expected results?

Damon

Here is my code for the sql results:
Private Function strSQLResult() As String

Dim strLog As String
strLog = "tblAttendanceLog"
Dim strEmp As String
strEmp = "tblEmployees"
Dim strCode As String
strCode = "tbllkupAttendanceCode"
Dim strCompName As String
strCompName = "tblCompanyInfo"

'SQL query to retrieve data.
strSQLResult = "SELECT [" & strLog & "].AttendanceCode, [" & strLog &
"].AttDate, [" & strLog & "].EmployeeID, [" & strLog & "].Count, [" &
strLog
& "].TravelTo,"
strSQLResult = strSQLResult & " [" & strEmp & "].[Company Name], [" &
strEmp & "].[Office Location], [" & strEmp & "].[DateTerminated], [" &
strEmp
& "].[Title], [" & strEmp & "]., [" & strEmp & "].[First Name], ["
&
strEmp & "].[Last Name], [" & strEmp & "].reportto,"
strSQLResult = strSQLResult & " [" & strCode & "].attendancecode AS
viewcode, [" & strCode & "].catagory, [" & strCode & "].note"
strSQLResult = strSQLResult & " FROM [" & strCode & "] INNER JOIN ([" &
strEmp & "] INNER JOIN [" & strLog & "] ON [" & strEmp & "].EmployeeId =
[" &
strLog & "].EmployeeID) ON [" & strCode & "].autonumber = [" & strLog &
"].AttendanceCode"
strSQLResult = strSQLResult & " WHERE (([" & strLog & "].AttDate = #" &
Date & "#)"
strSQLResult = strSQLResult & " AND ([" & strLog & "].AttendanceCode <>
17))"
strSQLResult = strSQLResult & " ORDER BY ([" & strEmp & "].[Company
Name])"

Debug.Print strSQLResult

End Function

It works fine for giving me all the data I need to put into the body of
the
email, but it will not give me the email address. It did once before, but
now
it doesn't. And I tried several ways for the myRS!Email too.

Every time I try to run the code, it starts at Maintenance and goes
through
a bunch of other positions in the field Title. It does not see all of
them.
And it definately does not see Admin Assistant. I am at a loss today on
how
to procede. Everything works fine until it has to look for that one email
address. Thanks

[QUOTE="Damon Heron"]
Oops! The Line :
HRRecip = (" & myRS!Email & ") should be
HRRecip = "(" & myRS!Email & ")"
if you want an answer like (emailaddress).
If you want something without the parens, try HRRecip = myRS!Email

I can't see your strSQLResults so I don't know how the fields are
formatted.
If you go to the immediate window (with a breakpoint at the start of the
function) and type Call HRRecip, you can step thru the code and look at
the
value as it reads the above line.- myRS!Email


Damon


I ran it again and it gave me a different answer. Both times the answer
came
after Admin Assistant in the table. There is only one Admin Assistant
listed.
It is not looking for that answer at all. What am I missing?

:

What happens when you put a breakpoint right here:

If (myRS!Title) = "Admin Assistant" And (myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ") ' is there a value?
End If

Damon


I am fairly new at VBA for Access. I have created codes to send
emails.
One
code picks a specific person to send an email to - I am getting a
runtime
error that says "There must be at least one name or distribution
list
in
the
To, Cc, or Bcc Box." Here is the code that looks for the email
address:

Private Function HRRecip() As String

'Variable to define the recordset.
Dim myRS As DAO.Recordset

'Fill recordset with data from SQL.
Set myRS = CurrentDb.OpenRecordset(strSQLResult)
'Test if the recordset contains records.
If myRS.EOF And myRS.BOF Then
'No records found.
MsgBox "Your selection gave no rows, please try again."
GoTo exit_btnEmail_Click
End If

myRS.MoveFirst

'Loop through all records til end of file.
Do While Not myRS.EOF

If (myRS!Title) = "Admin Assistant" And
(myRS!DateTerminated) =
"12/31/9999" Then
HRRecip = (" & myRS!Email & ")
End If

myRS.MoveNext
Loop

exit_btnEmail_Click:

'Close and clean up object variables, then done.
myRS.Close
Set myRS = Nothing

End Function

This is where I am having the problem:

With OlkHRMsg
'Make the "To" address of message.
Dim OlkHRRecip As Outlook.Recipient
Set OlkHRRecip = .Recipients.Add(HRRecip) <<<<<This line here
says
"nothing"
OlkHRRecip.Type = olTo
.Subject = "Attendance Report"
'Need to specify HTML for body format.
.BodyFormat = olFormatHTML
'Must have Microsoft HTML Object Library checked in
Tools/References.
.HTMLBody = strBodyHR
.send
End With

Please tell me what I am doing wrong. Thanks
[/QUOTE][/QUOTE]
[/QUOTE]