Stop OUTLOOK Security Madness

[Guest]

I am trying it get the following code in Access to call Outlook and send a
bunch of emails. Each one is different for each of the recipients and it can
be for one to 50 or even more. I would like to bypass the Outlook security,
preferably not using on of the 3rd party products like ClickYes.

I am using Outlook and Access 2003.

I have looked at other posts here and in the Outlook Discussion Group and at
http://www.outlookcode.com/d/sec.htm
but I just don't get it.

TIA

Vanya

******* Start Record Set Code **************************************
Private Sub cmdMailTotalPoints_Click()
On Error GoTo Err_CmdMailTotalPoints_Click
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT <stuff> FROM <mytable>")
Set qdf = dbs.QueryDefs("<myQuery>")
BaseSQL = qdf.SQL
strFrom = "<whofrom>"
strSubject = "<Subject>"
strBodyEnd = vbCrLf & _
"If you have any questions..." & vbCrLf & _
vbCrLf & _
"Thank you ..."
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL) - 1)
qdf.SQL = strSQL
strEmail = !HWorkEmail
strBodyStart = "" & !FirstName & ", " & vbCrLf & _
vbCrLf & _
"Your current account info is..." & vbCrLf & _
Stuff... vbCrLf
strBody = strBodyStart & strBodyEnd
' Call the Mass Mailing Routine
Call MassMail(strFrom, strBody, strEmail, strSubject)
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing
******* End Record Set Code **************************************

******** START OUTLOOK CODE ************************************
Private Sub MassMail(strFrom As Variant, strBody As Variant, strEmail As
Variant, strSubject As Variant)
Dim olApp As Outlook.Application
Dim olMsg As MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMsg = outApp.CreateItem(olMailItem)
With olMsg
.To = strEmail
.SentOnBehalfOfName = strFrom
.Subject = "TEST TEST Please Delete TEST"
.SenderEmailAddress = strFrom
.Body = strBody
.Send
End With
Set olApp = Nothing
Set olMsg = Nothing
End Sub
******** END OUTLOOK CODE ************************************

 

[Baz]

If you insist on automating Outlook then the easiest way to get around the
stupid security warnings is to use Redemption:

http://www.dimastr.com/redemption/

It's free to download for development, but you need to buy a licence to
distribute it (and it's worth every penny unless you put a very low value on
your time).

However, automating Outlook is a pretty clunky way of doing bulk mailings.
The most elegant way of doing this that I have yet discovered is to use
CDONTS. Something like this will send a single message, without requiring
any additional references:

Dim CDOMessage As Object
Dim CDOConf As Object
Dim CDOFlds As Object

Set CDOConf = CreateObject("CDO.Configuration")
Set CDOFlds = CDOConf.Fields
CDOFlds("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
CDOFlds("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "my
SMTP server name"
CDOFlds("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
25
CDOFlds.Update
Set CDOMessage = CreateObject("CDO.Message")
Set CDOMessage.Configuration = CDOConf
With CDOMessage
.From = strFrom
.To = strEmail
.Subject = "TEST TEST Please Delete TEST"
.TextBody = strBody
.Send
End With

 

[Guest]

For a start, you need to use just one Outlook Application.
Opening then closing Outlook for each message would be
foolish even if it didn't throw up a warning.

And you don't need to 'send' your mail messages either.
Just 'save' them. Let the user open Outlook and 'send'
your 'saved' messages. You get rid of all those confirmation
messages, and the user gets better control anyway.

But actually, you don't want Outlook at all. That is like
opening then closing a new Access application every time
you want look at a table or query! I see that you are actually
using CurrentDB, which is a much smaller and faster DAO
object, rather than creating a new Access Application object
in your code. The same for Outlook: you want to use one
of the CDO objects or something similar, not
Outlook.application.


(david)

 

[Guest]

Baz & David,

Thanks for the ideas! I'll give them a try.

Vanya