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

Ron

I have just got back to this problem, and have been trying your suggestion of using CDO.

I am working on a LAN and want everyone to be able to use the resulting code to be able to send attachements from the Excel file back to one of the users. We use Outlook and don't have Outlook Express set up for users.

I started with the following code:

Sub CDO_Send_Workbook()
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant


Application.ScreenUpdating = False


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


iConf.Load -1 ' CDO Source Defaults

Set Flds = iConf.Fields
With Flds

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "our proxy server"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "domain\username"

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

.Update

End With


With iMsg
Set .Configuration = iConf
.To = "admin user email"
.From = """paul"" <my email"
.Subject = "Your Weekly Performance Files"
.TextBody = "Weekly Files"
.AddAttachment "U:\Temp\Time.xls"
.Send
End With


Set iMsg = Nothing
Set iConf = Nothing
Set wb = Nothing
Application.ScreenUpdating = True

End Sub


I put in the full domain name, and my usersname, but not sure about the password (used my usual logon password).

It returns a run time error: "The message could not be sent to the SMTP server. The transport error code was 0x800cc15. The server response was not available."

I realise I am a bit out of my depth, but if there is a something obvious I am doing wrong...

Thanks in anticipation,

Paul