Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 52 votes, 5.00 average. Display Modes
Old 11-28-2005, 03:26 AM   #1
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
"A program is trying to automatically send e-mail on your behalf."

Hi,

Upgrading an Access 2000 database to Access 2003, integrating with Outlook 2003.

I get the message:
A program is trying to automatically send e-mail on your behalf.
Do you want to allow this?
If this is unexpected, it may be a virus and you should choose "No".

This interrupts the automated process in my VBA script.

I know this is caused by the Outlook E-mail Security Update, and have reviewed the workaround options in this MS article: http://support.microsoft.com/default...b;en-us;263084

The options given by Microsoft are:
1. customize the behavior of the Outlook E-mail Security Update. Our Exchange administrator is looking at this option now, but obviously it is far from desirable if it compromises the intended security features of Outlook.

2. use CDO for Windows (CDOSYS). I would like to try this but I can't find the full syntax to work with the classes in CDOSYS. I need to send messages, but also look up addresses without requiring user interaction.

3. use Extended MAPI. I would like to try this but I can't find the full syntax to work with Extended MAPI. I thought I had found an example but it used CDO 1.2 and caused the same error.

4. Create a COM add-in for Outlook. I would like to try this but the example given is for VB 6.0, and I would not know how to do this in Access.

So, has anyone had to integrate Access 2003 with Outlook 2003, and could give me syntax to create an email message which the user can check before sending, and to lookup a full or partial Outlook id in the address book and get the Outlook user's name.

Many thanks,
Keith.

KeithWilliams is offline   Reply With Quote
Old 11-28-2005, 12:52 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,381 Times in 1,316 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
I can't answer any of your questions because luckily, I haven't had to face the problem. However, there is a mail application that will work as a substitute. You can find it (and a trial version) at www.fmsinc.com.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-29-2005, 01:13 AM   #3
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
Hi Pat,

I'm not sure the product Total Access Emailer will meet my needs. It says "Total Access Emailer avoids this problem by using SMTP rather than Outlook."

However, I need to use Outlook to allow the system to validate network user ids against the outlook address book, and generate Outlook emails which the user can then preview in Outlook before sending. Sorry if I didn't make this clear in my original description.

Thanks,
Keith.

KeithWilliams is offline   Reply With Quote
Old 11-29-2005, 02:07 AM   #4
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 116 Times in 107 Posts
Mile-O will become famous soon enough
There's also Redemption although I've never used it.
Mile-O is offline   Reply With Quote
Old 11-29-2005, 05:10 AM   #5
TomAllins
Registered User
 
