"Do you want to allow this" messages driving me mad! Tried Clickyes and Redemption

[Biguana]

Hi there,

I know this has come up many times before, so excuse the long
explanation, but I've searched and searched and can't solve the problem
satisfactorily.

This is my story so far.

Initial task:
Wanting to send automated personalised admin emails (not spam!) to a
couple of hundred training partners via outlook (sometimes including
personalised attachments etc). I started trying to use Outlook VBA
objects from Excel and Access as I've done many times before.

The problem:
The 2 messages:
a) "A PROGRAM IS TRYING TO ACCESS EMAIL ADDRESSED YOU HAVE
STORED IN OUTLOOK. DO YOU WANT TO ALLOW THIS."
Not really a problem as it's possible to allow access for ten minutes -
easily long enough.
b) "A PROGRAM IS AUTOMATICALLY TRYING TO SEND EMAIL ON YOUR
BEHALF."
Herein is the real problem. Not only does this pop up once for every
email, but it insists on a 5 second delay! Why can't it allow ten
minutes like the other warning? This requires 200 mouse clicks with a
5 second delay each time. (

Solution 1:
Download and install "Express Clickyes". First time I've used this.
Seems to do the job well enough. Saves me writing API calls to click
yes myself. I've used the APIs to enable and disable it as required.
( http://www.vb123.com/toolshed/05_docs/stopoutlookmessages.htm )
Problem:
The 5 second wait is still present. For 200 emails that means over a
quarter of an hour of waiting for that progress bar (plus the time for
the code to run of course).

Solution 2:
Download and register the redemption.dll (
http://www.dimastr.com/redemption/ ). Use the safe Outlook objects.
Problem:
It doesn't work. Emails sit in the Drafts folder italicised going
nowhere. I assume this has to do with No.1 on the Redemption FAQ page.
But it's worse than that makes out - even send & receive doesn't work.
The emails have to be individually sent. And the body has been placed
into one long line (no crlfs). I'm using Outlook 2002, SP3 connecting
to an IMAP Server.

Please can anyone help?

Thanks,

Tim

 

[Ken Slovak - [MVP - Outlook]]

Show the Redemption code you're using. I use Redemption all the time without
those problems. Certainly the items remain in Drafts until sent but that's
cosmetic and does not change anything in terms of functionality.

 

[Biguana]

Hi Ken,

Thanks for the reply. The weird thing is that the items sit in drafts
italicised (as I mentioned), as if they're ready to go, but the only
way I can induce them to do so is by going into each one and sending
separately (defeating the object). Also, is the lack of line breaks
usual with redemption?

Some code is below. The utils.DeliverNow seems to execute a
Send&Receive OK (judging by the pause), but to no avail.

Thanks for your time.

Tim


Sub Sendmails(intAddressCount As Integer, oOutlookApp As
Outlook.Application)

Dim i As Integer
Dim oItem As Outlook.MailItem
Dim rItem As Redemption.SafeMailItem
Dim utils As Object


On Error GoTo ErrHandle

For i = 1 To intAddressCount
'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)
Set rItem = CreateObject("Redemption.SafeMailItem")

With oItem
'Set the recipient for the new email
.To = Replace(Sheet2.Cells(i, 1).Value, """", "")
'Set the subject
.Subject = Sheet1.Cells(1, 2).Value
.Body = Sheet1.Cells(2, 2).Value & vbCrLf & vbCrLf
& Sheet1.Cells(3, 2).Value
rItem.Item = oItem
rItem.Send
Set utils = CreateObject("Redemption.MAPIUtils")
utils.DeliverNow
'.Send
End With

'Clean up
Set oItem = Nothing
Set rItem = Nothing
Next
Exit Sub
ErrHandle:
MsgBox "Error Occurred - " & vbCrLf & Err.Description

End Sub

 

[Ken Slovak - [MVP - Outlook]]

As is mentioned on the Redemption Web site MAPIUtils.DeliverNow doesn't do
much for you with Outlook 2002 and later since the MAPI spooler isn't there
any longer. I use the alternate method of invoking the Send or Send/Receive
menu commands Dmitry lists on the Web site.

What format are the messages in, is it HTML? If so you'd need to use
HTMLBody instead of using Body. In HTML vbCRLF means nothing, you'd need to
use HTML formatting <br> instead of using vbCRLF. Also, what about the
contents of the cells? As an experiment try setting Body to some fixed
string:

oItem.Body = "The quick brown fox" & vbCRLF & vbCRLF "jumps over the lazy
dog."

I also generally save my items before assigning a SafeMailItem object to
them, I find it works better and sets all the properties so the MAPI used by
Redemption is able to access them properly.

 

[Biguana]

OK. I've now sorted the carriage returns, it was in HTML previously.
Thanks.

But the main problem is that they will not send!!! I've tried saving
them previously, and again since you mentioned it, but it makes no
difference.

In the drafts folder (when italicised) they have a "sent date" and time
from when the code ran, but if I open and close them that disappears,
and they are no longer italicised.

I wouldn't mind manually clicking send and receive, but manually
opening and sending 200 emails is not reasonable. Can anyone help?

Tim

 

[Ken Slovak - [MVP - Outlook]]

Did you look at that entry in the Redemption FAQ?

Here is the later part of that FAQ entry, it has the answer you're looking
for:

There is however one problem if you are using Outlook 2002 with a PST file
and POP3/SMTP transport provider or Outlook 2000 installed in the Internet
Only Mode: there is no way to flush the queues using Extended MAPI. That
part of Outlook is simply broken. Note however that Outlook 2002/2003
(online) with an Exchange Server or Outlook 2000 C/W in any configuration
are fine.

If you are using Outlook 2003 with Exchange in a cached mode, it will
exhibit the same problem. Uncheck "Use cached mode" in the Exchange Server
properties to force online mode - that will ensure that messages are
delivered immediately.

Microsoft is aware of the problem, and they are working on providing a
workaround. As a last resort, you can simulate clicking "Send/Receive"
button in Outlook after sending a message:



MailItem.Send

Set Btn = Application.ActiveExplorer.CommandBars.FindControl(1, 5488)

Btn.Execute



Note that in Outlook 2003 that button is now a dropdown, the real
Send/Receive is a subitem of the button:

Set Btn = Application.ActiveExplorer.CommandBars.FindControl(1, 7095)
Btn.Execute

Note that the code above assumes that there is an active Explorer; this will
not be the case if you start Outlook programmatically (and it was not
previously started by a user) and do not display any folders. In this case
you can start a sync using the the Namespace.SyncObjects collection.

set NS = Application.GetNamespace("MAPI")
NS.Logon
Set Sync = NS.SyncObjects.Item(1)
Sync.Start

 

[Biguana]

Thanks Ken,

I've already been through all the FAQs (and that one in particular
several times). As I understand it the point is that my Outlook setup
can't automatically send (because it's broken) and therefore needs
another way to send & receive, so it provides ways to do this
programatically.

Well that's fine - I can do that, but send & receive doesn't affect
these emails (as mentioned three times previously) whether done
programatically or by clicking the button.

So I am still very much in need of a way to solve the redemption issue
or a new approach entirely.

Thanks,

Tim

 

[Ken Slovak - [MVP - Outlook]]

Have you tried using the SyncObjects? I use that and it works fine here. The
items go out and don't just sit in Drafts.

 

[Biguana]

Yup.

Again, they force a Send & Receive, but leave the items in the drafts
folder.

Tim

 

[Biguana]

I have eventually given in and upgraded to Outlook 2003 in the
desperate hope that it will make a difference, but still to no avail.
Emails just sit there in the drafts folder, all italicised and grinning
at me, but they never send.

Can anyone help?

Please?

Tim

 

[Ken Slovak - [MVP - Outlook]]

Are you releasing all references to the mail object after sending? That
would keep it from going out. So would attempting to access it in the Drafts
folder.