One thing we do here is to:
1) create a temp directory
2) output the query as an Excel spreadsheet, and save
it in the temp directory
3) Attach it to an e-mail
'put this in the form code:
Const Base_path As String =
"C:\Temp123456789"
'put this behind the button to perform the
action
MkDir
Base_path
'we have this in its own module
'Create the OUTLOOK
OBJ
Set myOLApp = CreateObject("Outlook.Application")
Set myitem = myOLApp.CreateItem(olMailItem)
'Add Information to the EMAIL
With myitem
.To = "" 'To: field here
.Subject = "" 'Subject of e-mail here
.Body = "" 'Body of e-mail here
DoCmd.OutputTo acReport, "YourReportName", acFormatXLS, Base_path & "\FileoutputName.xls", False, "", 0
.Attachments.Add Base_path & "\FileoutputName.xls"
.Save 'Puts them in the Draft Box - I think you can do .Send here as well
End With
'Clear the OBJ out
Set myOLApp = Nothing
Set myitem = Nothing
RmDir base_path
Set myOLApp = CreateObject("Outlook.Application")
Set myitem = myOLApp.CreateItem(olMailItem)
'Add Information to the EMAIL
With myitem
.To = "" 'To: field here
.Subject = "" 'Subject of e-mail here
.Body = "" 'Body of e-mail here
DoCmd.OutputTo acReport, "YourReportName", acFormatXLS, Base_path & "\FileoutputName.xls", False, "", 0
.Attachments.Add Base_path & "\FileoutputName.xls"
.Save 'Puts them in the Draft Box - I think you can do .Send here as well
End With
'Clear the OBJ out
Set myOLApp = Nothing
Set myitem = Nothing
RmDir base_path
Hope that helps. There is some extra stuff I took
out - error-checking, and where we set the variables for the To, Subject, Body
and report name, but I know you can do that stuff easily.
Eric
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Hoffman, Tobi K (DYS)
Sent: Tuesday, August 09, 2005 10:24 AM
To: [email protected]
Subject: [AccessDevelopers] E-mail with spreadsheet attachmentI've been searching the archive (and BTW, the search procedure has been much improved since the last time I did this!) to find if anybody ever addressed the issue of sending an e-mail with an attachment via e-mail. I have generated a spreadsheet to export data, and the client wants it to be sent automatically via e-mail to the central office. The DoCmd.SendObject method allows you to send an Access object, such as a table or query, but what I need to do is send the spreadsheet that I have just created. I could send the whole list of queries that went into the spreadsheet, but I'd prefer to do it as a single e-mail with the spreadsheet attached.
Tobi Hoffman
Dept. of Youth Services
Boston, MA
Please zip all files prior to uploading to Files section.
YAHOO! GROUPS LINKS
- Visit your group "AccessDevelopers" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
