Jump to content

My 1st CopyExcel-then-Email excel data

meatsack

By meatsack,
in AutoIt Example Scripts

 Share

Recommended Posts

meatsack Wayfarer

meatsack

Posted
Posted (edited)

Here it is below. Thank you to all who responded to my forum question posts: PsaltyDS

enaiman

Beege

Water

Jos

Melba23

Corgano

Steveiwonder

Brettf

AdmiralAlkex

crashdemons

JohnOne

Richard Robertson

UEZ

famaes

My 1st substantial script. It successfully Copies a few Excel data cells -then- Emails the excel data to my cell phone sms. It will soon do it daily for only $10.00 on t-mobiles sms unlimited service.

Its nearly finished I mostly just cut & paste from other example scripts, and lots of help from the forum.

;##################################
; Include
;##################################
#Include<file.au3>
#include <Excel.au3>
#include <Array.au3>
#include <String.au3>


;##################################
; Variables
;##################################
Dim $aArray2d
Dim $Body
Dim $zString

$sFilePath1 = @ScriptDir & "\test.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)
$aArray2d = _ExcelReadSheetToArray($oExcel, 2, 2, 3, 3)
Local $aArray1D = _Array2DTo1D($aArray2d)

_ArrayDisplay($aArray1D)


Func _Array2DTo1D($aTwo_d)
    If UBound($aTwo_d, 0) <> 2 Then
        MsgBox(0, "Error", "Array not 2 dimensional")
        Return
    EndIf
    Local $iRow = UBound($aTwo_d)
    Local $iCol = UBound($aTwo_d, 2)
    Local $aOne_d[$iRow * $iCol]

    For $i = 0 To $iRow - 1
        For $j = 0 To $iCol - 1
            $aOne_d[$i * $iCol + $j] = $aTwo_d[$i][$j]
        Next
    Next
    Return $aOne_d
EndFunc   ;==>_Array2DTo1D
;




$zString = _ArrayToString($aArray1D)

$SmtpServer = "smtp.gmail.com"              ; address for the smtp-server to use - REQUIRED
$FromName = "john@gmail.com"                      ; name from who the email was sent
$FromAddress = "john@gmail.com" ; address from where the mail should come
$ToAddress = "doe@gmail.com"   ; destination address of the email - REQUIRED
$Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
$Body =    $zString               ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file you want to attach- leave blank if not needed
$CcAddress = ""       ; address for cc - leave blank if not needed
$BccAddress = ""     ; address for bcc - leave blank if not needed
$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
$Username = "john"                    ; username for the account used from where the mail gets sent - REQUIRED
$Password = "password"                  ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 465                            ; port used for sending the mail
$ssl = 1                                ; enables/disables secure socket layer sending - put to 1 if using httpS

;~ $IPPort=465                          ; GMAIL port used for sending the mail
;~ $ssl=1                               ; GMAILenables/disables secure socket layer sending - put to 1 if using httpS

;##################################
; Script
;##################################
Global $oMyRet[2]
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)
If @error Then
    MsgBox(0, "Error sending message", "Error code:" & @error & "  Description:" & $rc)
