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_doc...okmessages.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

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Biguana" wrote in message
ups.com...
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_doc...okmessages.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

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] wrote:
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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Biguana" wrote in message
ups.com...
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_doc...okmessages.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

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Biguana" wrote in message
ps.com...
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

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] wrote:
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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Biguana" wrote in message
ps.com...
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

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


--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm


"Biguana" wrote in message
ups.com...
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