Function in a For loop

Infiri · August 6, 2013

I'm trying to make a script that pulls a number and an email address from an excel spreadsheet I have, creates something with the number, then emails it to them. I have it all down except the excel part. The problem I am having is my For loop is stopping when the function is called in the SMTP mailer script here: '?do=embed' frameborder='0' data-embedContent>>.

I need to include the mailer in my loop because each person needs an email. Is there a way to do this?

Here is an example of what I have, you will have to fill in your own email info.

#Include<file.au3>
#Include <Excel.au3>

$oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)   

For $i = 1 To 10
$Number = _ExcelReadCell($oExcel, $i, 1)
$Email = _ExcelReadCell($oExcel, $i, 2)


;
;##################################
; Include
;##################################
#Include<file.au3>
;##################################
; Variables
;##################################
$SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
$FromName = "Name"                      ; name from who the email was sent
$FromAddress = "your@Email.Address.com" ; address from where the mail should come
$ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
$Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
$Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
$BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
$Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
$Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25                            ; port used for sending the mail
$ssl = 0                                ; 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
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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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

next

Edano · August 6, 2013

i have no idea, but welcome i wanted to say.

Edit: and please use the autoit tags instead of plain text tags. looks better.

.

#Include<file.au3>
#Include <Excel.au3>

$oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)   

For $i = 1 To 10
$Number = _ExcelReadCell($oExcel, $i, 1)
$Email = _ExcelReadCell($oExcel, $i, 2)


;
;##################################
; Include
;##################################
#Include<file.au3>
;##################################
; Variables
;##################################
$SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
$FromName = "Name"                      ; name from who the email was sent
$FromAddress = "your@Email.Address.com" ; address from where the mail should come
$ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
$Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
$Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
$BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
$Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
$Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25                            ; port used for sending the mail
$ssl = 0                                ; 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
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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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

next

Edited August 6, 2013 by Edano

jaberwacky · August 6, 2013

You cannot embed functions in the For loop.

Edano · August 6, 2013

You cannot embed functions in the For loop.

and includes neither

Edano · August 6, 2013

you need a structure: first includes, then variables, then the gui (if there is), then the loop(s), and at last the functions outside of the loops.

Edano · August 6, 2013

and probably you will need to store the variables in a ten element array.

jaberwacky · August 6, 2013

This is how I would structure this code. It may or may not work for you.

#AutoIt3Wrapper_AU3Check_Parameters=-w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 -d

#Include <File.au3>

#Include <Excel.au3>

Global $oMyRet

Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

GLobal $rc = ''

main()

func main()
    Local Const $SmtpServer  = "MailServer"             ; address for the smtp-server to use - REQUIRED
    Local Const $FromName    = "Name"                   ; name from who the email was sent
    Local Const $FromAddress = "your@Email.Address.com" ; address from where the mail should come
    Local Const $ToAddress   = "your@Email.Address.com" ; destination address of the email - REQUIRED
    Local Const $Subject     = "Userinfo"               ; subject from the email - can be anything you want it to be
    Local Const $Body        = ""                       ; the messagebody from the mail - can be left blank but then you get a blank mail
    Local Const $AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
    Local Const $CcAddress   = "CCadress1@test.com"     ; address for cc - leave blank if not needed
    Local Const $BccAddress  = "BCCadress1@test.com"    ; address for bcc - leave blank if not needed
    Local Const $Importance  = "Normal"                 ; Send message priority: "High", "Normal", "Low"
    Local Const $Username    = "******"                 ; username for the account used from where the mail gets sent - REQUIRED
    Local Const $Password    = "********"               ; password for the account used from where the mail gets sent - REQUIRED
    Local Const $IPPort      = 25                       ; port used for sending the mail
    Local Const $ssl         = 0                        ; enables/disables secure socket layer sending - put to 1 if using httpS
;~  Local Const $IPPort=465                             ; GMAIL port used for sending the mail
;~  Local Const $ssl=1                                  ; GMAILenables/disables secure socket layer sending - put to 1 if using http

    Local $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)
    Local $Number = ''
    Local $Email = ''

    For $i = 1 To 10
        $Number = _ExcelReadCell($oExcel, $i, 1) ; You assign to this but do nothing with it
        
        $Email = _ExcelReadCell($oExcel, $i, 2) ; You assign to this but do nothing with it

        $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
    next
