Tamer Seoud said:
I created a user interface to view and print out reports.
I want to include a code that sends some of the reports to
recipients via e-mail after converting them to Excel
format. We use Lotus Notes as our e-mail system.
There are two parts to your answer.
1) is to convert the data to Excel. You can save a report in Excel
format but if you are using group headings and footings then the
layout will suck big time. You'll want to use some VBA code which
creates the spreadsheet and copies the data as produced by the query
directly to the spreadshet.
See below for the sample code.
2) For the email portoin there are some Lotus Notes specific links at
the Access Email FAQ at my website.
======================================================
'Copy records to rows
'in an Excel Workbook and worksheet
On Error GoTo tagError
'======================
'The following is Dimmed when Late Binding ison
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
' The following is Dimmed when a reference is used.
'Dim objXL As Excel.Application
'Dim objWkb As Excel.Workbook
'Dim objSht As Excel.Worksheet
'===================
Dim db As Database
Dim rs As Recordset
'Dim intLastCol As Integer, '
Dim iCols As Integer, iCountOfCols As Integer
Const conSHT_NAME = "Sheet1"
Set db = CurrentDb
' Set objXL = New Excel.Application
Set objXL = CreateObject("Excel.Application")
Set rs = db.OpenRecordset(strQuery, dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
objWkb.SaveAs Filename:=strXLSName
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
iCountOfCols = rs.Fields.Count
On Error GoTo 0
' intLastCol = objSht.UsedRange.columns.Count
With objSht
' .Range(.Cells(1, 1), .Cells(9999, intLastCol)).ClearContents
.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold =
True
' Insert Field Names as Heading Names
For iCols = 0 To iCountOfCols - 1
.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
' Insert recordset into cells
.Range("A2").CopyFromRecordset rs
' Autofit the cells
.Range(.Cells(1, 1), .Cells(rs.RecordCount,
iCountOfCols)).columns.AutoFit
End With
objWkb.Save
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rs.Close
Set rs = Nothing
Exit Sub
tagError:
Select Case Err.Number
Case 1004 ' Cannot Access 'test - 2003 05 06 - 2.xls'.
MsgBox "Error message '" & Err.Description & "' was
encountered." & vbCrLf & vbCrLf & _
"This likely means you have the spreadsheet open in Excel.
Please close it and try again."
Case Else
MsgBox Err.Description
End Select
Exit Sub
Resume
End Sub
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm