Sending Access Reports as Attachments in Outlook 1

[Eightball3]

I think I'm close to figuring this out. I'm trying to set my application up so that when a button is pushed a report is attached to email and sent. I have everything working except the attachment part. How do you tell Outlook that you want to attach an Access Report? I get the message that the report is not open. Here is what I have:

Sub SendMessage_Mail(Optional AttachmentPath)
Dim Message As String
Dim CC As String
Dim Sbjct As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

CC = "test"

Sbjct = "test"
Message = "test"

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.add("test&quot
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
If CC = "" Then
Else
Set objOutlookRecip = .Recipients.add(CC)
objOutlookRecip.Type = olCC
End If

' Set the Subject, Body, and Importance of the message.
.Subject = Sbjct
.Body = Message
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
'Set objOutlookAttach = .Attachments.add([Reports]![Report])

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Display
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Can anyone help me out here? Thanks

 

[JeremyNYC]

Howzabout one line, no references to be set:
call docmd.SendObject(acSendReport,"rptName",,"junk@nowhere.com",,,"New Report","Here's the new report.",True)

Check out the SendObject method in the help files. If all you're doing is sending a report, this will be far easier.

Jeremy ==
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

(See the Developers' section)
AlphaBet City Dataworks

Please post in the appropriate forum and include 1) a descriptive subject 2) code and SQL, if referenced,
and 3) expected results. thread181-473997

 

[Rick39]

Might export the reports as snapshots then:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = "Put your address here"
.Subject = "Your Subject"
.Attachments.Add "C:\Stuff.snp", olByValue, 1, "Stuff"
.Attachments.Add "C:\MoreStuff.snp", olByValue, 1, "Morestuff"
.Send
End With
End Sub

 

[Eightball3]

Hi Jeremy,
That is the way I did it in the past. We have just upgraded to Access 2000 and it has a bug in the SendObject command (

http://support.microsoft.com/default.aspx?scid=kb;en-us;260819).

It acts like it sends it but it doesn't. Working with the Outlook Object is a work-around for me. I would much rather handle it with the SendObject command ... much easier.

Hi Rick,
I can work with the snapshots but how do I create them? With the SendObject command I simply specified the output format and then the report name. Would this report appear on the screen before it gets attached to the email (I don't want that). I'm using a loop to gererate about 50 different reports to different recipients. Thanks.

 

[Rick39]

Try something like:

Dim strREPORTNAME as string
Dim strTEMPFILENAME as string

strREPORTNAME = "rptYourReport"
strTEMPFILENAME = "C:\rptYourReport.snp"

DoCmd.OutputTo acOutputReport, strREPORTNAME, "Snapshot Format (*.snp)", strTEMPFILENAME, False 'don't start OLE app

You could even set up a table with report names, open a recordset, and loop thru it to create and mail the snapshots.

Hope this helps

 

[Eightball3]

O.K. I've given it a try but get this error message "Run-Time error 2282 The formats that enable you to output data as a Microsoft Excel, rich-text format, MSDOS text, or HTML file are missing from the Windows Registry." How do I set up the Registry? Thanks.

 

[Rick39]

Take a look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;226526

 

[GoinDeep]

Here is a question to add to this. If your command button is on a form, how can I do a acSendReport, but only send the current record the form is displaying?

This is something I have temporarily given up on, and now I just do a OpenReport command button with a Where condition, and then save it as an export from there. Sure would be easier to send the report from a command button directly.

 

[Rick39]

Filter your underlying report query for the current record's RecordID

 

[Eightball3]

Thanks for your help Rick, got it working now!