Send Access reports in email with Security Warning

[bharlow]

Is there a way to disable the Outlook security warnings so that I can send email from Access with clicking "Allow" Program to send email on my behalf?

 

[pbaldy]

Some thoughts here:

http://www.mdbmakers.com/forums/showthread.php?t=3147

I've used ClickYes successfully.

 

[Mike375]

I also found Yes/No/Pro good. I have only used the free trial version and with mine if I reboot the computer I have to add the application (MSACCESS) back in. When the computer turns back on the Yes/No screen to add an application opens. But this might apply to the one you buy and even it did it wouldbe much of problem if for your own use.

I also tried it with emailing myself a 100 times on an auto basis and no problem.

http://www.contextmagic.com/express-clickyes/

I think the standard Yes/No is OK if you are not going to send a bunch of emails in one run.

 

[CameronM]

Using the Outlook application object in Acccess you can try saving the messages as Drafts, instead of sending straight away. It is then a fairly easy task to send the messages from the outlook drafts folder.

Alternatively, using an application like, Mike375 suggested, is also an option. From memory, the version of ClickYes I used, which was free at the time, still had to wait for the Yes button to become active before it could automatically press yes, so it wasn't good for sending bulk emails. This may have changed in recent releases.

I know I have code somewhere, so if you need further help let me know.

 

[Mike375]

From memory, the version of ClickYes I used, which was free at the time, still had to wait for the Yes button to become active before it could automatically press yes, so it wasn't good for sending bulk emails. This may have changed in recent releases.

That is supposed to be the case with Yes/No but Yes/No/Pro just bypasses and goes srtraight through.

I have SMTP doing about all I can do with Outlook including putting formatted contents of Word in the body but I can't get it to show in the Send box whereas that is not a problem with Outlook.

I find SMTP more reliable than Outlook. Outlook seems to leave stuff in the Outbox at different times, although most they clear if you open Outlook but not veryone uses Outlook as their Default. I don't as I prefer Outlook Express and Windows Mail.

It all has its moments

 

[bharlow]

I tried the ClickYes Pro and it worked to send the first email without the security warning but then it would not go to the next record and send the next email. It just stops after the first email.

Any thoughts?

 

[Mike375]

I tried the ClickYes Pro and it worked to send the first email without the security warning but then it would not go to the next record and send the next email. It just stops after the first email.

Any thoughts?

It does not automatically send lots of emails it just by passes the security. Its up to what you make so you move through a bunch of records and have an email go to each person.

Or are you saying you have that set up but Outlook stopped it running.

 

[bharlow]

Before I put ClickYes Pro on my PC the access macro would progress through all records and send each store their email. It just forced me to click "allow" each time.

I am using the actions SendObject and GoTo record. It worked except for the security warning. Now, for some reason, my program to bypass the security warning is causing the macro to stop after sending one email.

Thanks for any advice

 

[Mike375]

Have you the macro that does SendObject and then GoToNextRecord run from another macro with Runmacro action.

Also, the form that the macro is appying to, are there any other forms open behind it. If so, check if one of those form has go to the next record. If that is the case then you will need to add a SelectObject action and with the form in question being the object.

One difference you now have is speed.

 

[Atomic Shrimp]

I ran up against this very problem this morning. I can't install ClickYes or anything like that, due to corporate policy, etc.

Using automation, via the Oulook object, doesn't raise the warning (at least, not in my setup), whereas it did with SendObject.

I'm using this function:

Public Sub SendMessage(strTo, strSubject, STRBodyMessage As String, Optional AttachmentPath, Optional BCC, Optional strBCC)
'*********************************************************
'* Send an email message with optional attachment        *
'* using Outlook Automation.                             *
'* requires selection of the MS Outlook object reference *
'*********************************************************
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'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(strTo)
objOutlookRecip.Type = olTo

'Add the CC recipient(s) to the message (enable code if required)
'Set objOutlookRecip = .Recipients.Add(strCc)
'objOutlookRecip.Type = olCC

'Add the BCC recipient(s) to the message (enable code if required)
'Set objOutlookRecip = .Recipients.Add(strBcc)
'objOutlookRecip.Type = olBCC


'Set the Subject, Body and Importance of the message
.Subject = strSubject
.Body = STRBodyMessage
.Importance = olImportanceNormal

'Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

