Send an email from Excel without notifying the user

[brettdj]

Thread split from http://www.vbaexpress.com/forum/showthread.php?t=6587

Thanks guys, nice coding Killian and I like your sneekiness John.
The email code (courtesy Ron de Bruin) is an absolute riot, as (1) the user doesn't see the email leave his/her system - as far as I am aware, (2) you can send the email from anyone

I was wondering though if there was a better way to detect an unprotected sheet than running a Workbook event



Sub Mail_Small_Text_CDO()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
' Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
On Error Resume Next
strbody = "Some knob " & Environ("usernAme") & "has unprotected a worksheet"
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") = "My server"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With iMsg
Set .Configuration = iConf
.To = "David.Brett@XXX.com"
.CC = "Rod.Mainland@XXX.com"
.BCC = ""
.From = "SmurfLover@PVR.com"
.Subject = "Some knob has tried cracking the protection"
.TextBody = strbody
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub

[CBrine]

Dave,
I was lurking around here trying to get some code from Zack, and I noticed this post. I like the idea of being able to send and email without the secuirty issues. I currently use a utility called blat.exe to bypass, but it's a seperate DOS executable. Being able to send a webmail without using a seperate program would be better!!
I tried this and it didn't work for me. Do you need to know the full http address in order to get this to work? I've tried it but I ran into problems, with the .send.

Thanks
Cal

[Ken Puls]

Hi Cal,

Try checking out Ron DeBruin's site: http://www.rondebruin.nl/cdo.htm He's got some pretty good documentation on how it all works, although it's been some time since I read it all myself. :)

[CBrine]

Ken,
Thanks for the link, just what I was looking for!

Cal

[Ken Puls]

Not a problem. FYI, I have used it without having an Outlook Express account set up. Works like a charm. You just need to pick an SMTP server that will relay, as I recall. :thumb

[CBrine]

Ken, Dave,
Got it working. WOW, I do like the fact that you can set the From to anything:-). Jokes on Co-Workers come to mind!
It's also going to be very helpful for some of my automation.

[Ken Puls]

WOW, I do like the fact that you can set the From to anything:-).

Uh, oh. Dave, it looks like we may have created a monster!

Cal... use your powers for Good! GOOD!

:rotlaugh:

[brettdj]

Its a beauty :)

There are a couple of ways to bypass Outlook security but this is even better as it avoids Outlook altogether

Another nasty trick is turning off Sheet Calculation for a single sheet depending on user logon name. This is a surprisingly poorly known property - it can be accessed directly from the sheet properties in the VBA without resorting to code

[brettdj]

After some tweaking I've managed to automate this for Outlook 2003 whereby the code estblishes if OL 2003 is running on the users machine, and if so it retrieves the Outlook 2003 SMTP server from the registry

If anyone wants a copy please PM me, I'm hesitant about dumping the code
here

Cheers

Dave