Case strFormat = acFormatRTF, Problem

B

Bob Vance

All my other Case scenarios are working except fpr "Word". But word is
working on another case code on another form
Can any see why this word not work?
Case "WORD"
strFormat = acFormatRTF
-------------------------------------------------
Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
End If

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer, tbAmount As String
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else ' catch all others
strFormat = acFormatHTML
End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError



DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'Cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3
 
D

Dirk Goldgar

Bob Vance said:
All my other Case scenarios are working except fpr "Word". But word is
working on another case code on another form
Can any see why this word not work?
Case "WORD"
strFormat = acFormatRTF
-------------------------------------------------
Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
End If

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
As Integer, tbAmount As String
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else ' catch all others
strFormat = acFormatHTML
End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError



DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'Cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3
Click to expand...


Bob -

You need to tell us in what way it's not working, what error message (if
any) and error number are generated, and exactly which line rasies the error
 
B

Bob Vance

Bob -

You need to tell us in what way it's not working, what error message (if
any) and error number are generated, and exactly which line rasies the
error

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Thanks Dirk
Enter parameter Value Forms!frmBillStatement!cbOwnerName
But That is the Form and Combo box to select Owner
 
D

Dirk Goldgar

Bob Vance said:
Thanks Dirk
Enter parameter Value Forms!frmBillStatement!cbOwnerName
But That is the Form and Combo box to select Owner
Click to expand...


Is that coming up as an *error*? That just looks like a parameter prompt,
for which the most likely cause would be that the form, "frmBillStatement",
isn't open at the time it is being called on by a query. There is no way it
can have anything directly to do with the format you are using, acFormatRTF.
Maybe they are related in some coincidental way, but I don't see how.

Looking at your code, I'm guessing that the recordsource of the report
refers to Forms!frmBillStatement!cbOwnerName. That form must be open when
the report is run, or else you will get a parameter prompt such as you
describe. Is it?
 
B

Bob Vance

Is that coming up as an *error*? That just looks like a parameter prompt,
for which the most likely cause would be that the form,
"frmBillStatement", isn't open at the time it is being called on by a
query. There is no way it can have anything directly to do with the
format you are using, acFormatRTF. Maybe they are related in some
coincidental way, but I don't see how.

Looking at your code, I'm guessing that the recordsource of the report
refers to Forms!frmBillStatement!cbOwnerName. That form must be open when
the report is run, or else you will get a parameter prompt such as you
describe. Is it?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
You are right Dirk the report is not open so I would need to have this code
on the On Deactivate, do you think?
Regards Bob

Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
End If

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer, tbAmount As String
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else ' catch all others
strFormat = acFormatHTML
End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError



DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'Cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub
 
D

Dirk Goldgar

Bob Vance said:
You are right Dirk the report is not open so I would need to have this
code on the On Deactivate, do you think?
Click to expand...

"the report is not open" -- did you mean, "the form is not open"? It's the
form that appears to be referenced by the query that is driving the report,
so the *form* must be open when you open or export the report.

I don't have enough information about what you're doing to answer your
question that I quoted above. All I can say is that the form must be open
when you run the report. If the code you posted, which uses SendObject to
send the report in an e-mail, is in a command button on the form in
question, then the form *is* open. If the button is on some other form,
then maybe you closed the original form, and you could avoid doing that.
 

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

Add to text box String 4
No Error Message wanted 2
Open Report while creating email 1
Email Through Windows Live! 2
Emailing Question from my DB 2
True or False for SNP or PDF format to email 12
Adding a CC to email code! 7
Email Formats Crashing my DB 0

Top