Send Status in gmail by VBScript

Do you forget sending the status every day after script run?

Why to worry, ask VBScript to do it. Update the status excel file by manually or by QTP, double click on the status.vbs file (copy below script and save as status.vbs) or if your lazy to double click, just create Windows Task Scheduler and set the time.

This script will send status by reading the data from excel and attaché in body of mail as text.

********************************************************************

Function GetData()
Dim x, strTemp, objExcel, objWB 

Set objExcel = Wscript.CreateObject("Excel.Application") 
Set objWB = objExcel.Workbooks.Open("D:\Test.xlsx") 
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 

' Make Excel visible while debugging 
objExcel.Visible = True 

' This is the row of our first cell. 
x = 2 

do while objExcel.Cells(x, 1).Value <> "" 
  strTemp =strTemp & objExcel.Cells(x, 1).Value &_
  Space(25)
  strTemp = strTemp & objExcel.Cells(x, 2).Value  &_
  Space(30)
  strTemp = strTemp & objExcel.Cells(x, 3).Value  & vbCRLF
  x = x + 1 
loop 

' This will prevent Excel from prompting us to save the workbook. 
objExcel.ActiveWorkbook.Saved = True 

' Close the workbook and exit the application. 
objWB.Close 
objExcel.Quit 

set objWB = Nothing 
set objExcel = Nothing 

GetData = strTemp 
End Function
'*******************************************
sendmail()
Sub sendmail()
  Set iMsg = CreateObject("CDO.Message")
  Set iConf = CreateObject("CDO.Configuration")

  iConf.Load -1 ' CDO Source Defaults
  Set Flds = iConf.Fields

  With Flds
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
   'Enable SSL Authentication
 
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
   'Value of 1 enables basic authnetication, 
   '2 enables NTLM Authentication, 
   '0 disables Authentication

    'Enter your gmail address from which you would like to send mail.
   .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "test@gmail.com"
   'Enter Password
   .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =  ""
   'Enter smtpserver, if it is gmail enter as smtp.gmail.com and if it is hotmail smtp.live.com
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

   .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
   'Value of 2 means send using port
   'value of 1 means send using a local SMTP server
   'value of 3 means send using Exchange Server

    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   'The SMTP Port which must be enabled in your network by ISP or local Firewall

    .Update
  End With

  With iMsg
   Set .Configuration = iConf
   .To = "test1@gmail.com"
   .CC = ""
   .BCC = ""
   .From = "test@gmail.com"
   .Subject = "Status"
   strBody = "Sl.No:" & Space(15) & "Test Case Name" & Space(25) & "Status" & vbCRLF 
    ' Here we call the function GetData to populate the body text. 
    strBody = strBody &  GetData

   .textBody = strBody
   '.AddAttachment "D:\Test1.txt"
   'Local path of the file to attached
   'For attaching another file, 
   'repeat the line with new path
   .Send
   MsgBox "Sent Mail"
  End With
End Sub

*************************************************************************** 

Send gmail and hotmail by VBscript

We have seen many scripts of sending mail using OUTLOOK by VB script. But what if the system does not have OUTLOOK installed? I thought of sending mail using gmail, but I don’t want to launch browser,login to gmail and send mail from it. Having this in mind I started googling and found nice script to send mail from gmail without much delay.  You can send such mails from hot mail as well. Interesting part is we can send mail with attachment as well. Thanks to google and thanks to learnqtp.info.

Here is the script, you can directly copy to notepad,save as .vbs and double click. Please don’t forget to enter your gmail user name and password. Enjoy QTP and VBScripting

**********************************************************************************

sendmail()
Sub sendmail()
  Set iMsg = CreateObject("CDO.Message")
  Set iConf = CreateObject("CDO.Configuration")

  iConf.Load -1 ' CDO Source Defaults
  Set Flds = iConf.Fields

  With Flds
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
   'Enable SSL Authentication
 
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
   'Value of 1 enables basic authnetication, 
   '2 enables NTLM Authentication, 
   '0 disables Authentication

    'Enter your gmail address from which you would like to send mail.
   .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "test@gmail.com"
   'Enter Password
   .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =  ""
   'Enter smtpserver, if it is gmail enter as smtp.gmail.com and if it is hotmail smtp.live.com
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

   .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
   'Value of 2 means send using port
   'value of 1 means send using a local SMTP server
   'value of 3 means send using Exchange Server

    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   'The SMTP Port which must be enabled in your network by ISP or local Firewall

    .Update
  End With

  With iMsg
   Set .Configuration = iConf
   .To = "test1@gmail.com"
   .CC = ""
   .BCC = ""
   .From = "test@gmail.com"
   .Subject = "A mail from VBscript"
   .TextBody = "This is a test email sent using Gmail's SMTP Server with port 25."
   .AddAttachment "D:\Test1.txt"
   'Local path of the file to attached
   'For attaching another file, 
   'repeat the line with new path
   .Send
   MsgBox "Sent"
  End With
End Sub

**********************************************************************************