Sendobject fails to send mail

  • Thread starter Thread starter Synergy
  • Start date Start date
S

Synergy

Hello,

This is among the myriad of problems I have encountered since linking to SQL
Server Tables. The following snippet shows the method then flagging the
email as being sent. Outlook 2000 is the Client and server.

DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT, vSendTo, ,
vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True


The entire method is below: Sometimes the email does not get sent, and no
error is generated, then it gets marked as being sent. This is a problem
for my client becuase they they can't take the time to call clients to see
if they received their confirmations.

Does anyone have any ideas on this???

Thanks and God Bless,

Mark A. Sam



On Error GoTo error_Section

'Check whether email has been sent for this order
If [ordConfEmailed] = True Then
If MsgBox("An Email has been sent for this order. Do you wish to resend
this confirmation?", vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
End If


Dim dbs As Database
Dim rsProds As Recordset
Dim rsCust As Recordset
Dim strCriteria As String
Dim vPreview As Boolean 'used to set the EditMessage argument for the
SendObject method
Dim vSendTo As String 'Used to set the recipient email addy for the
SendObject method
Dim vBCcTo As String 'Used to set the Blind CC for the SendObject method
Dim vDisclaimer As String

Set dbs = CurrentDb()
Set rsCust = dbs.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
Set rsProds = [ Products].Form.RecordsetClone


If rsProds.RecordCount = 0 Then 'If there are no products then the
confirmation isn't nesessary
MsgBox "There are no products entered in this order. Confirmation is
aborted."
Exit Sub
End If

'Determine if this is an Email or Fax send
strCriteria = "[CustID] = " & [ordCustID]
rsCust.FindFirst strCriteria
If rsCust.NoMatch Then
MsgBox "Customer Not Found. You must enter email address manually!"
[ViewEmail] = True
End If

Select Case rsCust![ConfMode]
Case 1 'Email Confirmation
' Redo***
vPreview = [ViewEmail] 'View email is a checkbox on the current form
vSendTo = Nz(rsCust![ConfEmail], "")
If IsNull(vSendTo) Or vSendTo = "" Then
MsgBox "No Confiramtion Email Address was set up for this customer.
You must enter email address manually!"
vPreview = True
End If

vBCcTo = "(e-mail address removed)"
vDisclaimer = "Merchandise covered by this confirmation is warranted to
be free from defects in workmanship " & _
"but not for any specific length of time, type or measure
of service. Claims for allowance will only " & _
"be recognized when presented in writing within 5 days of
receipt of material. No claims for labor, " & _
"transportation or consequential damages will be allowed.
The maximum liability for any claim predicated upon " & _
"defective merchandise is limited to replacement of
merchandise, or to repayment of the purchase price, " & _
"whichever may be elected by Synergy Tooling Systems,
Inc."


Dim strLineItems As String 'This is to hold the line item values from
the [ Products] subform
Dim vQty As String
Dim vPrice As String
Dim vExt As String
Dim vOrdTotal As String

'Create Email Subject line
Dim strSubject As String
strSubject = "Synergy Order Confirmation: " & [ordJob] & " PO: " &
[ordPO] & " Ref#: " & [ordCustRef]

'Create Email message
Dim strMessage As String
If Not IsNull(rsCust![ConfContact]) Then
strMessage = rsCust![ConfContact] & vbCrLf 'This is the first line
of the Message. If you move this you must remove the Null assignment below
Else
strMessage = ""
End If
strMessage = strMessage & [ordCustName] & vbCrLf & _
"PO Number: " & [ordPO] & vbCrLf & _
"Ref # " & [ordCustRef] & vbCrLf & _
"Required Date: " & [ordReqDate] & vbCrLf & _
"Synergy Order Number: " & [ordJob]

strLineItems = String(70, "-") & vbCrLf
strLineItems = strLineItems & "Qty" & Space(4) & "Description" &
Space(27) & "Price Ea " & "Total Price" & vbCrLf
strLineItems = strLineItems & String(70, "-") & vbCrLf


rsProds.MoveFirst
Do Until rsProds.EOF
vQty = Format(rsProds![detQty], "##0")
vPrice = Format(rsProds![detPrice], "#,0.00") '00
vExt = Format(rsProds![detExt], "#,0.00") '00

If Len(vExt) = 4 Then 'Adjust position of extention according to size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(18 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 5 Then 'Adjust position of extention according to
size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(17 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 6 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 8 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(14 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 9 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(13 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
Else
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
End If

rsProds.MoveNext

Loop

vOrdTotal = Format([ Products].Form![Text5], "#,0.00") '00

strLineItems = strLineItems & Space(58) & String(9, "_") & vbCrLf

If Len(vOrdTotal) = 5 Then 'Adjust position of Total according to size
strLineItems = strLineItems & Space(62) & vOrdTotal
ElseIf Len(vOrdTotal) = 6 Then 'Adjust position of Total according to
size
strLineItems = strLineItems & Space(61) & vOrdTotal
'ElseIf Len(vOrdTotal) = 7 Then There is no case for 7 which is the
comma
'strLineItems = strLineItems & Space(59) & vOrdTotal
ElseIf Len(vOrdTotal) = 8 Then
strLineItems = strLineItems & Space(59) & vOrdTotal

ElseIf Len(vOrdTotal) = 9 Then
strLineItems = strLineItems & Space(57) & vOrdTotal
Else
strLineItems = strLineItems & Space(62) & vOrdTotal
End If
'strMessage = strMessage & vbCrLf & strLineItems & vbCrLf & _ (Line
items omitted from email body)

strMessage = strMessage & vbCrLf & vbCrLf & _
"Please contact Synergy Customer Service with any questions
concerning this order." & vbCrLf & _
"716-834-4457" & vbCrLf & vbCrLf & _
"Thank you for your business!" & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & _
"See Attachment for price confirmation!"
strMessage = strMessage & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & vbCrLf & vbCrLf & vDisclaimer



DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT,
vSendTo, , vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True
'MsgBox "Email Sent!"


Case 2 'Fax Confirmation

Case Else 'Abort
MsgBox "No method of sending was defined in the Customer Profile for " &
[ordCustName]
End Select


exit_Section:
On Error Resume Next
Set rsProds = Nothing
Set rsCust = Nothing
Set dbs = Nothing
Exit Sub

error_Section:

MsgBox "Error " & Err & " has occured: " & Err.Description
Resume exit_Section
 
I have seen quite a bit of code for sending email. I have
tried many of them. The one below( I have it stored as a
function) has been the most reliable fot me with MS
Outlook. Also I am able to pull from an open form the
recipient, subject, text and attachment. Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String,
strAttachment1 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for bdy here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
End If
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

-----Original Message-----
Hello,

This is among the myriad of problems I have encountered since linking to SQL
Server Tables. The following snippet shows the method then flagging the
email as being sent. Outlook 2000 is the Client and server.

DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT, vSendTo, ,
vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True


The entire method is below: Sometimes the email does not get sent, and no
error is generated, then it gets marked as being sent. This is a problem
for my client becuase they they can't take the time to call clients to see
if they received their confirmations.

Does anyone have any ideas on this???

Thanks and God Bless,

Mark A. Sam



On Error GoTo error_Section

'Check whether email has been sent for this order
If [ordConfEmailed] = True Then
If MsgBox("An Email has been sent for this order. Do you wish to resend
this confirmation?", vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
End If


Dim dbs As Database
Dim rsProds As Recordset
Dim rsCust As Recordset
Dim strCriteria As String
Dim vPreview As Boolean 'used to set the EditMessage argument for the
SendObject method
Dim vSendTo As String 'Used to set the recipient email addy for the
SendObject method
Dim vBCcTo As String 'Used to set the Blind CC for the SendObject method
Dim vDisclaimer As String

Set dbs = CurrentDb()
Set rsCust = dbs.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
Set rsProds = [ Products].Form.RecordsetClone


If rsProds.RecordCount = 0 Then 'If there are no products then the
confirmation isn't nesessary
MsgBox "There are no products entered in this order. Confirmation is
aborted."
Exit Sub
End If

'Determine if this is an Email or Fax send
strCriteria = "[CustID] = " & [ordCustID]
rsCust.FindFirst strCriteria
If rsCust.NoMatch Then
MsgBox "Customer Not Found. You must enter email address manually!"
[ViewEmail] = True
End If

Select Case rsCust![ConfMode]
Case 1 'Email Confirmation
' Redo***
vPreview = [ViewEmail] 'View email is a checkbox on the current form
vSendTo = Nz(rsCust![ConfEmail], "")
If IsNull(vSendTo) Or vSendTo = "" Then
MsgBox "No Confiramtion Email Address was set up for this customer.
You must enter email address manually!"
vPreview = True
End If

vBCcTo = "(e-mail address removed)"
vDisclaimer = "Merchandise covered by this confirmation is warranted to
be free from defects in workmanship " & _
"but not for any specific length of time, type or measure
of service. Claims for allowance will only " & _
"be recognized when presented in writing within 5 days of
receipt of material. No claims for labor, " & _
"transportation or consequential damages will be allowed.
The maximum liability for any claim predicated upon " & _
"defective merchandise is limited to replacement of
merchandise, or to repayment of the purchase price, " & _
"whichever may be elected by Synergy Tooling Systems,
Inc."


Dim strLineItems As String 'This is to hold the line item values from
the [ Products] subform
Dim vQty As String
Dim vPrice As String
Dim vExt As String
Dim vOrdTotal As String

'Create Email Subject line
Dim strSubject As String
strSubject = "Synergy Order Confirmation: " & [ordJob] & " PO: " &
[ordPO] & " Ref#: " & [ordCustRef]

'Create Email message
Dim strMessage As String
If Not IsNull(rsCust![ConfContact]) Then
strMessage = rsCust![ConfContact] & vbCrLf 'This is the first line
of the Message. If you move this you must remove the Null assignment below
Else
strMessage = ""
End If
strMessage = strMessage & [ordCustName] & vbCrLf & _
"PO Number: " & [ordPO] & vbCrLf & _
"Ref # " & [ordCustRef] & vbCrLf & _
"Required Date: " & [ordReqDate] & vbCrLf & _
"Synergy Order Number: " & [ordJob]

strLineItems = String(70, "-") & vbCrLf
strLineItems = strLineItems & "Qty" & Space(4) & "Description" &
Space(27) & "Price Ea " & "Total Price" & vbCrLf
strLineItems = strLineItems & String(70, "-") & vbCrLf


rsProds.MoveFirst
Do Until rsProds.EOF
vQty = Format(rsProds![detQty], "##0")
vPrice = Format(rsProds![detPrice], "#,0.00") '00
vExt = Format(rsProds![detExt], "#,0.00") '00

If Len(vExt) = 4 Then 'Adjust position of extention according to size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(18 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 5 Then 'Adjust position of extention according to
size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(17 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 6 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 8 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(14 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 9 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(13 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
Else
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
End If

rsProds.MoveNext

Loop

vOrdTotal = Format([ Products].Form![Text5], "#,0.00") '00

strLineItems = strLineItems & Space(58) & String(9, "_") & vbCrLf

If Len(vOrdTotal) = 5 Then 'Adjust position of Total according to size
strLineItems = strLineItems & Space(62) & vOrdTotal
ElseIf Len(vOrdTotal) = 6 Then 'Adjust position of Total according to
size
strLineItems = strLineItems & Space(61) & vOrdTotal
'ElseIf Len(vOrdTotal) = 7 Then There is no case for 7 which is the
comma
'strLineItems = strLineItems & Space(59) & vOrdTotal
ElseIf Len(vOrdTotal) = 8 Then
strLineItems = strLineItems & Space(59) & vOrdTotal

ElseIf Len(vOrdTotal) = 9 Then
strLineItems = strLineItems & Space(57) & vOrdTotal
Else
strLineItems = strLineItems & Space(62) & vOrdTotal
End If
'strMessage = strMessage & vbCrLf & strLineItems & vbCrLf & _ (Line
items omitted from email body)

strMessage = strMessage & vbCrLf & vbCrLf & _
"Please contact Synergy Customer Service with any questions
concerning this order." & vbCrLf & _
"716-834-4457" & vbCrLf & vbCrLf & _
"Thank you for your business!" & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & _
"See Attachment for price confirmation!"
strMessage = strMessage & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & vbCrLf & vbCrLf & vDisclaimer



DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT,
vSendTo, , vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True
'MsgBox "Email Sent!"


Case 2 'Fax Confirmation

Case Else 'Abort
MsgBox "No method of sending was defined in the Customer Profile for " &
[ordCustName]
End Select


exit_Section:
On Error Resume Next
Set rsProds = Nothing
Set rsCust = Nothing
Set dbs = Nothing
Exit Sub

error_Section:

MsgBox "Error " & Err & " has occured: " & Err.Description
Resume exit_Section




.
Click to expand...
 
Thanks Jim,

I don't want to use automation for this task. I am planning on getting out
of this business soon and don't want to leave the client with the
responsibility of having to know to link the Outlook Library. He just
bought out his competitor. With the new locations he has an added challange
of getting this app implemented remotely. This has to be an SQL Server
issue. Since upsizing and linking the tables, it has been nothing but a
nightmare of goofy issues. My procedure worked perfectly well for several
years prior to changing to SQL Server.

God Bless,

Mark




Jim/Chris said:
I have seen quite a bit of code for sending email. I have
tried many of them. The one below( I have it stored as a
function) has been the most reliable fot me with MS
Outlook. Also I am able to pull from an open form the
recipient, subject, text and attachment. Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String,
strAttachment1 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for bdy here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
End If
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

-----Original Message-----
Hello,

This is among the myriad of problems I have encountered since linking to SQL
Server Tables. The following snippet shows the method then flagging the
email as being sent. Outlook 2000 is the Client and server.

DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT, vSendTo, ,
vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True


The entire method is below: Sometimes the email does not get sent, and no
error is generated, then it gets marked as being sent. This is a problem
for my client becuase they they can't take the time to call clients to see
if they received their confirmations.

Does anyone have any ideas on this???

Thanks and God Bless,

Mark A. Sam



On Error GoTo error_Section

'Check whether email has been sent for this order
If [ordConfEmailed] = True Then
If MsgBox("An Email has been sent for this order. Do you wish to resend
this confirmation?", vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
End If


Dim dbs As Database
Dim rsProds As Recordset
Dim rsCust As Recordset
Dim strCriteria As String
Dim vPreview As Boolean 'used to set the EditMessage argument for the
SendObject method
Dim vSendTo As String 'Used to set the recipient email addy for the
SendObject method
Dim vBCcTo As String 'Used to set the Blind CC for the SendObject method
Dim vDisclaimer As String

Set dbs = CurrentDb()
Set rsCust = dbs.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
Set rsProds = [ Products].Form.RecordsetClone


If rsProds.RecordCount = 0 Then 'If there are no products then the
confirmation isn't nesessary
MsgBox "There are no products entered in this order. Confirmation is
aborted."
Exit Sub
End If

'Determine if this is an Email or Fax send
strCriteria = "[CustID] = " & [ordCustID]
rsCust.FindFirst strCriteria
If rsCust.NoMatch Then
MsgBox "Customer Not Found. You must enter email address manually!"
[ViewEmail] = True
End If

Select Case rsCust![ConfMode]
Case 1 'Email Confirmation
' Redo***
vPreview = [ViewEmail] 'View email is a checkbox on the current form
vSendTo = Nz(rsCust![ConfEmail], "")
If IsNull(vSendTo) Or vSendTo = "" Then
MsgBox "No Confiramtion Email Address was set up for this customer.
You must enter email address manually!"
vPreview = True
End If

vBCcTo = "(e-mail address removed)"
vDisclaimer = "Merchandise covered by this confirmation is warranted to
be free from defects in workmanship " & _
"but not for any specific length of time, type or measure
of service. Claims for allowance will only " & _
"be recognized when presented in writing within 5 days of
receipt of material. No claims for labor, " & _
"transportation or consequential damages will be allowed.
The maximum liability for any claim predicated upon " & _
"defective merchandise is limited to replacement of
merchandise, or to repayment of the purchase price, " & _
"whichever may be elected by Synergy Tooling Systems,
Inc."


Dim strLineItems As String 'This is to hold the line item values from
the [ Products] subform
Dim vQty As String
Dim vPrice As String
Dim vExt As String
Dim vOrdTotal As String

'Create Email Subject line
Dim strSubject As String
strSubject = "Synergy Order Confirmation: " & [ordJob] & " PO: " &
[ordPO] & " Ref#: " & [ordCustRef]

'Create Email message
Dim strMessage As String
If Not IsNull(rsCust![ConfContact]) Then
strMessage = rsCust![ConfContact] & vbCrLf 'This is the first line
of the Message. If you move this you must remove the Null assignment below
Else
strMessage = ""
End If
strMessage = strMessage & [ordCustName] & vbCrLf & _
"PO Number: " & [ordPO] & vbCrLf & _
"Ref # " & [ordCustRef] & vbCrLf & _
"Required Date: " & [ordReqDate] & vbCrLf & _
"Synergy Order Number: " & [ordJob]

strLineItems = String(70, "-") & vbCrLf
strLineItems = strLineItems & "Qty" & Space(4) & "Description" &
Space(27) & "Price Ea " & "Total Price" & vbCrLf
strLineItems = strLineItems & String(70, "-") & vbCrLf


rsProds.MoveFirst
Do Until rsProds.EOF
vQty = Format(rsProds![detQty], "##0")
vPrice = Format(rsProds![detPrice], "#,0.00") '00
vExt = Format(rsProds![detExt], "#,0.00") '00

If Len(vExt) = 4 Then 'Adjust position of extention according to size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(18 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 5 Then 'Adjust position of extention according to
size
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(17 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 6 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 8 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(14 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
ElseIf Len(vExt) = 9 Then
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(13 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
Else
strLineItems = strLineItems & vQty & Space(7 - Len(vQty)) &
rsProds![detProdDescription] & Space(38 - Len(rsProds![detProdDescription]))
& Space(8 - Len(vPrice)) & vPrice & Space(16 - Len(vPrice) - (8 -
Len(vPrice))) & vExt & vbCrLf
End If

rsProds.MoveNext

Loop

vOrdTotal = Format([ Products].Form![Text5], "#,0.00") '00

strLineItems = strLineItems & Space(58) & String(9, "_") & vbCrLf

If Len(vOrdTotal) = 5 Then 'Adjust position of Total according to size
strLineItems = strLineItems & Space(62) & vOrdTotal
ElseIf Len(vOrdTotal) = 6 Then 'Adjust position of Total according to
size
strLineItems = strLineItems & Space(61) & vOrdTotal
'ElseIf Len(vOrdTotal) = 7 Then There is no case for 7 which is the
comma
'strLineItems = strLineItems & Space(59) & vOrdTotal
ElseIf Len(vOrdTotal) = 8 Then
strLineItems = strLineItems & Space(59) & vOrdTotal

ElseIf Len(vOrdTotal) = 9 Then
strLineItems = strLineItems & Space(57) & vOrdTotal
Else
strLineItems = strLineItems & Space(62) & vOrdTotal
End If
'strMessage = strMessage & vbCrLf & strLineItems & vbCrLf & _ (Line
items omitted from email body)

strMessage = strMessage & vbCrLf & vbCrLf & _
"Please contact Synergy Customer Service with any questions
concerning this order." & vbCrLf & _
"716-834-4457" & vbCrLf & vbCrLf & _
"Thank you for your business!" & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & _
"See Attachment for price confirmation!"
strMessage = strMessage & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf &
vbCrLf & vbCrLf & vbCrLf & vDisclaimer



DoCmd.SendObject acSendReport, "OrderConfirmation", acFormatTXT,
vSendTo, , vBCcTo, strSubject, strMessage, vPreview

[ordConfEmailed] = True
'MsgBox "Email Sent!"


Case 2 'Fax Confirmation

Case Else 'Abort
MsgBox "No method of sending was defined in the Customer Profile for " &
[ordCustName]
End Select


exit_Section:
On Error Resume Next
Set rsProds = Nothing
Set rsCust = Nothing
Set dbs = Nothing
Exit Sub

error_Section:

MsgBox "Error " & Err & " has occured: " & Err.Description
Resume exit_Section




.
Click to expand...
Click to expand...
 
Mark A. Sam said:
Thanks Jim,

I don't want to use automation for this task. I am planning on getting out
of this business soon and don't want to leave the client with the
responsibility of having to know to link the Outlook Library. He just
bought out his competitor. With the new locations he has an added challange
of getting this app implemented remotely. This has to be an SQL Server
issue. Since upsizing and linking the tables, it has been nothing but a
nightmare of goofy issues. My procedure worked perfectly well for several
years prior to changing to SQL Server.
Click to expand...

Silent failures of SendObject is a known bug in Access 2000. In fact that
is just one of many problems in A2K with SendObject. Some report that
installing all of the service packs solves the problems, but I can't
confirm or deny that.

I can't see any reason why a move to SQL Server would have any bearing on
this with one possible exception. I see that at one point you test for
[ordConfEmailed] = True. Is this a field that was a Jet Yes/No field and
is now a SQL Server bit field? If so, you might need to change your test to
<> 0.

Access stores Yes/No as -1 or zero while SQL Server bit fields use 1 or
zero. While Access has no trouble with testing for True/False even though
what is stored is -1/0 the same has not been my experience with SQL Server
bit fields. I had many queries after moving to SS that required me to
change tests for True/False to = 0 or <> 0 before they would work reliably.
 
Thank you Rick,
I will make that change, even though it seems unlikely, becuase this
occurs inconsitently.

There was another issue that I found on the knowledge base which said that
some messages which have too much data will fail silently. I don't see this
as the case, because the body of rh message for the most part is constant
and the data which is variable content goes into an attachment. But I tried
anyway to duplicate this problem at home and cannot. I added an abudance of
data to an order and it went out fine. Your explanation sounds plausible,
except for that fact that it doesn't occur in each case.

God Bless,

Mark


Rick Brandt said:
Mark A. Sam said:
Thanks Jim,

I don't want to use automation for this task. I am planning on getting out
of this business soon and don't want to leave the client with the
responsibility of having to know to link the Outlook Library. He just
bought out his competitor. With the new locations he has an added challange
of getting this app implemented remotely. This has to be an SQL Server
issue. Since upsizing and linking the tables, it has been nothing but a
nightmare of goofy issues. My procedure worked perfectly well for several
years prior to changing to SQL Server.
Click to expand...

Silent failures of SendObject is a known bug in Access 2000. In fact that
is just one of many problems in A2K with SendObject. Some report that
installing all of the service packs solves the problems, but I can't
confirm or deny that.

I can't see any reason why a move to SQL Server would have any bearing on
this with one possible exception. I see that at one point you test for
[ordConfEmailed] = True. Is this a field that was a Jet Yes/No field and
is now a SQL Server bit field? If so, you might need to change your test to
<> 0.

Access stores Yes/No as -1 or zero while SQL Server bit fields use 1 or
zero. While Access has no trouble with testing for True/False even though
what is stored is -1/0 the same has not been my experience with SQL Server
bit fields. I had many queries after moving to SS that required me to
change tests for True/False to = 0 or <> 0 before they would work reliably.
Click to expand...
 
Actually, after checking the code, I don't see that logically this could be
the problem.

'Check whether email has been sent for this order
If [ordConfEmailed] = True Then
If MsgBox("An Email has been sent for this order. Do you wish to resend
this confirmation?", vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
End If

[ordConfEmailed] is a checkbox on the form, and I am pretty sure handled by
JET.
If the value is -1 the which is the value of [ordConfEmailed] the method
will abort as it is suppose to, otherwise if it is not -1 the email will be
sent, which it is suppose to do. The issue occurs when [ordConfEmailed] is
not true.

You did bring up a good point however and I will check for other boolean
values that may be an issue. It would be much easier if I could duplicate
the problem though.

God Bless,

Mark


Rick Brandt said:
Mark A. Sam said:
Thanks Jim,

I don't want to use automation for this task. I am planning on getting out
of this business soon and don't want to leave the client with the
responsibility of having to know to link the Outlook Library. He just
bought out his competitor. With the new locations he has an added challange
of getting this app implemented remotely. This has to be an SQL Server
issue. Since upsizing and linking the tables, it has been nothing but a
nightmare of goofy issues. My procedure worked perfectly well for several
years prior to changing to SQL Server.
Click to expand...

Silent failures of SendObject is a known bug in Access 2000. In fact that
is just one of many problems in A2K with SendObject. Some report that
installing all of the service packs solves the problems, but I can't
confirm or deny that.

I can't see any reason why a move to SQL Server would have any bearing on
this with one possible exception. I see that at one point you test for
[ordConfEmailed] = True. Is this a field that was a Jet Yes/No field and
is now a SQL Server bit field? If so, you might need to change your test to
<> 0.

Access stores Yes/No as -1 or zero while SQL Server bit fields use 1 or
zero. While Access has no trouble with testing for True/False even though
what is stored is -1/0 the same has not been my experience with SQL Server
bit fields. I had many queries after moving to SS that required me to
change tests for True/False to = 0 or <> 0 before they would work reliably.
Click to expand...
 
Mark A. Sam said:
Actually, after checking the code, I don't see that logically this could be
the problem.

'Check whether email has been sent for this order
If [ordConfEmailed] = True Then
If MsgBox("An Email has been sent for this order. Do you wish to resend
this confirmation?", vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
End If

[ordConfEmailed] is a checkbox on the form, and I am pretty sure handled by
JET.
Click to expand...

That was my experience. Testing controls on forms (even if bound to a bit
field) seemed to still work consistently with testing True/False. It was
testing field values in queries where I saw the problems.
 
Hello Rick,

" It was testing field values in queries where I saw the problems."

That surprises me. I haven't noticed that yet. I have redesigned queries
that used funtions and expressions but left the Boolean criteria and haven't
seen that the results have been affected. In fact I have edit screens thatg
previously ran off of queries in which I created tables from those queries,
leaving all True/False criteria and the results seem to match on the new and
old screen versions. BUT, that isn't to say you are wrong. The results are
inconsistent. I could be that the results are wrong on Monday/ Thursday and
Friday and incorrect on Tuesday and Thursday. ;)


Have you had any experience with changing Access tables to Oracle and
linking? I have a website that I will be upsizing from an Access Db in the
furture and was thinking of Oracle rather than SQL Server but don't know if
the issues will be there also.

God Bless,

Mark
 
Mark A. Sam said:
Hello Rick,

" It was testing field values in queries where I saw the problems."

That surprises me. I haven't noticed that yet. I have redesigned queries
that used funtions and expressions but left the Boolean criteria and haven't
seen that the results have been affected. In fact I have edit screens thatg
previously ran off of queries in which I created tables from those queries,
leaving all True/False criteria and the results seem to match on the new and
old screen versions. BUT, that isn't to say you are wrong. The results are
inconsistent. I could be that the results are wrong on Monday/ Thursday and
Friday and incorrect on Tuesday and Thursday. ;)


Have you had any experience with changing Access tables to Oracle and
linking? I have a website that I will be upsizing from an Access Db in the
furture and was thinking of Oracle rather than SQL Server but don't know if
the issues will be there also.
Click to expand...

Let me emphasize that the problems I had were inconsistent. I had some
queries where True/False tests clearly worked as expected and others where
they did not. Another factor is that while bits are stored as Positive one
and zero, they are displayed in Access as negative one and zero. I found
that if I opened a Datasheet that displayed the number values and placed
the cursor in a field showing (-1) and applied the "Filter-By-Selection"
tool that the result was totally incorrect. If I went into the advanced
filter grid and changed the criteria from (-1) to (1) that didn't always
work right either. Using the "Filter-By-Selection" or
"Filter-Excluding-Selection" always worked on the zeros. That is what got
me started with always testing for zeros. When I applied these changes to
the queries that were acting up all the problems were solved.

We were using SQL Server 6.5 up until a month or so ago and as I recently
tried these things again they appear to work properly. Perhaps the SQL
Server revision solved some problems or perhaps it was an update to the
ODBC driver. All I know is that testing for =0 or <>0 seems to always work
and I generally now use integer fields on the server for this stuff instead
of bit fields and the zero test carries over without incident there as
well.
 
Back
Top