'Resolve each recipients name, for use with Outlook address book
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[Mike375]

Using automation, via the Oulook object, doesn't raise the warning (at least, not in my setup), whereas it did with SendObject.

I need ClickYes with code but perhaps that is because I am opening Word and having the content/format of the Word doc go in the email body.

 

[Atomic Shrimp]

I need ClickYes with code but perhaps that is because I am opening Word and having the content/format of the Word doc go in the email body.

I get the warning if I use the built in SendObject function, but not if I use the custom sub I posted. I'm on Office 2007, which might make some kind of difference, I suppose...

 

[bharlow]

I'm also on Office 2007.

Does the sub code run without the security warning?

If so, please excuse my ignorance in this matter, but where does the sub code go? Do I keep my current macro, which runs through each record, and add this to send the email??

 

[Atomic Shrimp]

The sub code just sends the email, no warnings or anything (for me, at least).

To use it, you need to create a new module, then paste the code into it. While you're there in the VB window, click Tools>References and check that there's a tick mark next to 'Microsoft Outlook xx.x Object Library'

Then you can use the SendMessage function just like you would any other function

 

[bharlow]

I added the module and loaded the code. I checked the Microsoft Outlook 12.0 Object Library.

I can not use the SendMessage function as an action item in a macro. Does this function only work with more code?

 

[Atomic Shrimp]

Hmmm...

I think it might work if it's changed to a public function instead of a sub, so change the first line to:

Public Function SendMessage(strTo, strSubject, STRBodyMessage As String, Optional AttachmentPath, Optional BCC, Optional strBCC) As Boolean

And probably put this line somewhere near the end:
SendMessage = True

 

[bharlow]

Mike,

I can not seem to utilize this code. Can you give me a short example of how to send an Access report email using this code?

Thank you very much for all your help and ideas.

 

[Mike375]

Here is some simple code if you want to experiment

Public Function MailAttach()
Dim OutMail As Object
Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)


With OutMail
.To = [Forms]![PrintAndClose]![EMStore]

.Subject = "Test Email"
.Attachments.Add ("C:\StoreLetters\" & Format([Forms]![PrintAndClose]![LNameNoDoc]) + ".doc")
.Body = [Forms]![PrintAndClose]![EMMessage]
.Send
End With
End Function

To run that first copy and paste the whole thing into a module.

Then in a macro use RunCode action and that will be MailAttach()

Alternatively you can copy the section between
Public Function MailAttach() and End Function and paste it into the code builder for a button or stand alone label. You paste it betweem where it refers to the button name and End Sub.

This does an attachment plus a message in the email body and is on a dynamic basis. That means it draws the data for the email address, the email message and the name of the file to be attached...from the Access record. If you look you can see it is being run against a form called PrintandClose. if it was not being done on a dynamic base then

("C:\StoreLetters\" & Format([Forms]![PrintAndClose]![LNameNoDoc]) + ".doc")

would become

("C:\StoreLetters\Whatever.doc")

The best way to avoid the YesClick situation is to use SMTP as that does not use Outlook.

I don't know how you send a report but a search should get you the answer. I suspect that if you only want to send a report and with a message in the emal then SendObject in either macro or VBA is the best option. However, if you want to have email do such things as put the contents of a Word doc with Word formatting in the body of the email and perhaps include data that gets inserted into Word bookmarks in that email body then you need to similar to above (but is much longer because Word gets involved) or SMTP

 

[bharlow]

Mike,

Thank you very much for all your help.

 

[Mike375]

Here is something that might be of use to you for emailing reports

http://www.lebans.com/reporttopdf.htm

Download the top option. This will put a small DB and a couple of dll files on your computer. Extract to the folder where you do your stuff

This converts reports to PDF files. Ideally you would move the code to your own DB where the Reports are made. You can import your reports to the DB but that of course would also need the table/query.

By deafult is saves the PDF using a filename the same as the name of your Report and saves it to the folder where the DB resides.

One it is a PDF then you can use the above code to send as an attachment. One the advantages of a PDF over snapshot is that not everyone has the snapshot on their computer. You can also include more than one attachment in an email. On the above code just copy the line for the attachment and paste under and change the file/path etc. Above each attachment line put

On Error Resume Next

That means if the file is no longer there is just goes past the line and onto the next line.

I think SendObject requires and email for each one.