troubleshooting Question

MS Excel/Access emailing gets security warnings. Should I use COM or SMTP to avoid them?

Avatar of rberke
rberkeFlag for United States of America asked on
Microsoft AccessOutlookMicrosoft Excel
10 Comments1 Solution785 ViewsLast Modified:
I would like some expert comment on my proposed solution for a simple MS Office e-mail application.

This is a generic solution that I am sure hundred of developers have encountered, but I feel that Microsoft has made my life a lot more complex than it should be.

My prototype solution is in Vba, but it is getting excessive security notifications.  

http://msdn.microsoft.com/en-us/library/Aa168346 says a COM application can avoid the security messages. I have never made one before.  Is it easy enough that I should consider doing so?

----- here are some details of my application in case anybody is interested.  -----------

We run sbs2003 with 10 XP pro clients and 2 windows 7 pro clients.  All clients use outlook 2003 and Office 2003.  Exchange is not currently being used.  Application is in MS Access 2003.

When my coworker, JaneDoe clicks a button, the application sends two messages, one to our customer, and a different message to our company's president.

In my prototype, a variation of the attached subroutine is used to send the emails.

Naturally, LineA of the program triggers the warning message that we all know and love:

    a program is trying to access e-mail address you have stored in outlook.
    Do you want to allow this?
 
    Allow access for   10 minutes.

Then, LineC of the program triggers the message

      "a program is trying to automatically send email on your behalf"

The LineA message does not bother me because JaneDoe can disable it for 10 minutes.

The LineB message drives me crazy because it comes out for every message that is sent. Sometimes Jane will push the button 4 or 5 times in a minute.

Microsoft is absolutely correct that people should not be able to entirely disable these annoying messages.

But, if JaneDoe sees the message once, there should be a way of suppressing it for a few minutes.

I am going to work around this problem by installing an SMTP server, and use it to send the emails. But, the causes the email audit trail to be lost because that SMTP server is not integrated with the Outlook Sent Items folder.

Consequently, I will CC JaneDoe so at least she can get the audit trail from her Inbox.  It is a little weird, but better than nothing.

I hope someone can come up with a simpler solution.

 

Sub test()
Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient
    Set OLF = GetObject("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        .Subject = "Delete this test message now" & now() ' message subject
lineA: .Recipients.Add "customerName@CustomerCompany.com" ' add a recipient
LineB:  Set ToContact = .Recipients.Add(Environ("username") & "@OurCompany.com") ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC
        .Body = "This is the message text" & Chr(13)
        .Attachments.Add "C:\e\aaatmp\confirmEmails.xls", olByValue, , _
            "Attachment" ' insert attachment
LineC:  .Send ' sends the e-mail message (puts it in the Outbox)
        On Error Resume Next
        .Send ' for some reason, a second send is often required in Windows 7
        On Error GoTo 0
    End With
    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros