Repetitive security warning

[Don Wells]

The code listed below was adapted from a post by one of our knowledgeable loungers. When running it from Excel 2003 SP3 I get the following warning once (that's OK), or sometimes two or three times before the e-mail is shown (that's aggravating. Can someone explain why the message is appearing more than once? And suggest a cure?

T.I.A.

2011-08-27_0955.png

Code: Select all

Option Explicit
    Sub SendMessage()
        Dim objApp As Object
        Dim objMsg As Object
        Dim objRec As Object
        Dim objAtt As Object
        On Error Resume Next
        Set objApp = GetObject(, "Outlook.Application")
        If objApp Is Nothing Then
            Set objApp = CreateObject("Outlook.Application")
            If objApp Is Nothing Then
                MsgBox "Failed to start Outlook.", vbCritical
                Exit Sub
            End If
        End If
        On Error GoTo ErrHandler
        Set objMsg = objApp.CreateItem(0) ' olMailItem
        objMsg.Subject = "Water Meter Reading"
        objMsg.Body = Range("N25") & "  " & Range("O25") _
          & vbCrLf & Range("N29") & "  " _
          & Format(Range("O29").Value, "#.00")
        Set objRec = objMsg.Recipients.Add("myaddress.ca")
        objRec.Resolve
'        Set objAtt = objMsg.Attachments.Add("C:\Files\Test.zip")
        ' Send
'        objMsg.Send
        ' or show
        objMsg.Display
        Exit Sub

ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub

[HansV]

I don't know why the code would display the warning more than once, but you basically have three options:

- Install ClickYes. This free utility will click "Yes" in the warning message for you.
- Install Outlook Redemption and rewrite your code to use this library (examples are provided on the site).
- If you're on Microsoft Exchange, the Exchange Administrator can turn off the warning message.

[Don Wells]

Thank you Hans

[ChrisGreaves]

Thank you Hans

I'm with Hans. I use ClickYes on my Word2003 mailings.
Note however, that even ClickYes can't evade the annoying 5-second count down in the Word box; multiply that 5 seconds by 100 emails going out and you have a significantly-lengthy mail run.

[HansV]

I agree that's a minor annoyance, but it's actually not so bad - if you try to send 100 emails in one go, chances are that your ISP will block some of them. Many ISPs limit the number of emails you can send at once (or within a short period of time), to conserve bandwidth and to prevent spamming (intentional or unintentional).

[Don Wells]

Thank you Hans

I'm with Hans. I use ClickYes on my Word2003 mailings.
Note however, that even ClickYes can't evade the annoying 5-second count down in the Word box; multiply that 5 seconds by 100 emails going out and you have a significantly-lengthy mail run.

    Thanks for the heads-up Chris. That's not a problem for me as I never send out batch mailings now that I'm fully retired. ClickYes is just what I was looking for.

[Don Wells]

even ClickYes can't evade the annoying 5-second count down in the Word box; multiply that 5 seconds by 100 emails going out and you have a significantly-lengthy mail run.

Hi Chris
    Try the following:

• Change your code to Display the message and not send it.
• Ensure that ClickYes is active
• Run your automated message code
• Without waiting for the code to complete, hold down the Alt + S keys.

    Using this technique, I just mailed 25 messages to myself in about 3 seconds.

[ChrisGreaves]

... Without waiting for the code to complete, hold down the Alt + S keys.

Hi Don.
I've not tried this, but I'm sure it works as advertised.
The trouble with this solution is that it requires me to perform a boring and repetitive manual task.

The point of me automating the mail run was to have it run unattended.

Now if there are only 5 emails going out this morning, and I'm in the shower, it's no big deal.
But if 100 events are tracked this morning so that 100 emails need to go out, and I showered earlier in the day, I have to wait 500 seconds for the job to complete.

The number of emails corresponds to the number of news items found that match client criteria, so I can't know in advance whether to run automatic or switch to the manual process, and even if my VBA program code alerted me to the manual process, I'd possibly not be near enough to see/hear that today I ought to switch to a manual process.

There's no escaping the fact that when MS turns an automated process into a manual process, it has become a manual process.

[Don Wells]

Hi Chris
    Now I understand. I surmised that you were bulk mailing a common message to many recipients; not an automated clipping service.
    Sounds like a task for a dedicated BBB.

[ChrisGreaves]

Sounds like a task for a dedicated BBB.

Hi Don. Yes, it could be a task for a dedicated BBB, whatever that is

The download, storage and analysis of about 400 press releases is mercifully brief - about 60 seconds, I think. The system generates anything from 2 to 12 emails in the morning.
The Prospector

[Don Wells]

dedicated BBB, whatever that is

I suspect that if you opened a new thread on the Lounge with this question, someone would inform you that it is:

Spoiler

Chris Greaves' Big Beige Box

as used so eloquently here.

[ChrisGreaves]

Spoiler

Chris Greaves' Big Beige Box

as used so eloquently here.

:stupid: :stupid: :stupid: :stupid: me

Ha!
OK, I gave this some thought. I could set up an Outlook 2003 mail account on the BBB, and have the BBB poll a file on the laptop every ??? minutes. In fact that'd make the BBB a sort-of mail server for all/any automated tasks.

But Holy Shopping Mall! here I be setting up a networked process to circumvent a problem which doesn't really exist.

If MS had been thoughtful they'd have provided a registry tweak for those of us who Know What We Are Doing^(TM).

[Don Wells]

those of us who Know What We Are Doing

Do you think that we can convince MS such a creature exists?