EndFunc

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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console

            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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
    EndIf
    
    $objEmail=""
EndFunc

; Com Error Handler
Func MyErrFunc()
    Local $HexNumber = Hex($oMyError.number, 8)
    $oMyRet = 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
EndFunc

You assign to $number and $email but do naught with them.

Edited August 7, 2013 by jaberwocky6669

Edano · August 6, 2013

just made the same as jaberwocky6669 but a different approach. i either dont know what i you do with $number and $email, but i assume these are the array variables.

.

;##################################
; Include
;##################################
#Include<file.au3>
#Include <Excel.au3>

Global $Number[10],$Email[10]

$oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)   

For $i = 1 To 10
    $Number[$i-1] = _ExcelReadCell($oExcel, $i, 1)
    $Email[$i-1] = _ExcelReadCell($oExcel, $i, 2)
Next

;##################################
; Variables
;##################################
$SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
$FromName = "Name"                      ; name from who the email was sent
$FromAddress = "your@Email.Address.com" ; address from where the mail should come
$ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
$Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
$Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
$BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
$Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
$Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25                            ; port used for sending the mail
$ssl = 0                                ; 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")

; here a loop ? for $i=1 to 10
    $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
; until here loop end ? next


;
; The UDF
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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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

Infiri · August 7, 2013

Thanks for the help so far guys, I really do appreciate it.

Alright so $number is used for something else I have in my script, that part is working fine both with and without this code. $Email would go in the to address of the smtp mailer script. It would say $ToAddress = $Email

Jaberwocky,

I tried what you said, but putting the first half of the script in its own function isn't allowing the second function (Func _INetSmtpMailCom) to see the included files. It's coming back with a Variable $rc used without being declared.

Edona,

That is what I originally tried, but ending the For statement before the smtp mailer part makes the variables $Number and $Email exclusive to the loop. It tries to send an empty email to no one.

jaberwacky · August 7, 2013

Then make $rc Global. Outside of the function, at the top of the script.

Edited August 7, 2013 by jaberwocky6669

Edano · August 7, 2013

that's what i suspected $ToAddress = $Email then you go like this:

.

;##################################
; Include
;##################################
#Include<file.au3>
#Include <Excel.au3>

Global $Number[10],$Email[10]

$oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)   

For $i = 1 To 10
    $Number[$i-1] = _ExcelReadCell($oExcel, $i, 1)
    $Email[$i-1] = _ExcelReadCell($oExcel, $i, 2)
Next

;##################################
; Variables
;##################################
$SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
$FromName = "Name"                      ; name from who the email was sent
$FromAddress = "your@Email.Address.com" ; address from where the mail should come
$ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
$Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
$Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
$BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
$Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
$Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
$Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25                            ; port used for sending the mail
$ssl = 0                                ; 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")

for $i=1 to 10;  =====================================>  put email array here
    $rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $Email[$i-1], $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)
    If @error Then
        MsgBox(0, "Error sending message", "Error code:" & @error & "  Description:" & $rc)
    EndIf
next


;
; The UDF
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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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

.

no error checking, just getting your ideas to work.

Edited August 7, 2013 by Edano

Infiri · August 7, 2013

Alright so here is where I'm at. I probably should have asked before, but I don't know how to use autoit tags, sorry.

#Include <file.au3> 
#Include <Excel.au3>

Global $oMyRet


Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")


;~ GLobal $rc = ''


main()
func main()
   
   
    Local $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)
Local $Number = ''
    Local $Email = ''


    For $i = 1 To 10
        $Number = _ExcelReadCell($oExcel, $i, 1) ; You assign to this but do nothing with it
        
        $Email = _ExcelReadCell($oExcel, $i, 2) ; You assign to this but do nothing with it


;~         $rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)
        
        If @error Then
MsgBox(0, "Error sending message", "Unable to find specified file name.")
 EndIf


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


next
EndFunc

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 : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console

            If FileExists($S_Files2Attach[$x]) Then
                ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
                $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
    EndIf
   
    $objEmail=""
EndFunc

; Com Error Handler
Func MyErrFunc()
    Local $HexNumber = Hex($oMyError.number, 8)
    $oMyRet = 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
EndFunc

I had to put $Number and $Email at the top of the function because $Email is used just below that. I removed $rc because it wasnt working, even declared at the top as a Global, but its for error checking that I'm not using right now anyways. Everything looks good in the first function, but the second function isn't running at all now. Hopefully I am just missing something small, my experience with functions is very limited.

Thanks again.

Edited August 7, 2013 by JakhammerJim

jaberwacky · August 7, 2013

You commented out the call to the 2nd function.

Change this:

;~         $rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

to this:

_INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

ALso, the list of variables to send to the second functioin needs to come before the call to the second function.

Thusly:

func main()
    Local $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)
    
    Local $Number = ''
    Local $Email = ''

    For $i = 1 To 10
        $Number = _ExcelReadCell($oExcel, $i, 1) ; You assign to this but do nothing with it

        $Email = _ExcelReadCell($oExcel, $i, 2) ; You assign to this but do nothing with it

        $SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
        $FromName = "Name"                      ; name from who the email was sent
        $FromAddress = $Email                   ; address from where the mail should come
        $ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
        $Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
        $Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
        $AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
        $CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
        $BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
        $Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
        $Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
        $Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
        $IPPort = 25                            ; port used for sending the mail
        $ssl = 0    

        _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

        If @error Then
            MsgBox(0, "Error sending message", "Unable to find specified file name.")
        EndIf                            ; enables/disables secure socket layer sending - put to 1 if using httpS
    next
EndFunc

Edited August 7, 2013 by jaberwocky6669

Infiri · August 7, 2013

Jaberwocky,

You sir, are a god among men. Not to say the other people here are just mere men, but thank you. Anyone that offered me assistance, thank you as well.

One final question:

The line _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

This is just calling the funciton right? The same as main() from earlier?

jaberwacky · August 7, 2013

Right. _INetSmtpMailCom is the name of a section of source code. This section of source code can pretend that it is its own little program isolated from the universe (with an exception). This: $SmtpServer, $FromName, $FromAddress, $ToAddress ... is a list of data that you want the function to have to do its job. That exception, btw, is these functions have access to Global variables too. But it can get dirty quickly when it comes down to debugging. Preferably, it is best to code a function in such a way as to not rely on global variables. Exceptions can be made for Const variables among other things. Also, that can be impossible with AutoIt too. But it's all good. Get back to coding.

Edano · August 7, 2013

i see no reason for the variables in the loop, so why not clean that up:

.

func main()
    Local $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls", 0)
    Local $Number = ''
    Local $Email = ''
    Local $SmtpServer = "MailServer"              ; address for the smtp-server to use - REQUIRED
    Local $FromName = "Name"                      ; name from who the email was sent
    Local $FromAddress                            ; address from where the mail should come
    Local $ToAddress = "your@Email.Address.com"   ; destination address of the email - REQUIRED
    Local $Subject = "Userinfo"                   ; subject from the email - can be anything you want it to be
    Local $Body = ""                              ; the messagebody from the mail - can be left blank but then you get a blank mail
    Local $AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
    Local $CcAddress = "CCadress1@test.com"       ; address for cc - leave blank if not needed
    Local $BccAddress = "BCCadress1@test.com"     ; address for bcc - leave blank if not needed
    Local $Importance = "Normal"                  ; Send message priority: "High", "Normal", "Low"
    Local $Username = "******"                    ; username for the account used from where the mail gets sent - REQUIRED
    Local $Password = "********"                  ; password for the account used from where the mail gets sent - REQUIRED
    Local $IPPort = 25                            ; port used for sending the mail
    Local $ssl = 0                                ; enables/disables secure socket layer sending - put to 1 if using httpS

    For $i = 1 To 10
        $Number = _ExcelReadCell($oExcel, $i, 1) ; You assign to this but do nothing with it
        $Email = _ExcelReadCell($oExcel, $i, 2) ; You assign to this but do nothing with it
        $FromAddress = $Email

        _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)

        If @error Then
            MsgBox(0, "Error sending message", "Unable to find specified file name.")
        EndIf 
    Next
EndFunc

.

which is, btw, about the same as what jaberwocky6669 suggested before...

Edited August 7, 2013 by Edano

jaberwacky · August 8, 2013

You're right E. I don't take the time to make sure that everything is perfectly correct if I'm guiding someone else. The idea is for them to figure things out. If they can't then they need to ask.

Edano · August 8, 2013

yes, but i have the impression we're moving in circles here

Function in a For loop

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members