Export Excel from Acceess to new outlook email

[djmousie]

I've recently received feedback from thread701-1198915 (thanks to BoxHead) on how to export an Access table to excel using a command button. Is there also a way, with VB script, to not only place the excel file in a designated directory on a PC, but also, when exported, it opens a new outlook email and attaches it to the new email? Below is the code I've used so far to export an Access table to an excel spreadsheet:

CODE
Private Sub Command0_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT SR FROM tblPipeline ORDER By SR;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("rpt_" & strCrt, "SELECT tblPipeline.* FROM tblPipeline WHERE tblPipeline.SR = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rpt_" & strCrt, "C:\Documents and Settings\John\My Documents\SR_Report_" & strDt & ".xls", True
DoCmd.DeleteObject acQuery, "rpt_" & strCrt
rs.MoveNext
Loop
End Sub

 

[Remou]

You may wish to look at SendObject. Also, a search in these fora for Attachment may prove useful.

 

[djmousie]

I have looked at SendObject, however, I am looking to use the code above in conjunction with the SendObject. I want to be able to use the code above to place a file on my shared drive, and at the same time open up a new outlook email and attach that file to the new email....

 

[Remou]

How about:
How to Email From Access using Outlook
faq702-2921

 

[djmousie]

Thanks Remou, I took a portion of the code from that thread, however, when using the command click option, it doesn't do anything at all (no errors either), but it doesnt open a new email from outlook....

'******begin code******
Dim email As String
Dim ref As String
Dim origin As String
Dim destination As String
Dim notes As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = strEmail
If Not IsNull(txtCCemail) Then
.CC = strCCemail
End If
.Subject = "Billbacks"
.Body = strBody
.Attachments.Add ("G:\example.xls") 'add attachment
End With

'****end attachment code****
End Sub

 

[Remou]

You need a .Display or .Send just after .Attachments.Add
I suggest the .Display for testing.

 

[djmousie]

Gotcha. Thanks!