Solved: Email Active Excel Worksheet from Access

[boneKrusher]

Hi all, I have found some code on this forum and am having trouble with the code. The code exports a query to a excel spreadsheet. Then I want it to email the active worksheet. The code works except it doesnt attach the active spreadsheet.

Any help would be great.


Public Function ExportTableToExcel(ByVal sTable As String) As Boolean
On Error Resume Next
Dim sFile As String, sThisMDB As String, sOpen As String
Const q As String * 1 = """"

sThisMDB = CurrentDb.NAME
sFile = Left(sThisMDB, InStrRev(sThisMDB, "\")) & sTable & ".xls"
If Dir(sFile) <> "" Then Kill sFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sTable, sFile, True

sOpen = "excel.exe " & q & sFile & q
Shell sOpen, vbNormalFocus
Dim OL As Object
Dim EmailItem As Object
Dim wb As Workbook
Dim fullname As String
Dim EMailComment
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set wb = ActiveWorkbook
ActiveWorkbook.Save
wb.Save
With EmailItem
.Subject = "QA Audit assigned"
EMailComment = InputBox("Please enter your comment for the E-mail")
.Body = EMailComment
.To = "name@domain.com"
.Importance = olImportanceHigh 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add wb.fullname
.Send
End With

[Norie]

You have no reference to Excel so this means nothing.:)

Set wb = ActiveWorkbook

Try this.

.Attachments.Add sFile

[boneKrusher]

Thanks! worked perfect. :friends: