Re: Email Subject Line

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
len.robichaud@xxxxxxxxxxxxxx so I'll be alerted to your message.

Len

"Stacey" <Stacey@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:18E46A27-AFC8-4664-B552-89FCD8969936@xxxxxxxxxxxxxxxx
Len, I get a "1" which is in the autonumber column.

"Len Robichaud" wrote:

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

'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

"Stacey" <Stacey@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:81D24D3E-138A-4941-A7E2-900824FE516C@xxxxxxxxxxxxxxxx
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
me@xxxxxxxxxxxxx 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" wrote:

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" <Stacey@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:25234EC9-4A78-4CEA-945E-95969C05DECF@xxxxxxxxxxxxxxxx
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









.

Quantcast