send an email containing a report but not by using docmd sendobject

[tozey]

Hello, I want to be able to send a report from my access 2003 database but I dont want to do it via the docmd.sendobject function.

the reason for this is I need to send many emails on a loop and when using the docmd it makes outlook use the security warning where you have to click on yes to conifirm you want to send. I dont want to turn this off in outlook as im sure my IT department wouldnt approve.

I have been sending mails through access via the following code, this code doesnt make outlook prompt the security warning it just sends the mail. However this code is set up to send an attachment, I want it to send my report in access. For the life of me I am not sure how I would include the access object into my email rather than an attachment held on my network.

Any help is appreciated, here is the code that send the attachment, I just need it to send the report. I will make it send to the relevant recipitants and loop once I know how to add my report....thank you.

Current code:

Private Sub Command25_Click()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim todayd

todayd = Format(Date, "DDMMYY")


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

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

With objOutlookMsg


' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("me@me.com")
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("you@you.com")
objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("them@them.com")
objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("C:file.txt")
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Sub

[R_Badger]

With a slight modification, and a little thought outside the box, I think it can be done.

Private Sub Command25_Click()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim todayd as date

todayd = Format(Date, "DDMMYY")


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

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

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("me@me.com")
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("you@you.com")
objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("them@them.com")
objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
' **** Start of modified code ****
dim reportpath as string
' prepares the variable
Reportpath= "C:\report.snp"
' Sets a filename and path for your report to go to
docmd.outputto, acreport, "report name here", "SnapshotFormat(*.snp)", reportpath, False, "", 0
' Outputs the report in SNP format to the filename and path stated before
Set objOutlookAttach = .Attachments.Add(reportpath)
' Should add the report as an attachment (i have not yet tested this)
kill reportpath
'deletes the temporary report file created before
' **** End of modified code ****

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Sub

[tozey]

Thank you for this info....I have tried it but I am getting the following error

Compile Error: Argument Not Optional

Then it highlights this line

DoCmd.OutputTo , acReport, "TEST", "SnapshotFormat(*.snp)", reportpath, False, "", 0

I have the report in the database and the .snp file on my C: drive

I am not too sure what this means.

Thanks

Alex

[R_Badger]

My bad, 1 comma too many


DoCmd.OutputTo acReport, "TEST", "SnapshotFormat(*.snp)", reportpath, False, "", 0

[tozey]

Thanks again, I re wrote this line and it worked when looking like this

DoCmd.OutputTo acOutputReport, "SnapshotFormat(*.snp)", reportpath, True

Thank you....Alex

(im not sure how to mark as solved?)

[LukeChung-FMS]

If interested, our Total Access Emailer add-in for Microsoft Access lets you send emails via SMTP with a lot more options than the DoCmd.SendObject command. For instance, you can attach filtered reports to each message, send messages in HTML format, embed graphics, etc. Attachments can also be zipped and password protected.

More info on the product, including a free trial, here: http://www.fmsinc.com/MicrosoftAccess/Email.asp

Here's a paper describing the features and limitations of the SendObject command: http://www.fmsinc.com/MicrosoftAcces...endObject.html

Hope this helps.

[Kirsti]

Hi Tozey, R_Badger,

I came across this thread, and as I have the same issue with the Outlook message popping up for each report (I have about 20), I decided to give your code a try. I have two issues though:

1. I still receive a pop-up message from Outlook: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this?"
2. In VBA I get a Run-time error 'Can't find this file. Make sure the path and file name are correct' and when I Debug, the highlighted code is: Set objOutlookAttach = .Attachments.Add(reportpath)

Just wondering if either of you are able to help with this?

Thanks, Kirsti

[Kirsti]

Hi again - ok, I have figured out issue 2. Duh! I hadn't specified the report name.

However, even when I allow Outlook to send the e-mail, instead of receiving the e-mailed report, I get an e-mail from the System Administrator saying that the following recipient could not be reached (only recipient is myself at this stage).

Any thoughts greatfully received, as I would love a way to bypass that Outlook message popping up for each report.

[R_Badger]

Hi Kirsti

The pop up message from outlook is an inbuilt security feature for outlook, it is possible to circumvent it, but if I recall correctly it's a pain.

You can try reading the section entitled
Using the trusted state of the Outlook VBA Project code to avoid the warning messages
http://www.everythingaccess.com/tuto...curity-Warning

With the system admin email, if you could post your code, I might be able to take a look and go from there

[Kirsti]

Thanks. I'll take a look at the website you posted, and try to figure out how to circumvent the Outlook messages. If I can't do that, I don't actually gain anything by using VBA code rather than the macro that I already have set up - so I won't bother posting the code unless I find a solution to the Outlook issue.

Thanks for your time,
Kirsti

[Kirsti]

Hi R Badger,

I copied and adjusted the code from the website you posted, and now have a button on my form that sends all of my reports with no popup messages from Outlook! I am very very excited about this! Thank you so much.

Kirsti