EndIf
;
; The UDF
_ArrayDisplay($Body)
Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_AttachFiles = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0)
    Local $objEmail = ObjCreate("CDO.Message")
    $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>'
    $objEmail.To = $s_ToAddress
    Local $i_Error = 0
    Local $i_Error_desciption = ""
    If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress
    If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress
    $objEmail.Subject = $s_Subject
    If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then
        $objEmail.HTMLBody = $as_Body
    Else
        $objEmail.Textbody = $as_Body & @CRLF
    EndIf
    If $s_AttachFiles <> "" Then
        Local $S_Files2Attach = StringSplit($s_AttachFiles, ";")
        For $x = 1 To $S_Files2Attach[0]
            $S_Files2Attach[$x] = _PathFull($S_Files2Attach[$x])
            ConsoleWrite('@@ Debug(62) : $S_Files2Attach = ' & $S_Files2Attach & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
            If FileExists($S_Files2Attach[$x]) Then
                $objEmail.AddAttachment ($S_Files2Attach[$x])
            Else
                ConsoleWrite('!> File not found to attach: ' & $S_Files2Attach[$x] & @LF)
                SetError(1)
                Return 0
            EndIf
        Next
    EndIf
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
    If Number($IPPort) = 0 then $IPPort = 25
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort
    ;Authenticated SMTP
    If $s_Username <> "" Then
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
    EndIf
    If $ssl Then
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    EndIf
    ;Update settings
    $objEmail.Configuration.Fields.Update
    ; Set email Importance
    Switch $s_Importance
        Case "High"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High"
        Case "Normal"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal"
        Case "Low"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low"
    EndSwitch
    $objEmail.Fields.Update
    ; Sent the Message
    $objEmail.Send
    If @error Then
        SetError(2)
        Return $oMyRet[1]
    EndIf
    $objEmail=""
EndFunc   ;==>_INetSmtpMailCom
;
;
; Com Error Handler
Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    $oMyRet[0] = $HexNumber
    $oMyRet[1] = StringStripWS($oMyError.description, 3)
    ConsoleWrite("### COM Error !  Number: " & $HexNumber & "   ScriptLine: " & $oMyError.scriptline & "   Description:" & $oMyRet[1] & @LF)
    SetError(1); something to check for when this function returns
    Return
EndFunc   ;==>MyErrFunc

_ExcelBookClose($oExcel, 1, 0)
Edited by meatsack
Link to comment
Share on other sites
meatsack Wayfarer

meatsack

Posted
  • Author
Posted

UPDATE: Had to make a final modification.

Had to change this line

$zString = _ArrayToString($aArray1D, @TAB, 1, 20)

To this to make it work.

$zString = _ArrayToString($aArray1D)

Whoops it was working, but now its not. I'll work on it in the morning and get it working again. Need to tap the array and jostle the variables a little more to get it to sputter back to life.

Link to comment
Share on other sites
  • 2 weeks later...
meatsack Wayfarer

meatsack

Posted
  • Author
Posted

Paid $30.00 for this modification:

#Include <File.au3> #include <Excel.au3> #include <Array.au3> #include <String.au3> #include <IE.au3>  HotKeySet("^.", "quit")  Local $s_FromName = 'Test Account'              ; From Name Local $s_FromAddress = 'test@test.com'          ; From @mail Local $s_ToAddress = 'test@test.com'           ; To @mail Local $s_Username = 'test.account'               ; Gmail Username Local $s_Password = 'test.password'                ; Gmail Password Local $oFile = @ScriptDir & "\testz.xls"       ; Excel File Local $vTime = 10                              ; First time to start!   While 1    Sleep(5000)     If @HOUR = $vTime Then      excelUpdate()       gmailSMTP($output)      $vTime = 17     EndIf   If @HOUR = $vTime Then      excelUpdate()       gmailSMTP($output)      $vTime = 10     EndIf WEnd  Func excelUpdate()  Local $Excel = ObjCreate("Excel.Application")   $Excel.Visible = True   $Excel.WorkBooks.Open($oFile, 2, False)     $Excel.ActiveCell.QueryTable.Refresh    $Excel.ActiveWorkBook.Saved = 1     $Excel.Quit     $oExcel = _ExcelBookOpen($oFile)    $oRead = _ExcelReadSheetToArray($oExcel, 2, 1, 40)  _ExcelBookClose($oExcel)    $output = ""    For $r = 0 to UBound($oRead,1) - 1      $output = $output & @CRLF       For $c = 0 to UBound($oRead,2) - 1          $output = $output & $oRead[$r][$c] & " "        Next    Next    Global $output EndFunc  Func gmailSMTP($vBody)  ; SMTP part [ Without Web-based GUI ]   Local $s_SmtpServer = 'smtp.gmail.com'  Local $s_IPPort = 465   Local $s_Subject = 'Calender'   Local $s_Body = $vBody  Local $objEmail = ObjCreate("CDO.Message")      $objEmail.From = $s_FromAddress     $objEmail.To = $s_ToAddress     $objEmail.Subject = $s_Subject  $objEmail.TextBody = $s_Body    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer   $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $s_IPPort   $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1     $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username   $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password   $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True    $objEmail.Configuration.Fields.Update   $objEmail.Send  If @error Then      MsgBox(32, "Error", "Error while sending")      Exit    EndIf   $objEmail=""    $output="" EndFunc  Func quit()     Exit EndFunc
Link to comment
Share on other sites
  • 2 weeks later...
meatsack Wayfarer

meatsack

Posted
  • Author
Posted

here it is a working version.

#Include <File.au3>
#include <Excel.au3>
#include <Array.au3>
#include <String.au3>
#include <IE.au3>

HotKeySet("^.", "quit")

Local $s_FromName = 'mya'               ; From Name
Local $s_FromAddress = 'mya'            ; From @mail
Local $s_ToAddress = ''         ; To @mail
Local $s_Username = 'mya'               ; Gmail Username
Local $s_Password = ''              ; Gmail Password
Local $oFile = @ScriptDir & "\testz.xls"        ; Excel File
Local $vTime = 10                               ; First time to start!


While 1
    Sleep(5000)
    If @HOUR = $vTime Then
        gmailSMTP(excelUpdate())
        $vTime = 17
    EndIf
    If @HOUR = $vTime Then
        gmailSMTP(excelUpdate())
        $vTime = 10
    EndIf
WEnd

Func excelUpdate()
    Local $Excel = ObjCreate("Excel.Application")

    $Excel.Visible = True
    $Excel.WorkBooks.Open($oFile, 2, False)
    $Excel.ActiveCell.QueryTable.Refresh
    $Excel.ActiveWorkBook.Saved = 1
    $Excel.Quit

    $oExcel = _ExcelBookOpen($oFile)
    $oRead = _ExcelReadSheetToArray($oExcel, 2, 1, 40)
    _ExcelBookClose($oExcel)

    $output = ""
    For $r = 0 to UBound($oRead,1) - 1
        $output = $output & @CRLF
        For $c = 0 to UBound($oRead,2) - 1
            $output = $output & $oRead[$r][$c] & " "
        Next
    Next
    Return $output
EndFunc

Func gmailSMTP($vBody)
    ; SMTP part [ Without Web-based GUI ]

    Local $s_SmtpServer = 'smtp.gmail.com'
    Local $s_IPPort = 465
    Local $s_Subject = 'Calender'
    Local $s_Body = $vBody
    Local $objEmail = ObjCreate("CDO.Message")

    $objEmail.From = $s_FromAddress
    $objEmail.To = $s_ToAddress
    $objEmail.Subject = $s_Subject
    $objEmail.TextBody = $s_Body

    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $s_IPPort
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    $objEmail.Configuration.Fields.Update

    $objEmail.Send
    If @error Then
        MsgBox(32, "Error", "Error while sending")
        Exit
    EndIf
    $objEmail=""
    $output=""
EndFunc

Func quit()
    Exit
EndFunc
Link to comment
Share on other sites
  • 1 year later...
meatsack Wayfarer

meatsack

Posted
  • Author
Posted (edited)

You are welcome to may any suggestions on how to make sure I get up-to-date Excel data. I would like to do it in the script. I have been hobboling along relying on xp to open it with the os opening it daily then closing. Then before the closing the Excel file refreshes the data anytime the file is opened. That doesn't seem to work every time or it hangs. Thanks for any suggestions.

Edited by meatsack
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...