|
|
|
|
|
|
|
|
|
|
|
|
AutoSoft Systems |
| |
|
|
|
AutoSoft
Systems |
2 Round Hill
Court |
| |
|
|
|
East Greenwich,
RI 02818 |
| |
|
|
|
401.885.3631 |
| |
|
|
|
401.884.5653 Fax |
| |
|
|
|
|
|
|
|
|
|
|
|
401.996.3631 Cell |
| AMDG |
|
|
|
Decision Ready
Information! |
autosoft@aol.com |
| Commercial & custom multi-user computer software for a
variety of applications including performance metrics, statistical analysis,
data extraction and merger from multiple large databases, computer simulation
and management information systems.
Founded in 1982. |
| We know Excel! Whenever possible, Excel
is the user interface, VBA is the programming language, ODBC connects to the
database of your choice. |
| Home |
About Us |
Literature |
Wire
& Cable |
Pharmaceutical
& Biotech |
Construction
Management |
Commercial
Products |
Custom Solutions |
| Contact Us |
Events |
Downloads |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
AMDG |
![MCj04415190000[1]](email_files/index_0000_image004.png)
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Several of the SMART tools programmatically send emails and text
messages for either periodic or exception reporting. This screen documents the several ways that
it is accomplished. |
| Both emails
and text messaging can be treated in identical fashion. For example, my email
is |
autosoft@aol.com |
|
| You can send a
text message to my phone using this address: |
4019963631@vtext.com |
(Verizon). |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| The domain for all major US carriers: |
Carrier |
Send
Email to phonenumber@.... |
|
|
|
|
|
|
|
|
|
|
Alltel |
@message.alltel.com |
|
|
|
|
|
|
|
|
|
|
AT&T |
@mms.att.net |
|
|
|
|
|
|
|
|
|
|
Nextel |
@messaging.nextel.com |
|
|
|
|
|
|
|
|
|
|
Sprint |
@messaging.sprintpcs.com |
|
|
|
|
|
|
|
|
|
|
SunCom |
@tms.suncom.com |
|
|
|
|
|
|
|
|
|
|
T-mobile |
@tmomail.net |
|
|
|
|
|
|
|
|
|
|
VoiceStream |
@voicestream.net |
|
|
|
|
|
|
|
|
|
|
Verizon |
@vtext.com (text only) |
|
|
|
|
|
|
|
|
|
|
Verizon |
@vzwpix.com (pictures and videos) |
|
|
|
|
|
|
| There are several ways to send an email in Excel |
| Usign Gmail |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| First, you need to have a GMail account. For this example, let's use
mygmail999@gmail.com |
|
|
|
|
|
|
|
|
|
| I always get stuck on having the correct References checked in
the VBA module. This is the screen
shot of the enabled references in a file that uses this routine. The "Microsoft CDO for Windows 2000
Library" is required. |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| This code works for me. The best code is the code you do not
have to write yourself. Included are
the web sites where I found this. |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Sub SendEmailUsingGmail(cfile As String) |
| 'http://www.learnexcelmacro.com/2011/12/how-to-send-an-email-using-excel-macro-from-gmail-or-yahoo/ |
| 'http://www.ozgrid.com/forum/showthread.php?t=82442 |
| Dim NewMail As
CDO.Message |
| Set NewMail = New
CDO.Message |
| 'Enable SSL
Authentication |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl")
= True |
| 'Make SMTP
authentication Enabled=true (1) |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1 |
| 'Set the SMTP server
and port Details |
| 'To get these details
you can get on Settings Page of your Gmail Account |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")
= "smtp.gmail.com" |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 465 |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")
= 2 |
| 'Set your credentials
of your Gmail Account |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
= "account@gmail.com" |
|
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
= "password" |
| 'Update the
configuration fields |
|
NewMail.Configuration.Fields.Update |
| 'Set All Email
Properties |
| With NewMail |
| .Subject =
"There are " + CStr(n) + " alarms" |
| .From =
"account@gmail.com" |
| .To =
"autosoft@aol.com;jserdakowski@cmcorp.com" 'sends email to email address |
| .CC =
"4019963631@vtext.com;4019963632@vtext.com" 'sends text message to cell phones |
| .BCC = "" |
| .TextBody =
"" |
| .AddAttachment
(cfile) |
| End With |
| NewMail.Send |
| ' MsgBox ("Mail
has been Sent") |
| 'Set the NewMail
Variable to Nothing |
| Set NewMail = Nothing |
| End Sub |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Note: One has to modify the security setting on the Gmail
account to allow "Access for less secure apps" |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Using CDO with Office 365 |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| I ran into issues recently with configuring a Windows 2012 R2
Server to send out emails. The below
"SendMail" routine refused to work.
I was able to quickly modify the above CDO approach to work with
Office 365. Again, you have to make
sure the proper references are checked. |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Sub SendEmail(cSubject As String, cTo As String, cCC As String,
cTextBody As String, cAttachment As String) |
| Dim myEmail As
CDO.Message |
| Set myEmail =
CreateObject("CDO.Message") |
| myEmail.Subject =
cSubject |
| myEmail.From =
"jserdako@amgen.com" |
| myEmail.To = cTo |
| myEmail.CC = cCC |
| myEmail.TextBody =
cTextBody ' if you do not have
something in the TextBody and if you have an attachment, the attachment will
corrupt |
| myEmail.AddAttachment
cAttachment |
| Application.Wait (Now
+ TimeValue("00:00:05")) |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")
= 2 |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")
= "smtp.office365.com" |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25 'SMTP Port |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= cdoBasic |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl")
= True |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
= <your email including the @> |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
= <your password> |
| 'Connection Timeout in
seconds (the maximum time CDO will try to establish a connection to the SMTP
server) |
|
myEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 60 |
|
myEmail.Configuration.Fields.Update |
| myEmail.Send |
| Application.Wait (Now
+ TimeValue("00:00:05")) |
| Set myEmail = Nothing |
| End Sub |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| You may want to hide the password in some way, shape or
form. The parameter cdoBasic evaluates
to a value of 1. Note that the smtp
port value is changed from the gmail example. |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| SendMail Method |
| By far the simplest way to do so is by using the SendMail
method. This sends an email with the active document as an attachment. Being
a lazy programmer, I often create a blank document just to send an email this
easy way. |
| |
This example
sends the active workbook to a single recipient. |
|
ActiveWorkbook.SendMail
recipients:="autosoft@aol.com" |
| FollowHyperlink Method |
| Simply build a URL |
|
|
|
|
|
|
|
|
|
|
|
|
curl =
"mailto:" & cEmail & "?subject=" & cSubj
& "&body=" & cBody |
|
curl =
Left(curl, 2025) 'was successful with 2025
, not with 2045 |
|
'-- Execute the URL (start the email client) |
|
ActiveWorkbook.FollowHyperlink
(curl) |
|
| BLAT |
| www.blat.net |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Blat is a Win32 command line SMTP mailer. |
| Sample code that uses Blat: |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Sub
Blat(vRECIPIENTS As String, vSUBJECT As String, vMSG1 As String) |
| vMAILSERVER = "172.16.1.219" '
DEFINE MAIL SERVER (Exch2007 Server IP Address |
| vSENDEREMAIL =
"SAFEmail@CMCorporation" '
Default Sender for ECR. |
| vMSGFILE = ThisWorkbook.Path +
"\Blat\MSG.TXT" 'CREATE vMSGFILE FROM VARIABLES |
| vRECIPIENTS =
Application.WorksheetFunction.Substitute(vRECIPIENTS, ";",
",") |
| vRECIPIENTS = vRECIPIENTS + ","
+ ThisWorkbook.Sheets("Detail").[CurrentEmail].Value +
"@cmcorporation.com" |
| Set fs =
CreateObject("Scripting.FileSystemObject") |
| Set a = fs.CreateTextFile(vMSGFILE, True) |
| a.WriteLine (vMSG1 + Chr(13) + Chr(10)) |
| a.Close |
| Set a =
fs.CreateTextFile(ThisWorkbook.Path + "\blat\blatx.bat", True) |
| a.WriteLine (ThisWorkbook.Path +
"\blat\blat.exe """ + vMSGFILE + _ |
| """ -t
""" + vRECIPIENTS + _ |
| """ -s
""" + vSUBJECT + _ |
| """ -f
""" + vSENDEREMAIL + _ |
| """ -server
""" + vMAILSERVER + """" + _ |
| Chr(13) + Chr(10)) |
| a.Close |
| Dim RetVal |
| RetVal = Shell(ThisWorkbook.Path +
"\blat\blatx.bat", vbHide) |
| End Sub |
| Embedding a Worksheet in the
body of an Email |
| |
Sub ttest() |
|
|
|
|
|
|
| |
Set
oOutLookObject = CreateObject("Outlook.Application") |
|
|
|
|
|
|
| |
Set
oEmailItem = oOutLookObject.CreateItem(MailItem) |
|
|
|
|
|
|
| |
ThisWorkbook.Sheets("Summary").Cells.Copy |
|
|
|
|
|
|
| |
With
oEmailItem |
|
|
|
|
|
|
| |
.Recipients.Add ("autosoft@aol.com") |
|
|
|
|
|
|
| |
.Subject = "Automation sample" |
|
|
|
|
|
|
| |
.Importance = IMPORTANCENORMAL |
|
|
|
|
|
|
| |
.Body = "This is easy!" |
|
|
|
|
|
|
| |
.display |
|
|
|
|
|
|
| |
.GetInspector.CommandBars.ExecuteMso ("Paste") |
|
|
|
|
|
|
| |
.Send |
|
|
|
|
|
|
| |
End
With |
|
|
|
|
|
|
| |
Set
oEmailItem = Nothing |
|
|
|
|
|
|
| |
Set
oOutLookObject = Nothing |
|
|
|
|
|
|
| |
Application.CutCopyMode = False |
|
|
|
|
|
|
| |
End Sub |
|
|
|
|
|
|
| Click Yes |
| http://www.contextmagic.com/express-clickyes/ |
|
|
|
|
|
|
|
|
|
| Outlook blocks efforts to automatically send emails. This free utility allows the automatic
emails to proceed by automatically clicking "Yes" when Outlook
presents this screen. |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| AutoSoft Systems | 2 Round Hill Court, East
Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 |
Mobile: 401.996.3631 |
| This web page was last updated at 03/02/2017 11:43 AM and is
written in EXCEL! |
| AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach
- AutoSoft Systems is not affiliated with Consona nor Zumbach |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|