Email Subject Line

S

Stacey

Can someone please help me? I'm trying to include a field (OpCo) from a
table that corresponds to an individuals email address; this is what I have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
 
L

Len Robichaud

You don't say what isn't working. The only thing that pops out at me is in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud
 
S

Stacey

Len the part that isn't working is the subject field, I am trying to get it
to change along with the email name so that if it sends something to
(e-mail address removed) the subject line which is in another column which indicates
the location...I would like for that part to appear in the subject line in
the email. Does that make sense?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Stacey said:
Can someone please help me? I'm trying to include a field (OpCo) from a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
 
S

Stacey

oh and the outstanding field is a check box.

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Stacey said:
Can someone please help me? I'm trying to include a field (OpCo) from a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
 
S

Stacey

Len, I think the problem is that the field "OpCo" is a combo box...any
suggestions on how I could correct my code to incorporate this?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Stacey said:
Can someone please help me? I'm trying to include a field (OpCo) from a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
 
L

Len Robichaud

Ok, let's step through this and find out where it is breaking down. If your
email addresses are changing then the subjects should change, too. So, I
suspect we are not getting them in the first place.
If you know how to set a breakpoint set one at DoWhile and then step through
the code examing each of the variables where OpCo is assigned. If you are
not familiar with breakpoints yet, use message boxes as shown below.
Let me know what you find.

Len

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail

'This Message Box will tell you if the strOpCo was passed into the procedure
MSGBOX strOpCo

strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
Click to expand...
Click to expand...
Click to expand...

'This Message Box will tell you if the strOpCo was returned from the SQL
MSGBOX rst.Fields("OpCo")

Stacey said:
Len the part that isn't working is the subject field, I am trying to get
it
to change along with the email name so that if it sends something to
(e-mail address removed) the subject line which is in another column which
indicates
the location...I would like for that part to appear in the subject line in
the email. Does that make sense?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is
in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be
omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Stacey said:
Can someone please help me? I'm trying to include a field (OpCo) from
a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String)
As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 
L

Len Robichaud

Is yor combo box based on a lookup table? With the primary key hidden (like
a good combo box should)?
We'll need to join that table to your select statement and return the
Description instead of the key that is probably stored in the table.
What values are showing up in the message boxes from my last response?

Len Robichaud
(e-mail address removed)


Stacey said:
Len, I think the problem is that the field "OpCo" is a combo box...any
suggestions on how I could correct my code to incorporate this?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is
in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be
omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Stacey said:
Can someone please help me? I'm trying to include a field (OpCo) from
a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String)
As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

pietlinden

Len the part that isn't working is the subject field, I am trying to get it
to change along with the email name so that if it sends something to
(e-mail address removed) the subject line which is in another column which indicates
the location...I would like for that part to appear in the subject line in
the email. Does that make sense?
Click to expand...

If you're looping through the recordset, you have access to all the
fields in that result set. Just specify the subject of your message
something like this:

strSubject = "Hello, " & rs.Fields("FirstName") & ", HAPPY BIRTHDAY!!"
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject
 
P

pietlinden

If it's a combobox and you're calling the code from that form, then
just use
Me.Controls("NameOfYourCombobox") in the place of that argument in the
SendObject method. Note that it will send the contents of the left-
most column, whether it's being displayed or not, so if you need
something other than the first column, you need to refer to it like
this:

Me.Controls("MyCombo").Columns(1)

columns are numbered starting at zero, so subtract one from the
ordinal position.
 
S

Stacey

Len, I get a "1" which is in the autonumber column.

Len Robichaud said:
Ok, let's step through this and find out where it is breaking down. If your
email addresses are changing then the subjects should change, too. So, I
suspect we are not getting them in the first place.
If you know how to set a breakpoint set one at DoWhile and then step through
the code examing each of the variables where OpCo is assigned. If you are
not familiar with breakpoints yet, use message boxes as shown below.
Let me know what you find.

Len

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail

'This Message Box will tell you if the strOpCo was passed into the procedure
MSGBOX strOpCo

strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
Click to expand...
Click to expand...

'This Message Box will tell you if the strOpCo was returned from the SQL
MSGBOX rst.Fields("OpCo")

