AutoSoft Systems
        AutoSoft Systems 2 Round Hill Court
        East Greenwich, RI 02818
        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
Programmatically Sending
Emails and Text Messages
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)
    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
    '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
'    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
    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 is a Win32 command line SMTP mailer.
Sample code that uses Blat:
Sub Blat(vRECIPIENTS As String, vSUBJECT As String, vMSG1 As String)
    vMAILSERVER = "" ' DEFINE MAIL SERVER (Exch2007 Server IP Address
    vSENDEREMAIL = "SAFEmail@CMCorporation"  ' Default Sender for ECR.
    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))
    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))
    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)            
      With oEmailItem            
         .Recipients.Add ("autosoft@aol.com")            
         .Subject = "Automation sample"            
         .Importance = IMPORTANCENORMAL            
         .Body = "This is easy!"            
         .GetInspector.CommandBars.ExecuteMso ("Paste")            
      End With            
      Set oEmailItem = Nothing            
      Set oOutLookObject = Nothing            
      Application.CutCopyMode = False            
  End Sub            
Click Yes
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