Sending Email using Excel

[alondon]

Folks,

I have been using an Excel VBA to mail 150 yacht club members a newsletter.
Works fine on old system Office 97 system at yacht club. Takes about 25
seconds - really fast.

But with my home computer with Office 2000 SP-1, I get a message after EACH
email saying I need to give permission to send the message. So, I select
the option "don't bug me for 10 minutes - everything OK I want to send these
emails" (something like that). Does not work. I still have to wait 5
seconds and click YES for each email. Pain in the but!

This is text, no attachments and the command used is .Send - nothing fancy.
Is there a way to turn this "feature" off using the Outlook security
settings, I have already set the Outlook macro security to Low to no avail.?

Thanks,

Allan P. London, CPA

 

[Jocelyn Fiorello [MVP - Outlook]]

Are you sure you only have SP1 on Outlook 2000? Those security features
were introduced in SP2. In any event, take a look at the following page
for an explanation of why this message pops up and what you can do to
make it less annoying, if not eliminate it entirely:

http://www.slipstick.com/outlook/esecup.htm#autosec

--
Jocelyn Fiorello
MVP - Outlook

*** Replies sent to my e-mail address will probably not be answered --
please reply only to the newsgroup to preserve the message thread. ***

 

[alondon]

Jocelyn,

You are certainly "Most Valuable Person". Almost there (I think).

Ok, I downloaded Redemption and installed redemption.dll and referenced its "SafeOutlookLibrary" (used Tools|Reference as this is not an add-in right?)

Now I have to reference the redemption objects which means changing or adding to the following code - see highlighted items green is out and red is in. All very logical, but it doesn't work? Hangs Outlook. So, what is wrong with my thinking here?

Sub NewMailMessage(thesubject As String, _
theBody As String, _
theAddress As String, _
theAttachment As String, _
theVersionSelected As String)

'Note Outlook Type Library must be referenced

Dim myOutlook As New Outlook.Application
Dim myOutlook As New Redemption.SafeContactItem
Dim newMail As Outlook.MailItem

On Error GoTo ErrorHandler

'Create a new mail message item
Set newMail = myOutlook.CreateItem(olMailItem)
Set newMail = New Redemption.SafeContactItem

With newMail
' **Add the subject of the mail message
.Subject = thesubject
' **Create some body text
.Body = vbCrLf & theBody & vbCrLf & vbCrLf
' **Add a recipient
.Recipients.Add (theAddress)
' **Attach a file
' **.Attachments.Add theAttachment, olByValue, , theVersionSelected
' **Send the mail message
.Send
End With
Exit Sub

ErrorHandler:

'Release memory
MsgBox "Outlook has created an error and your version has not been sent."
Set myOutlook = Nothing
Set myNameSpace = Nothing
Set newMail = Nothing

End Sub