Stacey said:
Len the part that isn't working is the subject field, I am trying to get
it
to change along with the email name so that if it sends something to
(e-mail address removed) the subject line which is in another column which
indicates
the location...I would like for that part to appear in the subject line in
the email. Does that make sense?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is
in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be
omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Can someone please help me? I'm trying to include a field (OpCo) from
a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String)
As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 
L

Len Robichaud

OK here is the easiest way. Create a new query (call it qryEmailSource)
that contains tblforecastnames and the Lookup table. Join the two tables
using OpCo and the Lookup Table Autonumber. Add the Email and Outstanding
Fields from tblforecastnames and the Description field from the Lookup table
(We'll call it OpCoDesc), set the criteria for Outstanding to True (-1).
Save the query.

Now either:
a - Switch the Query view from design view to SQL, copy the SQL Statement
and paste over your current strSQL or
b - Change Set rst = dbs.OpenRecordset(strSQL) to Set rst =
dbs.OpenRecordset ("qryEmailSource")
Then change strOpCo = rst.Fields("OpCo") to strOpCo = rst.Fields("OpCoDesc")

You should be good to go from there.

If you need more help tonight, post to the group but cc me at
(e-mail address removed) so I'll be alerted to your message.

Len

Stacey said:
Len, I get a "1" which is in the autonumber column.

Len Robichaud said:
Ok, let's step through this and find out where it is breaking down. If
your
email addresses are changing then the subjects should change, too. So, I
suspect we are not getting them in the first place.
If you know how to set a breakpoint set one at DoWhile and then step
through
the code examing each of the variables where OpCo is assigned. If you
are
not familiar with breakpoints yet, use message boxes as shown below.
Let me know what you find.

Len

Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail

'This Message Box will tell you if the strOpCo was passed into the
procedure
MSGBOX strOpCo

strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
Click to expand...

'This Message Box will tell you if the strOpCo was returned from the SQL
MSGBOX rst.Fields("OpCo")
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...

Stacey said:
Len the part that isn't working is the subject field, I am trying to
get
it
to change along with the email name so that if it sends something to
(e-mail address removed) the subject line which is in another column which
indicates
the location...I would like for that part to appear in the subject line
in
the email. Does that make sense?

:

You don't say what isn't working. The only thing that pops out at me
is
in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be
omproved
but nothing that shouldn;t work as written (let's get inot those after
we
solve the main problem.)

Len Robichaud

Can someone please help me? I'm trying to include a field (OpCo)
from
a
table that corresponds to an individuals email address; this is what
I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As
String)
As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , ,
strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 
S

Stacey

Len, as soon as you mentioned the primary key, I knew that was the problem (I
can't believe I overlooked it)....really thanks for showing me how to isolate
a coding problem and for taking the time to help me. I really do sincerely
appreciate the help you provided me. Thanks!

And Pietlinden thanks to you for responding and offering your advice….I’m
sure it would have worked as well.


Len Robichaud said:
Is yor combo box based on a lookup table? With the primary key hidden (like
a good combo box should)?
We'll need to join that table to your select statement and return the
Description instead of the key that is probably stored in the table.
What values are showing up in the message boxes from my last response?

Len Robichaud
(e-mail address removed)


Stacey said:
Len, I think the problem is that the field "OpCo" is a combo box...any
suggestions on how I could correct my code to incorporate this?

Len Robichaud said:
You don't say what isn't working. The only thing that pops out at me is
in
your WHERE statement... is OU(t)standing mis-spelled?
there are a few other unnecessary things in your code that could be
omproved
but nothing that shouldn;t work as written (let's get inot those after we
solve the main problem.)

Len Robichaud

Can someone please help me? I'm trying to include a field (OpCo) from
a
table that corresponds to an individuals email address; this is what I
have
so far.

Function ForecastnotificationFC(strOpCo As String, strEmail As String)
As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Attachments 1
Attachments 4
Dlookup Frustration 2
Qualifier Must Be Collection 1
Help with VB 4
Using SendObject from Access 07 - need help with OL 07 warning 1
Need help with looping through records to email report 7
email report based on unique ID 0

Top