Email-Sendobject-Automation-Excel: Formatting Page Settings in code 
Author Message
 Email-Sendobject-Automation-Excel: Formatting Page Settings in code

I am using the following code to send reports by email.  It takes a reports
data and creates a (MicrosoftExcel(*.xls) attachment.

DoCmd.SendObject acReport, "rptGLTable", "MicrosoftExcel(*.xls)",
rsCriteria![User], "", "", "This is a test", "I am testing a new idea for
reports", False, ""

The code works fine as is.  An Excel ss is created and is emailed.

Problem is the page settings of the Excel ss that is generated.  The
finished ss comes out as portrait orientation and it should be landscape.
The print area is too narrow and puts data on another page.  Also the
margins need to be changed.

Any way I can manipulate the page settings in the code?

Should I try another method?

Do I have to use automation?
i.e. from within access use code to start Excel, open a template, export the
report data, then send the attachment.

Your ideas and code samples appreciated.


TIA,

Howard

--begin code--
'**********************************
'Created by Roger Carlson


*'**********************************

Sub SeparateEmails()'*** error trapping - execution goes to bottom on error
On Error GoTo Err_SeparateEmails

Dim db As DatabaseDim qdf As QueryDefDim strSQL As String
Dim rsGLTable As RecordsetDim rsCriteria As Recordset

Set db = CurrentDbSet rsCriteria = db.OpenRecordset("Users", dbOpenSnapshot)

'*** the first record in the Criteria table ***rsCriteria.MoveFirst

'*** loop to move through the records in Criteria tableDo Until
rsCriteria.EOF
    '*** create the Select query based on
    '    the first record in the Criteria table
    strSQL = "SELECT * FROM GLTable WHERE "
    strSQL = strSQL & "[Acct] = '" & rsCriteria![Param] & "'"

    'MsgBox strSQL    '*** delete the previous query
    db.QueryDefs.Delete "NewQuery"
    Set qdf = db.CreateQueryDef("NewQuery", strSQL)

     DoCmd.SendObject acReport, "rptGLTable", "MicrosoftExcel(*.xls)",
rsCriteria![User], "", "", "This is a test", "I am testing a new idea for
reports", False, ""

    '*** goto the next record in Criteria table    rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:    Exit Sub

Err_SeparateEmails:  '*** if there  is an error, execution goes here
    '*** if the error is the table or query missing (3265)
    '    then skip the delete line and resume on the next line
    '    Error 2501 notifies you that the SendObject action
    '    has been cancelled.  See the OnNoData Event of the report.
    If Err.Number = 3265 Or Err.Number = 2501 Then        Resume Next
Else
    '*** write out the error and exit the sub    MsgBox Err.Description
    Resume Exit_SeparateEmails    End If

End Sub--end code--



Thu, 27 Dec 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. format body of Email automation

2. ActiveDocument.Content.Find.Execute error

3. Excel: Automation for Setting Data format

4. Formatting Submitted Text HELP PLEASE URGENT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

5. DIRE NEED OF HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

6. Contact Item, SMTP, EX email address using Redemption?

7. Setting the Email Sensitivity in code etc

8. Code to extract a formatted message body of an email into an access Database

9. RTF CODES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

10. Modify EMail properties on a per automation generated EMail

11. trying to setting figure format in vba code

 

 
Powered by phpBB® Forum Software