Vba to send Email Discretely

wilster98

New Member
Joined
May 11, 2016
Messages
20
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,079
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
155
'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
20
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
57,664
Office Version
  1. 365
Platform
  1. 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
20
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,133,428
Messages
5,658,737
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top