Good morning, I am trying to sen

[Cornerstone]

Good morning,

I am trying to send a snapshot of a report using vba from within Access. Outlook security prompts me for permission to have my vbs code send the e-mail. I would like to have a solution that would still prompt a user for scripts that may run from "untrusted" sources but not from my code. However, I am willing to turn off the security feature altogether to avoid this prompt. Any ideas?

Rick

 

[BillNielsen]

What code are you using?

I use code like this and never get the prompt:

'*********************************************
Private Function MailMessage() As Boolean

Dim objOutlook As Outlook.Application
Dim strText As String
Dim strFile As String
Dim rsUsers As Recordset

On Error GoTo errHandler

Set objOutlook = New Outlook.Application


With objOutlook.CreateItem(olMailItem)

'set the subject body and text of the msg
strText = "This is the data you requested"
strText = strText & vbCr & vbCr & cSignature & vbCr & vbCr
.Subject = getSubject
.Body = strText
.Importance = olImportanceNormal

'Add attachments to the Email
'it is assumed that the spreadsheet file has already been created
.Attachments.Add (cDailyExportedExcelFile)
'.Attachments.Add (cMontlhlyExportedExcelFile)


Set rsUsers = CurrentDb.OpenRecordset("SELECT * FROM tblUsers", dbOpenSnapshot)

If Not rsUsers.EOF Then
rsUsers.MoveFirst
Do While Not rsUsers.EOF
.Recipients.Add (rsUsers.Fields(0).Value)
rsUsers.MoveNext
Loop

Else
MsgBox cNoUsers & Chr(13) & Chr(13) & cCheckUserForm, vbCritical, cCannotProceedTitle
End If

.Display
'.Send
MailMessage = True
End With

CleanUpCode:

On Error Resume Next
rsUsers.Close
Set rsUsers = Nothing
Set objOutlook = Nothing

'lastly we don't want to litter the users harddrive with
'excel files so delete the file
Kill cDailyExportedExcelFile
Kill cMontlhlyExportedExcelFile
Err.Clear
Exit Function

errHandler:
'notify user of what went wrong and exit this module
MsgBox Err.Description, vbCritical, "Cannot Proceed"
Resume CleanUpCode
End Function

'******************************************************

Are you trying to do something similiar to what I'm doing?

Bill Nielsen

 

[Cornerstone]

My code is simple:

Dim msg As Variant
On Error Resume Next
msg = InputBox("Add a message to the e-mail?", "Title&quot

If Forms![frmreportselector]![chkCAR].Value = True Then
DoCmd.SendObject acSendReport, "rptCreditApprovalReport", "Snapshot Format", "mailaddress", , "mailaddress", varapplicationid & " Credit Approval Report", msg, False
End If

I did try your code to no avail. Do you have Outlook 2000 SR2 installed?

 

[BillNielsen]

No I have Outlook 98 but did you make a reference to the type library?

I actually have this function inside this function:

Public Function SendMessage() As Boolean
'This procedure will send an Emails to all users
'The Outlook type library must be referenced for this module to even compile
'To create a reference to Outlook, goto Tools->references->Outlook 98 and click
If CreateExcelFiles Then
If MailMessage Then SendMessage = True
End If


End Function

I'm thinking that you would be doing something similiar as in:

If CreateSnapShotReports Then
If MailMessage Then SendMessage=True
End If

In other words, I first create a function that returns true/false as to the success of Creating a snapshot of your reports, then if it was successful (returns true) send the Email message with the reports attached.

Your code is simple and it does the work of creating the report in a snapshot format but like many other Access users have complained, DoCmd.SendObject is unreliable and that is why writing the extra code and getting the extra control is worth the few lines.

Bill Nielsen