Sending email from Excel vba problem

We have just upgraded from Office 97 to 2003.

I have an Excel macro that sends an email with Outlook to inform of client changes. This worked fine in 97, but in 2003 you now get a message box saying:

You have a program trying to access addresses stored in Outlook. Do you want to allow this?

If you choose the Yes button 3 times it works, but choosing No brings up a VB error. I have tried reducing the security level in Outlook, but the message still appears.

Is it possible to hide this box and get it to choose Yes by default?

Paul

Hi Paul

See
http://www.rondebruin.nl/mail/prevent.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"PaulC" <PaulC.1qvpmc_1119193506.8284@excelforum-nospam.com> wrote in message
news:PaulC.1qvpmc_1119193506.8284@excelforum-nospam.com...
>
> We have just upgraded from Office 97 to 2003.
>
> I have an Excel macro that sends an email with Outlook to inform of
> client changes. This worked fine in 97, but in 2003 you now get a
> message box saying:
>
> YOU HAVE A PROGRAM TRYING TO ACCESS ADDRESSES STORED IN OUTLOOK. DO YOU
> WANT TO ALLOW THIS?
>
> If you choose the Yes button 3 times it works, but choosing No brings
> up a VB error. I have tried reducing the security level in Outlook, but
> the message still appears.
>
> Is it possible to hide this box and get it to choose Yes by default?
>
> Paul
>
>
> --
> PaulC
> ------------------------------------------------------------------------
> PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
> View this thread: http://www.excelforum.com/showthread...hreadid=380386
>

Ron

Thanks for your reply. I will try as soon as I return from a training course next week.

Paul

Hi Ron

I have just installed Express ClickYes which is working - many thanks for that. However, it brings up Outlook with the "Do you want to allow...." message and when it has clicked on Yes, the email is sent, but Outlook remains on top.

Is there a way of keeping Outlook hidden during this process (as happened with Office 97) or at least returning to Excel at the end with Client List Current.xls activated?

This is to stop users getting into the wrong version of Excel afterwards.

The relevant bit of code I am using is:

Sub SendEmail()

Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.Recipients.Add "reception.oak@yyy.com"
.Subject = "IAS Reps Changes"
.Body = "Please find attached IAS and Reps changes Spreadsheet"
.Attachments.Add ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
.ReadReceiptRequested = True
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

Windows("Client List Current.xls").Activate
Sheets("Current").activate

End Sub


Thanks

Paul

Hi Paul

I never used ClickYes

Can't you use CDO ?
http://www.rondebruin.nl/cdo.htm
No problems then



--
Regards Ron de Bruin
http://www.rondebruin.nl


"PaulC" <PaulC.1rbzql_1119953126.8145@excelforum-nospam.com> wrote in message
news:PaulC.1rbzql_1119953126.8145@excelforum-nospam.com...
>
> Hi Ron
>
> I have just installed Express ClickYes which is working - many thanks
> for that. However, it brings up Outlook with the "Do you want to
> allow...." message and when it has clicked on Yes, the email is sent,
> but Outlook remains on top.
>
> Is there a way of keeping Outlook hidden during this process (as
> happened with Office 97) or at least returning to Excel at the end with
> Client List Current.xls activated?
>
> This is to stop users getting into the wrong version of Excel
> afterwards.
>
> The relevant bit of code I am using is:
>
> Sub SendEmail()
>
> Dim olApp As Outlook.Application
> Dim olMail As MailItem
>
> Set olApp = New Outlook.Application
> Set olMail = olApp.CreateItem(olMailItem)
>
> With olMail
> Recipients.Add "reception.oak@yyy.com"
> Subject = "IAS Reps Changes"
> Body = "Please find attached IAS and Reps changes Spreadsheet"
> Attachments.Add ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
> ReadReceiptRequested = True
> Send
> End With
>
> Set olMail = Nothing
> Set olApp = Nothing
>
> Windows("Client List Current.xls").Activate
> Sheets("Current").activate
>
> End Sub
>
>
> Thanks
>
> Paul
>
>
> --
> PaulC
> ------------------------------------------------------------------------
> PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
> View this thread: http://www.excelforum.com/showthread...hreadid=380386
>

Many thanks Ron. I will try CDO when we get back from Scotland mid July.

Paul