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


--
PaulC
------------------------------------------------------------------------
PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
View this thread: http://www.excelforum.com/showthread...hreadid=380386

Sending email from Excel vba problem
 

Hi Paul

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


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


"PaulC" wrote in message
...

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

Sending email from Excel vba problem
 

Ron

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

Pau

--
Paul
-----------------------------------------------------------------------
PaulC's Profile: http://www.excelforum.com/member.php...nfo&userid=756
View this thread: http://www.excelforum.com/showthread.php?threadid=38038

Sending email from Excel vba problem
 

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 "
..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

Sending email from Excel vba problem
 

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" wrote in message
...

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 "
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

Sending email from Excel vba problem
 

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

Paul


--
PaulC
------------------------------------------------------------------------
PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
View this thread: http://www.excelforum.com/showthread...hreadid=380386

Sending email from Excel vba problem
 

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


--
PaulC
------------------------------------------------------------------------
PaulC's Profile: http://www.excelforum.com/member.php...fo&userid=7563
View this thread: http://www.excelforum.com/showthread...hreadid=380386