Using a while command for emailing reports

[Michael57]

I have a routine that prints a series of quotes based on the begining and ending quote numbers entered on form using the following code:

Private Sub Command7_Click()

PrintQuoteNo = Me.StartQuoteNo
While (PrintQuoteNo <= Me.EndQuoteNo)

DoCmd.OpenReport "QuotePrintSigned", acViewNormal, , "QuoteNo =" & PrintQuoteNo
DoCmd.Close acReport, "QuotePrint"
PrintQuoteNo = (PrintQuoteNo + 1)
Wend

End Sub

How can I convert the following code to email with a similar while statement. I need to be able to choose the starting and ending quote number.

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String

stDocName = "QuotePrintSigned"
DoCmd.SendObject acReport, stDocName

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

 

[JerryKlmns]

I think if you open the report (hidden) and use the DoCmd.SendObject method without defining the report name, the currently open report is send as an attached snapshot...

Private Sub Command12_Click()
Dim iCount As Integer
On Error GoTo Err_Command12_Click

For iCount = Me.StartQuoteNo To Me.EndQuoteNo
   DoCmd.OpenReport "QuotePrintSigned",acViewNormal,,"QuoteNo =" & PrintQuoteNo,acHidden
   DoCmd.SendObject acSendReport,, acFormatSNP, "to@somewhere.com","cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText"
   DoCmd.Close acReport, "QuotePrintSigned"
Next 

Exit_Command12_Click:
    Exit Sub

Err_Command12_Click:
    MsgBox Err.Description
    Resume Exit_Command12_Click
    
End Sub

 

[Michael57]

Jerry when I run your code I get the following error:

Extra ) in query expression '(QuoteNo=)'.

Not sure where this is coming from?

 

[PHV]

Typed, untested:
PrintQuoteNo = Me.StartQuoteNo
While PrintQuoteNo <= Me.EndQuoteNo
DoCmd.OpenReport "QuotePrintSigned", acViewPreview, , "QuoteNo=" & PrintQuoteNo, acHidden
DoCmd.SendObject acSendReport, , acFormatSNP, "to@somewhere.com", "cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText"
DoCmd.Close acReport, "QuotePrintSigned"
PrintQuoteNo = PrintQuoteNo + 1
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

PHV
Thanks for pointing the OP's similar while statement I overlooked


Michael57
Probably from the query behind the report QuotePrintSigned?

 

[Michael57]

I now get a debug message with this line highlighted:

DoCmd.SendObject acSendReport, , acFormatSNP, "to@somewhere.com", "cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText

 

[JerryKlmns]

Please post that message because my Crystal Ball is down for maintance....

 

[Michael57]

Sorry it says:
Run-Time error '2487':
The Object Type argument for the action or method is blank or invalid

 

[JerryKlmns]

Trying to fool the machine ....

DoCmd.SendObject 3, , acFormatSNP, "to@somewhere.com", "cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText

 

[Michael57]

Now I get Run-time error 2498
An expression you entered is the wrong data type for one of the aruguments

 

[JerryKlmns]

Ok,

by trial and error found that you can not have it hidden and send it, so remove that acHidden when you open the report.

PrintQuoteNo = Me.StartQuoteNo
While PrintQuoteNo <= Me.EndQuoteNo
    DoCmd.OpenReport "QuotePrintSigned", acViewPreview, , "QuoteNo=" & PrintQuoteNo
    DoCmd.SendObject acSendReport, , acFormatSNP, "to@somewhere.com", "cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText"
' If you need not to edit it rather than just send it
'    DoCmd.SendObject acSendReport, , acFormatSNP, "to@somewhere.com", "cc@somewhere.com", "bcc@somewhere.com", "ThisSubject", "WithThisText", False 
    DoCmd.Close acReport, "QuotePrintSigned", acNoSave
    PrintQuoteNo = PrintQuoteNo + 1
Wend

 

[Michael57]

Getting closer however instead of combining all quotes on one email it is sending out an email for each quote. Is there a way to change this. Thanks for your continued support.

 

[Michael57]

Also is there a way to output adobe instead of snap

 

[JerryKlmns]

Sorry,
a] Not that I know
b] pdf format is not a built-in one.

 

[JerryKlmns]

This is for pdf faq705-1635

 

[PHV]

pdf format is not a built-in one
Even in ac2007 ?