Sending E-mails/ Windox XP

D

D

Hi:

I am using the below code to generate e-mail confirmations; this Db/code is
working fine with Access 97/ and 2000; under Windos XP, when I ma trying to
send e-mails, it genrates just one e-mail; does not show/generates the
others???

Any help will be appreciate it!

Thank you,

dan


****

Sub SendFxEMail(Myform As Form, ToList As String, Optional CCList As Variant)
' Send Confirmation
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb

Dim RecSelected As String
Dim strIntroParagraph As String
Dim strEmailContent As String
Dim strSubjectHeader As String
Dim sOrg As String
Dim sFXLeg As String


RecSelected = Myform!ListBox_Transaction.Column(0)
sOrg = Myform!ListBox_Transaction.Column(15)
Set rs = db.OpenRecordset("SELECT * FROM tbl_Input_FX WHERE
tbl_Input_FX.[TRADE_NO] = " & RecSelected _
& " AND tbl_Input_FX.[ORG] = '" & sOrg & "';")

If rs.BOF Then Exit Sub
rs.MoveFirst
Do Until rs.EOF
If rs![TRADE_NO] = RecSelected Then
If rs![CURR_STAT] <> "CONFIRM" Then
MsgBox "THE TRADE HAS NOT CONFIRMED, PLEASE CONFIRM BEFORE
SENDING OUT E-MAIL"
' Exit Sub
Else
Exit Do
End If
End If
rs.MoveNext
Loop
rs.MoveFirst