Join Date: Oct 2004
Location: Leuven, Belgium
Posts: 7
Thanks: 0
Thanked 1 Time in 1 Post
TomAllins is on a distinguished road
I use ClickYes (http://www.contextmagic.com/express-clickyes/) to circumvent the outlook warning.
TomAllins is offline   Reply With Quote
Old 11-29-2005, 05:38 AM   #6
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
Hi,

Thanks for all your replies. Looks like I might need to try using Redemption, which if it works as described, should allow me to do everything I need.

ClickYes will not work for me as the scripts are running as a result of user interaction, so the user would see the message popup and then disappear, which would be disorienting to say the least. It sounds as though it might compromise your security as well, if you are not very careful.

Many thanks,
Keith.
KeithWilliams is offline   Reply With Quote
Old 12-02-2005, 10:00 AM   #7
BCullenward
Registered User
 
Join Date: Jul 2005
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
BCullenward is an unknown quantity at this point
Used to have the same problem, try this

Code:
Public Sub SendMessage(strTo As String, strFrom As String, strBCC As String, strAttachment2 As String)
    'Send using the Port on a SMTP server
    Dim attList() As String
    Dim item As Integer
    Dim iMsg 
    Dim iConf 
    Dim Flds 
    Dim strHTML
    Dim stBody1 As String, stBody2 As String, stSubject As String
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.configuration")
    Set Flds = iConf.Fields
    'Set lfsFile1 = CreateObject("Scripting.FileSystemObject")
    With Flds
        .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdoSendUsingPort
         .item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR.MAIL.SERVER"
        'Use SSL to connect to the SMTP server:
        .item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        .Update
    End With

    If InStr(strAttachment2, ";") Then attList = Split(strAttachment2, ";")

    With iMsg
        Set .Configuration = iConf
        .To = strTo
        '.CC = strCC
        .FROM = strFrom
        .BCC = strBCC
        '.Subject = strSubject
        '.HTMLBody = strHTML
        '.TextBody = strMessage
        '.Nocoverpage: True
        '.confirmsend: true
        If InStr(strAttachment2, ";") Then
            For item = 1 To UBound(attList)
                .addAttachment (attList(item))
            Next
        Else
            If Not IsNull(strAttachment2) And Len(strAttachment2) > 0 Then .addAttachment strAttachment2
        End If
        .Send
    End With
    
    ' cleanup of variables
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub

BCullenward is offline   Reply With Quote
Old 12-02-2005, 11:06 AM   #8
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
You can use SMTP or Redemption, your choice. However, I think SMTP has to be on your network (Microsoft Exchange Server). Personally, I've posted on Redemption before. If the thread wasn't deleted (because of the hacking), it has some sample code.

Redemption is a .dll file that wraps your email in the extended MAPI so that it avoids the security prompt. The problem is that you have to be able to save/install this .dll on every computer that uses the database. Which is a problem if you dont have install rights on your computer and if a lot of people use the database.


Note that you can see more information about Bcullenward's sample at, which includes wrapping in HTML:
http://support.microsoft.com/kb/286431/
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post

Last edited by modest; 12-02-2005 at 12:01 PM.
modest is offline   Reply With Quote
Old 12-02-2005, 03:49 PM   #9
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Found the thread with some Redemption stuff:
http://www.access-programmers.co.uk/...ght=redemption


Additionally, with SMTP you don't need to install anything, just use a Microsoft schema, choose the port and you're set. You can send anyone a message on your mail server. Remember that Microsoft set up all this security so crackers/hackers could not send things from your mail account without you know it. This is an effort to minimize the spamming and the speed at which viruses are spread.
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post

Last edited by modest; 12-02-2005 at 10:36 PM.
modest is offline   Reply With Quote
Old 12-05-2005, 02:50 AM   #10
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
Hi,

The SMTP method works OK for part of my requirement. The problem remains, I have existing code that I use to resolve the Outlook id of a user defined on MS Exchange, including the entry of a partial id. So for example a user can enter "kwi" and the system will resolve it to "kwilliams", the same as Outlook itself can do. I use the following code:

Dim objRecipient As Recipient
Dim objMailItem As MailItem

'Create a new Outlook mail object.
Set objMailItem = gobj_Outlook.CreateItem(olMailItem)

With objMailItem
'Add the name as a recipient to the mail object, and attempt to resolve it.
Set objRecipient = .Recipients.Add(argId)
objRecipient.Resolve
If Not objRecipient.Resolved Then
:
:

This is the essential bit of the code, which uses an existing instance of Outlook in a global variable gobj_outlook.

Can the equivalent be done in SMTP mail - I guess not? Is there another way of validating user ids - the same ids will exist in Active Directory, if there is a way I can do that.

Thanks,
Keith.

PS I've posted the full code for the method I use below:

Code:
Function GetOutlookId(argId As String)
' -------------------------------------------------------------------------------------
' Resolve a specified full or partial Exchange user name and return the resolved unique
' name.
' -------------------------------------------------------------------------------------
    'Dim gobj_Outlook As Outlook.Application
    Dim objRecipient As Recipient
    Dim objMailItem As MailItem
    Dim strAddressElement() As String, strAddressId As String, strNameId As String, strNameElement() As String
    Dim lngDelayCount As Long, lngLimitDelay As Long
    
    'If a null name was passed, return an empty string.
    If IsNull(argId) Then
        GetOutlookId = ""
        Exit Function
    End If
    
    'Create a new Outlook mail object.
    Set objMailItem = gobj_Outlook.CreateItem(olMailItem)
    
    With objMailItem
        'Add the name as a recipient to the mail object, and attempt to resolve it.
        Set objRecipient = .Recipients.Add(argId)
        objRecipient.Resolve
        If Not objRecipient.Resolved Then
            'If name could not be resolved, return an empty string.
            GetOutlookId = ""
        Else
            'Split the name into two parts; the last part is the desired id.
            strAddressElement = Split(objRecipient.Address, "=")
            strAddressId = strAddressElement(UBound(strAddressElement))
            'Verify that id found matches fragment supplied, otherwise could cause problems
            'with people whose names have changed. If not, use the name found.
            If Left(strAddressId, Len(argId)) = argId Then
                'Return the found id.
                GetOutlookId = strAddressId
            Else
                'Extract the id from the resolved name.
                strNameElement = Split(objRecipient.Name, " ")
                If UBound(strNameElement) > 0 Then
                    'Construct an alternative id from the first initial and the last name.
                    strNameId = LCase(Left(strNameElement(UBound(strNameElement) - 1), 1) & strNameElement(UBound(strNameElement)))
                    'Verify that id found matches fragment supplied.
                    If Left(strNameId, Len(argId)) = argId Then
                        GetOutlookId = strNameId
                    Else
                        'No match, so revert to the original passed id.
                        GetOutlookId = argId
                    End If
                Else
                    'No match, so revert to the original passed id.
                    GetOutlookId = argId
                End If
            End If
        End If
    End With
    
    'Clean up resources used.
    Set objRecipient = Nothing
    Set objMailItem = Nothing
End Function

Last edited by KeithWilliams; 12-05-2005 at 06:31 AM.
KeithWilliams is offline   Reply With Quote
Old 12-05-2005, 06:33 AM   #11
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
Hi,

After a further review of the Microsoft support site and this site, I wonder if I can use LDAP for Active Directory to check for a user id. Does anyone have a simple code sample? What are the software requirements for a machine running Access to be able to make LDAP queries? Does it need any special LDAP client software?

Thanks again,
Keith.
KeithWilliams is offline   Reply With Quote
Old 12-07-2005, 07:25 AM   #12
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
To answer your other question: SMTP is not really a software, its a protocol. It stands for Simple Mail Transfer Protocol; you can't look up or do anything with it anymore then you could with TCP/IP information - it's just a way of setting up and sending/receiving packets. The software that handles the user information would be on the Mail Server, in this case the Microsoft Exchange Server. I'm assuming the reason you don't want to use Outlook for this easy-lookup ability is in the case someone doesn't have Outlook installed? I'm not sure why you don't keep using Outlook, but I would assume if you wanted to get down to it, the LDAP would be a way to be more independent.


Quote:
Originally Posted by KeithWilliams
After a further review of the Microsoft support site and this site, I wonder if I can use LDAP for Active Directory to check for a user id. Does anyone have a simple code sample? What are the software requirements for a machine running Access to be able to make LDAP queries? Does it need any special LDAP client software?
Haha, I see you're where I was a year ago. For some reason, at my other work, I was not able to successfully achieve everything I wanted to with LDAP, so I'm not sure if you will be able to or not. I haven't tried it on my current company's systems so I don't know if it was a programming error or a system problem.

For starters, do a search on LDAP on this forum. I think I've provided some useful links and sample code (if not I have some at home). You will need to make a reference to the ActiveDirectory library (I think it's listed as Active DS in the reference list). Not to mention, you will need your LDAP connection string/password.

-Modest
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post

Last edited by modest; 12-07-2005 at 07:46 AM.
modest is offline   Reply With Quote
Old 12-07-2005, 08:24 AM   #13
KeithWilliams
Registered User
 
Join Date: Feb 2004
Location: Exeter, UK
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
KeithWilliams
Hi Modest,

Thanks for your reply. I was using SMTP to try to get around the Outlook Security Update, which gives the message I described at the beginning of this thread.

I have already looked for LDAP posts, but the one you linked to in this forum had disappeared. I think there was a problem caused by the site being hacked recently...?

I am now experimenting with Outlook Redemption, and it looks like it might meet all my needs.

Thanks,
Keith.

KeithWilliams is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using infolabel to send a record by mail giovi2002 General 0 09-25-2005 02:45 PM
Automatically Send Email When Date in Table = Current Date guyinATL Macros 3 02-14-2005 08:49 AM
Send to mail? Nirious General 2 01-24-2005 07:26 AM
How to run VBA program automatically from database ChristineC Modules & VBA 10 01-21-2002 06:16 PM
Send e mail only once per cycle hilbertm Modules & VBA 8 12-23-2001 09:50 PM




All times are GMT -8. The time now is 05:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World