Vba to send Email Discretely

wilster98

New Member
Joined
May 11, 2016
Messages
11
Hello VBA Gurus.
I have a form that automates portions of an AS9102 report. The report is on a secure server, but people like to save a copy to a network folder. I have to go on a search and destroy mission every so often. What I want to do is add to one of the macros when I find these bootleg copies that will discretely send an Email that a bootleg copy is being used. Can this be done so that the operator is none the wiser?

Thanks In advance, Willy
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,346
VBA Code:
Sub Mail_workbook_Outlook()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = "YourEmailAddressHere"
.CC = ""
.BCC = ""
.Subject = "Your Subject"
.Body = "Bootleg copy being used by " & Application.UserName
'.Display    'or you can use .Send to autosend the email without first reviewing
.Send
End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
Last edited:

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
154
'its worth noting that outlook's default settings will alert the user that another app is trying to send an email.
 

wilster98

New Member
Joined
May 11, 2016
Messages
11
Thank You, that is brilliant. It sends the email without alerting the user.


VBA Code:
Sub Mail_workbook_Outlook()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = "YourEmailAddressHere"
.CC = ""
.BCC = ""
.Subject = "Your Subject"
.Body = "Bootleg copy being used by " & Application.UserName
'.Display    'or you can use .Send to autosend the email without first reviewing
.Send
End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,666
Office Version
365
Platform
Windows
What if they disable VBA code? Then that won't run.
Also, how does it distinguish where it is being run from in the code (secure server vs. network folder)?
 

wilster98

New Member
Joined
May 11, 2016
Messages
11
What if they disable VBA code? Then that won't run.
Also, how does it distinguish where it is being run from in the code (secure server vs. network folder)?
Great question. The macros are password protected, and I am one of 4 people with the password.
The macro does not distinguish where the form is stored. When I find a bootleg copy I add the code to to the form so that I know when it is opened.
The code runs when the workbook is open.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,591
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top