Send a mail automatically through Outlook from Excel 2

[murad5]

I am trying to automate a report so that a it is sent to a mail list automatically every morning. Here is the relevant section of code:
Set OlkApp = CreateObject("Outlook.Application&quot
Set NewMail = OlkApp.CreateItem(olMailItem)
Set Attachments = NewMail.Attachments
message = "some text"
mail_list = Range("Mail_List&quot.Text
With NewMail
.To = mail_list
.Body = message
.Importance = 1
.Attachments.Add strPath, 1, 2
.Subject = strFileName
.Send
End With

This works fine, except that when it gets to ".send" I get a dialog box from Outlook, telling me that an application is trying to send mail on my behalf and asking me if this is alright. Is there any way to turn off this feature, or possibly some code which will allow me to tell it it's ok without my manual intervention?

Thanks...

 

[Tiglet]

Hi,

Try Application.DisplayAlerts = False but make sure you put Application.DisplayAlerts = True at the end of the macro

Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[VBAjedi]

Tiglet,

Unfortunately, it's probably not that easy. For most versions of Outlook after Outlook97, it is not possible to turn that warning off via an Outlook setting, nor can it be bypassed simply by using VBA. It's an anti-virus feature, so it would be a bit pointless if it could be easily bypassed via code.

Murad,

There is a little app floating around the net that can be installed to allow that PC to bypass the warning, but you have to install it on every computer you want to bypass the warning on. You should be able to find it via Google.

VBAjedi

 

[IvanMoala]

If you do use Outlook2K then you will be
warned of out going mail. The work around for this is
to get "Outlook Redemption" it works around limitations imposed by the
Outlook Security Patch and Service Pack 2 of MS Office 2000
(which includes Security Patch) plus provides a number of functions
to work with properties and functionality not exposed through the
Outlook object model.
For more information see

http://www.dimastr.com/redemption

Set SafeItem = CreateObject("Redemption.SafeMailItem&quot
would be the command to use to send without warning.


Also have a look @ this thread

http://216.92.17.166/board2/viewtopic.php?t=13446&highlight=redemption

 

[murad5]

Thanks for your help, guys. Unfortunately I have to go through my IT department to get it downloaded, so I don't know if it works yet, but I'll keep my fingers crossed! A star for both of you...

 

[SQLBI]

Ivan,

Will this then totally disable the pain in the @ss prompts from outlook each time a mail is sent from Excel/Access?

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[murad5]

Hi guys, just having a few teething troubles with this one (as you might have guessed, my VB is a bit shaky once you get outside Excel!) I have installed Redemption and amended my code like so:

Set OlkApp = CreateObject("outlook.application&quot
Set NewMail = OlkApp.CreateObject("redemption.safemailitem&quot
Set Attachments = NewMail.Attachments
CCList = "me@server.com"
message = "some text"
mail_list = Range("Mail_List&quot.Text
With NewMail
.To = mail_list
.CC = CCList
.Body = message
.Importance = 1
.Attachments.Add strPath, 1, 2
.Subject = strFileName
.Send
End With

I'm sure there's something stupid I'm doing wrong, any ideas?

 

[VBAjedi]

I haven't used Redemption before, but here's a snippet from their web site (

http://www.dimastr.com/redemption/):

dim SafeItem, oItem
set SafeItem = CreateObject ("Redemption.SafeMailItem&quot 'Create an instance of Redemption.SafeMailItem
set oItem = Application.CreateItem(0) 'Create a new message
SafeItem.Item = oItem 'set Item property
SafeItem.Recipients.Add "somebody@somewhere.com"
SafeItem.Recipients.ResolveAll
SafeItem.Subject = "Testing Redemption"
SafeItem.Send

I'd try to mimic their Set statements a bit more closely. . .

VBAjedi

 

[ladybyrd]

Try this code:

'automatically answers "yes" to the dialog security box in Outlook
Set fso = CreateObject("Scripting.FileSystemObject&quot
Set fsofile = fso.CreateTextFile("ByPass.vbs&quot
fsofile.writeline "set fso = createobject(""wscript.shell"&quot"
fsofile.writeline "while fso.appactivate(""microsoft outlook"&quot =false"
fsofile.writeline "wscript.sleep 1000"
fsofile.writeline "wend"
fsofile.writeline "fso.sendkeys ""a"", true"
fsofile.writeline "fso.sendkeys ""y"", true"
fsofile.writeline "wscript.sleep 7000"
fsofile.writeline "while fso.appactivate(""microsoft outlook"&quot =false"
fsofile.writeline "wscript.sleep 1000"
fsofile.writeline "wend"
fsofile.writeline "fso.sendkeys ""y"", true"
fsofile.Close

Set wshShell = CreateObject("Wscript.Shell&quot
wshShell.Run ("ByPass.vbs&quot

Put it before your Outlook code.

 

[murad5]

Thanks for your help, but when I tried this code I get "Run Time Error '438': Object doesn't support this property or method"

This occurs on the line
set oItem = Application.CreateItem(0)

I'm presuming this is because this is from within Excel - this seems to be asking Excel (the Application) to create an item. What I can't understand is whether to ask Outlook to create a Redemption item, or to ask Redemption to create an Outlook item (not very clear, I know, but I don't understand it too clearly myself!)

 

[ladybyrd]

Sorry, I didn't read carefully enough. The code I posted is for use without Redemption.

 

[murad5]

It's OK Ladybyrd, turns out it was me who read too quickly. Your post hadn't come up before my last reply.

In any case, I tried your code, placing it before the section which deals with Outlook, but not right at the start of the module. Unfortunately, it doesn't seem to have had any effect. Can you think of anything obvious I should be doing that I might be leaving out?

I think I prefer your solution, as it means that the code can work on any machine, regardless of whether or not it has redemption installed.

 

[murad5]

Finally figured it out, although using Redemption rather than the scripting example above! Using this code
Dim SafeItem, olApp, NewMail, attachments
Dim maillist As String, message As String
Const olMailItem = 0
Set SafeItem = CreateObject("Redemption.Safemailitem&quot
Set olApp = CreateObject("Outlook.Application&quot
Set NewMail = olApp.CreateItem(olMailItem)
SafeItem.Item = NewMail
maillist = "me@server.com"
message = "some text"
With SafeItem
.to = maillist
.Body = message
.Importance = 1
.Send
End With

Seems to work without a hitch. Thanks for your help everyone.

Ladybyrd, I'm still interested in your approach above, any ideas how I could make it work?

Thanks,

Richard

 

[ladybyrd]

It's possible that it was running too slowly. The dialog box asking if you want to send mail will still appear, but you shouldn't need to respond to it. The sendkeys statements should take care of it. You might want to try shortening the sleep time.

If it still doesn't work, I'm afraid I'm in the dark. I use this code, but I didn't write it and I don't know a whole lot about vbscript.

 

[murad5]

Fair enough, I'll give it a go. Thanks again for your help