Do Until rs.EOF
If rs![TRADE_NO] = RecSelected Then
If rs![FX_TYPE] = "Spot" Then
strSubjectHeader = Left(rs![DEALNO], 7)
strEmailContent = IntroParagraphType(rs![COUNTR_NM], rs![ORG])
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + "Trade details are as
follows:"
strEmailContent = strEmailContent + Chr(13) + Chr(13)
strEmailContent = strEmailContent + "Ref:" + Space(20) +
Left(rs![DEALNO], 7) + Chr(13)
strEmailContent = strEmailContent + "Trade date:" +
Space(13) + Format(rs![TRADE_DATE], "mmm dd, yyyy") + Chr(13)
strEmailContent = strEmailContent + "Value date:" +
Space(13) + Format(rs![START_DATE], "mmm dd, yyyy") + Chr(13)
strEmailContent = strEmailContent + "Spot rate:" + Space(14)
+ Format(rs![FX_SPOT_RATE], "#,###.0000000") + Chr(13)
If rs![TRANS_TP] = "BUY" Then
strEmailContent = strEmailContent + "Buy / Receive:" +
Space(10) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![TRANS_AMT]), "###,###.00") + Chr(13)
strEmailContent = strEmailContent + "Sell / Pay:" +
Space(13) + rs![CCY_AGAINST] + Space(18 - Len(Format(Abs(rs![VS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") + Chr(13)
ElseIf rs![TRANS_TP] = "SELL" Then
strEmailContent = strEmailContent + "Buy / Receive:" +
Space(10) + rs![CCY_AGAINST] + Space(18 - Len(Format(Abs(rs![VS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") + Chr(13)
strEmailContent = strEmailContent + "Sell / Pay:" +
Space(13) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![TRANS_AMT]), "###,###.00") + Chr(13)
End If
strEmailContent = strEmailContent + Chr(13) + Chr(13)
strEmailContent = strEmailContent + "Thank you" + Chr(13)
strEmailContent = strEmailContent + "Rosie Brunsek" + Chr(13)
strEmailContent = strEmailContent + "CIBC TBM - Middle
Office" + Chr(13)
strEmailContent = strEmailContent + "Tel: (416) 594-7030" +
Chr(13)
strEmailContent = strEmailContent + "Fax: (416) 956-3608" +
Chr(13)
ElseIf rs![FX_TYPE] = "Forward" Then
sOrg = rs![ORG]
sFXLeg = rs![FX_LEG]
strSubjectHeader = Left(rs![DEALNO], 7)
strEmailContent = IntroParagraphType(rs![COUNTR_NM], sOrg)
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + Chr(13)
strEmailContent = strEmailContent + "Trade details are as
follows:"
strEmailContent = strEmailContent + Chr(13) + Chr(13)
strEmailContent = strEmailContent + "Ref:" + Space(20) +
Left(rs![DEALNO], 7) + Chr(13)
strEmailContent = strEmailContent + "Trade date:" +
Space(13) + Format(rs![TRADE_DATE], "mmm dd, yyyy") + Chr(13)
strEmailContent = strEmailContent + "Value date:" +
Space(13) + Format(rs![START_DATE], "mmm dd, yyyy") + Chr(13)
strEmailContent = strEmailContent + "Spot rate:" + Space(14)
+ Format(rs![FX_SPOT_RATE], "#,###.0000000") + Chr(13)
If rs![TRANS_TP] = "BUY" Then
strEmailContent = strEmailContent + "Buy / Receive:" +
Space(10) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![TRANS_AMT]), "###,###.00") + Chr(13)
strEmailContent = strEmailContent + "Sell / Pay:" +
Space(13) + rs![CCY_AGAINST] + Space(18 - Len(Format(Abs(rs![VS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") + Chr(13)
ElseIf rs![TRANS_TP] = "SELL" Then
strEmailContent = strEmailContent + "Sell / Pay:" +
Space(13) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![TRANS_AMT]), "###,###.00") + Chr(13)
strEmailContent = strEmailContent + "Buy / Receive:" +
Space(10) + rs![CCY_AGAINST] + Space(18 - Len(Format(Abs(rs![VS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") + Chr(13)
End If
strEmailContent = strEmailContent + Chr(13) + Chr(13)
rs.MoveNext
If rs![TRADE_NO] = RecSelected Then
If rs![FX_TYPE] = "Forward" And Trim(rs![ORG]) =
Trim(sOrg) Then

strEmailContent = strEmailContent + Chr(13) + Chr(13)
strEmailContent = strEmailContent + "Ref:" +
Space(20) + Left(rs![DEALNO], 7) + Chr(13)
strEmailContent = strEmailContent + "Value date:" +
Space(13) + Format(rs![MATURITY_DATE], "mmm dd, yyyy") + Chr(13)
strEmailContent = strEmailContent + "Forward rate:"
+ Space(11) + Format(rs![FX_FWD_RATE], "#,###.0000000") + Chr(13)
If rs![TRANS_TP] = "BUY" Then
strEmailContent = strEmailContent + "Buy /
Receive:" + Space(10) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) _
+ Format(Abs(rs![TRANS_AMT]), "###,###.00")
+ Chr(13)
strEmailContent = strEmailContent + "Sell /
Pay:" + Space(13) + rs![CCY_AGAINST] + Space(18 -
Len(Format(Abs(rs![VS_AMT]), "###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") +
Chr(13)
ElseIf rs![TRANS_TP] = "SELL" Then
strEmailContent = strEmailContent + "Sell /
Pay:" + Space(13) + rs![CCY] + Space(18 - Len(Format(Abs(rs![TRANS_AMT]),
"###,###.00"))) + _
Format(Abs(rs![TRANS_AMT]), "###,###.00") +
Chr(13)
strEmailContent = strEmailContent + "Buy /
Receive:" + Space(10) + rs![CCY_AGAINST] + Space(18 -
Len(Format(Abs(rs![VS_AMT]), "###,###.00"))) _
+ Format(Abs(rs![VS_AMT]), "###,###.00") +
Chr(13)
End If
strEmailContent = strEmailContent + Chr(13) + Chr(13)
strEmailContent = strEmailContent + "Thank you" +
Chr(13)
strEmailContent = strEmailContent + "Rosie Brunsek"
+ Chr(13)
strEmailContent = strEmailContent + "CIBC TBM -
Middle Office" + Chr(13)
strEmailContent = strEmailContent + "Tel: (416)
594-7030" + Chr(13)
strEmailContent = strEmailContent + "Fax: (416)
956-3608" + Chr(13)
Exit Do
End If
End If
End If
Exit Do
End If
rs.MoveNext
Loop

On Error GoTo CloseOutLook:

DoCmd.SendObject acSendNoObject, , acFormatTXT, ToList, CCList, ,
strSubjectHeader, strEmailContent, True
' MsgBox strEmailContent

CloseOutLook:
DoCmd.Close acDefault, , acSavePrompt

' DoCmd.SendObject acSendNoObject, , acFormatTXT, sSendListName, sCc, ,
sSubjectHeader, _
strEmailContent, True
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.

Ask a Question

Similar Threads

Outlook 2010 - access 2010]Problems with ContactItem.LastModificationTime 0
Calling all Experts!!!! 2
Creating Folders 1
Losing the A: Drive. 2
creating new folders 2
Multiple Delimiters in Array 9
Workbook reminder 0
trouble printing variavel 1

Top