4 Replies Latest reply: Jan 7, 2013 8:07 AM by Jose Tos RSS

    Sending Email via VBScript and batch script

    Adam Abwat-Johnson

      Hi,

       

      I'm trying to figure out if it is possible to do a reload of a qvw file from a batch script and then, using a module on the report, output a straight table to a file and send it via email.

       

      I've managed to create the batch script to reload the report and export the straight table no problem. The issue is the emailing part and trying to get it to work in combination with the batch script.

       

      For some reason, if I reload the report with it open (just from the reload button) it works and the module sends the email. If I close the report and run the batch script then the report reloads (I see the progress pop-up box) but I don't get the email and the command prompt stays open. Below is the VBScript I'm using (by the way, I've been chopping it from various different web sites and posts here so may be a mess!):

       

      sub testy()


      dim theDate

      dim filename


      if len(CStr(Month(Date()))) = 1 then

          theDate = CStr(Day(Date())) & "0" & CStr(Month(Date())) & CStr(Year(Date()))

      else

          theDate = CStr(Day(Date())) & CStr(Month(Date())) & CStr(Year(Date()))

      end if


      filename = "C:\Users\chris_johnson\Documents\Test " &theDate& ".xls"


      call ExportToExcel(filename)


      end sub


      sub ExportToExcel (filename)


      dim o

      'dim theDate

      'dim filename


      Set o = ActiveDocument.GetSheetObject("LA01")

      'theDate = CStr(Date())

      'filename = "C:\Users\chris_johnson\Documents\Test " &theDate& ".xls"


      o.ExportBiff filename


      set o = Nothing


      call SendEmail(filename)


      end sub


      sub SendEmail(attachment)


      varTo = "myemail@company.com"

      varSubject = "Report"

      varTextBody = "Please find report attached"

      varAttachment = attachment


      Dim ObjSendMail

      Set ObjSendMail = CreateObject("CDO.Message")


      'This section provides the configuration information for the remote SMTP server.


      ObjSendMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).

      ObjSendMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtpserv"

      ObjSendMail.Configuration.Fields.Update


      ObjSendMail.To = varTo

      ObjSendMail.Subject = varSubject

      ObjSendMail.From = "myemail@company.com"


      ' we are sending a text email.. simply switch the comments around to send an html email instead

      'ObjSendMail.HTMLBody = "this is the body"

      ObjSendMail.TextBody = varTextBody

      ObjSendMail.AddAttachment varAttachment


      ObjSendMail.Send


      Set ObjSendMail = Nothing


      end sub


      Can someone help me with trying to understand if (1) this is the right way to go about this and (2) how to change this script in order to get it to work with emailing from batch script.


      I've had half a thought that maybe I should remove the emailing part from the VBScript and possibly put it into the batch script.


      Thanks,


      Chris