Read Excel Cells - Send email depending on whats in there

kylomas · August 29, 2013

Give this a whirl. The code depends on the XREF spreadsheet format not changing (columns and rows). You can add to each column without harm.

The email piece is your thing...

#include <Excel.au3>
#include <array.au3>

;teams / countries XRFE TBL

; [0] = team name
; [1] = boolean to indicate that an email was sent for this team
; [2]...[n] = country codes

local $aXREF_TBL[4][100]

Local $sXREFPath = @ScriptDir & '\destinationbreakdown.xlsx'
Local $oExcel = _ExcelBookOpen($sXREFPath,0)

if $oExcel = 0 then
    ConsoleWrite('Error opening ' & @ScriptDir & '\destinationbreakdown.xlsx' & @LF)
    Exit
endif

Local $sXREFSize = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$AXREFSize = StringRegExp($sXREFSize, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $XREFRow = $aXREFSize[0]
Local $XREFCol = $aXREFSize[1]

ConsoleWrite('Processing ' & $XREFRow & ' rows and ' & $XREFCol & ' columns' & @LF)

$aXREF_TBL[0][0] = $oExcel.Activesheet.Cells(3,2).Value ; team name
$aXREF_TBL[1][0] = $oExcel.Activesheet.Cells(3,4).Value ; team name
$aXREF_TBL[2][0] = $oExcel.Activesheet.Cells(3,6).Value ; team name
$aXREF_TBL[3][0] = $oExcel.Activesheet.Cells(3,8).Value ; team name
$aXREF_TBL[0][1] = 0
$aXREF_TBL[1][1] = 0
$aXREF_TBL[2][1] = 0
$aXREF_TBL[3][1] = 0

; populate South Pacific

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,1).Value = '' then exitloop
        $aXREF_TBL[0][$1-2] = $oExcel.Activesheet.Cells($1,1).Value
next

; populate Asia

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,3).Value = '' then exitloop
        $aXREF_TBL[1][$1-2] = $oExcel.Activesheet.Cells($1,3).Value
next

; populate Europe / Africa

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,5).Value = '' then exitloop
        $aXREF_TBL[2][$1-2] = $oExcel.Activesheet.Cells($1,5).Value
next

; populate Australia

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,7).Value = '' then exitloop
        $aXREF_TBL[3][$1-2] = $oExcel.Activesheet.Cells($1,7).Value
next

_ExcelBookClose($oExcel, 0)

Local $sFilePath1 = @ScriptDir & "\p_ebefffrm.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath1,0)

switch @error
    case 1
        MsgBox(0, "Error!", "Unable to Create the Excel Object")
        Exit
    case 2
        MsgBox(0, "Error!", "File does not exist - Shame on you!")
        Exit
endswitch

; get # of rows and columns
Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]

; define country results variable
local $sMList

; loop through visible entries and add an entry to the country results variable if it does not already exist
for $1 = 2 to $iLastRow
    if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then
    Else
        ; create string of unique country codes
        if not stringinstr($sMList, $oExcel.Activesheet.Cells($1,1).Value & ',') then $sMList &= $oExcel.Activesheet.Cells($1,1).Value & ','
    EndIf
next

ConsoleWrite(@LF)
ConsoleWrite('! Processing entries for countries : ' & stringtrimright($sMList,1) & @LF)
ConsoleWrite(@LF)

; create array to search against $aXREF_TBL
$aMList = stringsplit(stringtrimright($sMList,1),',',2)

; search XREF TBL and send email to clients
for $1 = 0 to ubound($aMList) - 1
    for $2 = 0 to ubound($aXREF_TBL) - 1
        for $3 = 2 to ubound($aXREF_TBL,2) - 1
            if $aXREF_TBL[$2][$3] = $aMList[$1] and $aXREF_TBL[$2][1] = 0 then
                _send_email($aXREF_TBL[$2][0])
                $aXREF_TBL[$2][1] = 1
            EndIf
        Next
    Next
next

ConsoleWrite(@LF)

_ExcelBookClose($oExcel, 0)

func _send_email($team)

    ConsoleWrite('!' & @tab & 'Sending Email to team name = ' & $team & @LF)

endfunc

kylomas

13lack13lade · August 29, 2013

Oh you wrote this script? Excuse me while i take my clothes off... haha

This is absolutely perfect, You are an absolute legend!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! words cannot express just how grateful i am...

thank you so much, if there is anything i can ever help you with just let me know!

kylomas · August 29, 2013

13lack13lade,

Relax... This is neither a complete nor elegant solution. It is just a framework to give you some ideas.

kylomas

kylomas · August 29, 2013

13lack13lade,

Let's keep all communications in topic. Someone else may have other/better ideas for your task, or, may be looking for a solution similar to yours.

Hey Kylomas,

Sorry to bother you again...

How do i use the information that gets sent to console to get my recipient list? or what would the best way be to get a variable so that i can quickly incoroporate this into my CDO email sender..

I need to make $ToAddress (line in my CDO sender) as the teams emails.. i know you have helped me alot so i dont expect you to code anything else for me but if you could kind of explain so i can join this script with my own that would be awesome (sorry i never use console write so im not familiar with what it can do)

Reply

The solution that I presented is based on two spreadsheets, one containing what you call "console" traffic and the other containing a cross-reference of locations to team.

I have no idea what you mean by "sent to console". How did you arrive at the 1ST spreadsheet you posted?

13lack13lade · August 30, 2013

#include <Excel.au3>
#include <array.au3>
#Include<file.au3>
#include<ie.au3>
#include<Date.au3>


;teams / countries XRFE TBL


; [0] = team name
; [1] = boolean to indicate that an email was sent for this team
; [2]...[n] = country codes


local $aXREF_TBL[4][100]


Local $sXREFPath = "Q:\Documents\Load Support\Tom\Automation" & '\destinationbreakdown.xlsx'
Local $oExcel = _ExcelBookOpen($sXREFPath,0)


if $oExcel = 0 then
    ConsoleWrite('Error opening ' & "Q:\Documents\Load Support\Tom\Automation" & '\destinationbreakdown.xlsx' & @LF)
    Exit
endif


Local $sXREFSize = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$AXREFSize = StringRegExp($sXREFSize, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $XREFRow = $aXREFSize[0]
Local $XREFCol = $aXREFSize[1]


ConsoleWrite('Processing ' & $XREFRow & ' rows and ' & $XREFCol & ' columns' & @LF)


$aXREF_TBL[0][0] = $oExcel.Activesheet.Cells(3,2).Value ; team name
$aXREF_TBL[1][0] = $oExcel.Activesheet.Cells(3,4).Value ; team name
$aXREF_TBL[2][0] = $oExcel.Activesheet.Cells(3,6).Value ; team name
$aXREF_TBL[3][0] = $oExcel.Activesheet.Cells(3,8).Value ; team name
$aXREF_TBL[0][1] = 0
$aXREF_TBL[1][1] = 0
$aXREF_TBL[2][1] = 0
$aXREF_TBL[3][1] = 0


; populate South Pacific


for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,1).Value = '' then exitloop
        $aXREF_TBL[0][$1-2] = $oExcel.Activesheet.Cells($1,1).Value
next


; populate Asia


for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,3).Value = '' then exitloop
        $aXREF_TBL[1][$1-2] = $oExcel.Activesheet.Cells($1,3).Value
next


; populate Europe / Africa


for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,5).Value = '' then exitloop
        $aXREF_TBL[2][$1-2] = $oExcel.Activesheet.Cells($1,5).Value
next


; populate Australia


for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,7).Value = '' then exitloop
        $aXREF_TBL[3][$1-2] = $oExcel.Activesheet.Cells($1,7).Value
next


_ExcelBookClose($oExcel, 0)


Local $sFilePath1 = "Q:\Documents\Load Support\Tom\Automation\QA Reports" & "\p_ebefffrm.xlsm"
Local $oExcel = _ExcelBookOpen($sFilePath1,0)


switch @error
    case 1
        MsgBox(0, "Error!", "Unable to Create the Excel Object")
        Exit
    case 2
        MsgBox(0, "Error!", "File does not exist - Shame on you!")
        Exit
endswitch


; get # of rows and columns
Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]


; define country results variable
local $sMList


; loop through visible entries and add an entry to the country results variable if it does not already exist
for $1 = 2 to $iLastRow
    if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then
    Else
        ; create string of unique country codes
        if not stringinstr($sMList, $oExcel.Activesheet.Cells($1,1).Value & ',') then $sMList &= $oExcel.Activesheet.Cells($1,1).Value & ','
    EndIf
next


ConsoleWrite(@LF)
ConsoleWrite('! Processing entries for countries : ' & stringtrimright($sMList,1) & @LF)
ConsoleWrite(@LF)


; create array to search against $aXREF_TBL
$aMList = stringsplit(stringtrimright($sMList,1),',',2)


; search XREF TBL and send email to clients
for $1 = 0 to ubound($aMList) - 1
    for $2 = 0 to ubound($aXREF_TBL) - 1
        for $3 = 2 to ubound($aXREF_TBL,2) - 1
            if $aXREF_TBL[$2][$3] = $aMList[$1] and $aXREF_TBL[$2][1] = 0 then
                _send_email($aXREF_TBL[$2][0])
                $aXREF_TBL[$2][1] = 1
            EndIf
        Next
    Next
next


ConsoleWrite(@LF)


_ExcelBookClose($oExcel, 0)


func _send_email($team)


    ConsoleWrite($team & @LF)


endfunc






$SmtpServer = "SMTP ADDRESS"                            ; address for the smtp-server to use - REQUIRED
$FromName = "13lack 13lade"                          ; name from who the email was sent
$FromAddress = "FROM EMAIL"                           ; address from where the mail should come
Global $ToAddress = $team ; destination address of the email - REQUIRED
$Subject = " report name "                      ; subject from the email - can be anything you want it to be
$Body = "enter body text here"
$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 = ""                              ; 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(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

No worries kylomas,

Okay so what i did was change the team names in the spreadsheet to email addresses... so $team = the team email addresses now.. however when joining with my CDO script i need $toaddress = $team so that this is how it gets the teams email addresses... however keeps saying variable used without being declared..

when i mean sent to console i am referring to 'console' traffic so for example in your script you have ConsoleWrite ($team) this gives you the team names.. i need $toaddress to = $team so that it gets the email address for the team to send it to.

kylomas · August 30, 2013

13lack13lade,

$team is a variable that exists only within the scope of function _send_email(). The reason that I organized it so was for you to put your email routine within that function, or call your email function from there with whatever parms are required.

kylomas

13lack13lade · September 5, 2013

With the script that i posted - the CDO is broken up into different Functions how do i put my entire email function within the _send_email function..

Or how can i re-type my code so that $team = $toaddress?

13lack13lade · September 5, 2013

; search XREF TBL and send email to clients
for $1 = 0 to ubound($aMList) - 1
    for $2 = 0 to ubound($aXREF_TBL) - 1
        for $3 = 2 to ubound($aXREF_TBL,2) - 1
            if $aXREF_TBL[$2][$3] = $aMList[$1] and $aXREF_TBL[$2][1] = 0 then
                _send_email($aXREF_TBL[$2][0])
                $aXREF_TBL[$2][1] = 1
            EndIf
        Next
    Next
next

Would my change need to be to delete the function _send_email and change the _send_email in the last bit of the code here to get $ToAddress?

Worked, didnt send any emails though so im guessing its trying to send to IE,NL,TH,ZA rather than $team.

Im completely lost!

13lack13lade · September 9, 2013

or even if i can somehow have it write into a spreadsheet the name of the teams so then i could just simply have an if these are shown email here.. etc..

Possible to do that??

kylomas · September 9, 2013

13lack13lade,

Your code should be organized similar to this. $team needs to contain the email address (change the team name in the orginal spreadsheet to an email address)

#include <Excel.au3>
#include <array.au3>
#Include<file.au3>
#include<ie.au3>
#include<Date.au3>

; Error handler

Global $oMyRet[2]
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

; Email definitions

global $SmtpServer = "SMTP ADDRESS"          ; address for the smtp-server to use - REQUIRED
global $FromName = "13lack 13lade"           ; name from who the email was sent
global $FromAddress = "FROM EMAIL"           ; address from where the mail should come
Global $ToAddress = ''                       ; destination address of the email - REQUIRED
global $Subject = " report name "            ; subject from the email - can be anything you want it to be
global $Body = "enter body text here"
global $AttachFiles = ""                     ; the file you want to attach- leave blank if not needed
global $CcAddress = ""                       ; address for cc - leave blank if not needed
global $BccAddress = ""                      ; address for bcc - leave blank if not needed
global $Importance = "Normal"                ; Send message priority: "High", "Normal", "Low"
global $Username = ""                        ; username for the account used from where the mail gets sent - REQUIRED
global $Password = ""                        ; password for the account used from where the mail gets sent - REQUIRED
global $IPPort = 25                          ; port used for sending the mail
global $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

;teams / countries XRFE TBL

; [0] = team name
; [1] = boolean to indicate that an email was sent for this team
; [2]...[n] = country codes

global $aXREF_TBL[4][100]

global  $sXREFPath = "Q:\Documents\Load Support\Tom\Automation" & '\destinationbreakdown.xlsx'
global  $oExcel = _ExcelBookOpen($sXREFPath,0)

if $oExcel = 0 then
    ConsoleWrite('Error opening ' & "Q:\Documents\Load Support\Tom\Automation" & '\destinationbreakdown.xlsx' & @LF)
    Exit
endif

global  $sXREFSize = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$AXREFSize = StringRegExp($sXREFSize, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
global  $XREFRow = $aXREFSize[0]
global  $XREFCol = $aXREFSize[1]

ConsoleWrite('Processing ' & $XREFRow & ' rows and ' & $XREFCol & ' columns' & @LF)

$aXREF_TBL[0][0] = $oExcel.Activesheet.Cells(3,2).Value ; team name
$aXREF_TBL[1][0] = $oExcel.Activesheet.Cells(3,4).Value ; team name
$aXREF_TBL[2][0] = $oExcel.Activesheet.Cells(3,6).Value ; team name
$aXREF_TBL[3][0] = $oExcel.Activesheet.Cells(3,8).Value ; team name
$aXREF_TBL[0][1] = 0
$aXREF_TBL[1][1] = 0
$aXREF_TBL[2][1] = 0
$aXREF_TBL[3][1] = 0

; populate South Pacific

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,1).Value = '' then exitloop
        $aXREF_TBL[0][$1-2] = $oExcel.Activesheet.Cells($1,1).Value
next

; populate Asia

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,3).Value = '' then exitloop
        $aXREF_TBL[1][$1-2] = $oExcel.Activesheet.Cells($1,3).Value
next

; populate Europe / Africa

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,5).Value = '' then exitloop
        $aXREF_TBL[2][$1-2] = $oExcel.Activesheet.Cells($1,5).Value
next

; populate Australia

for $1 = 4 to $XREFRow
        if $oExcel.Activesheet.Cells($1,7).Value = '' then exitloop
        $aXREF_TBL[3][$1-2] = $oExcel.Activesheet.Cells($1,7).Value
next

_ExcelBookClose($oExcel, 0)

global  $sFilePath1 = "Q:\Documents\Load Support\Tom\Automation\QA Reports" & "\p_ebefffrm.xlsm"
global  $oExcel = _ExcelBookOpen($sFilePath1,0)

switch @error
    case 1
        MsgBox(0, "Error!", "Unable to Create the Excel Object")
        Exit
    case 2
        MsgBox(0, "Error!", "File does not exist - Shame on you!")
        Exit
endswitch

; get # of rows and columns
global  $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
global  $iLastRow = $sLastCell[0]
global  $iLastColumn = $sLastCell[1]

; define country results variable
global  $sMList

; loop through visible entries and add an entry to the country results variable if it does not already exist
for $1 = 2 to $iLastRow
    if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then
    Else
        ; create string of unique country codes
        if not stringinstr($sMList, $oExcel.Activesheet.Cells($1,1).Value & ',') then $sMList &= $oExcel.Activesheet.Cells($1,1).Value & ','
    EndIf
next

ConsoleWrite(@LF)
ConsoleWrite('! Processing entries for countries : ' & stringtrimright($sMList,1) & @LF)
ConsoleWrite(@LF)

; create array to search against $aXREF_TBL
$aMList = stringsplit(stringtrimright($sMList,1),',',2)

; search XREF TBL and send email to clients
for $1 = 0 to ubound($aMList) - 1
    for $2 = 0 to ubound($aXREF_TBL) - 1
        for $3 = 2 to ubound($aXREF_TBL,2) - 1
            if $aXREF_TBL[$2][$3] = $aMList[$1] and $aXREF_TBL[$2][1] = 0 then
                _send_email($aXREF_TBL[$2][0])
                $aXREF_TBL[$2][1] = 1
            EndIf
        Next
    Next
next

ConsoleWrite(@LF)

_ExcelBookClose($oExcel, 0)

func _send_email($team)

    ; $team need to contain the email address of the recipient at this point

    $rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $team, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)
    If @error Then
        MsgBox(0, "Error sending message", "Error code:" & @error & "  Description:" & $rc)
    EndIf

endfunc

;
; 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(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

kylomas

Edited September 9, 2013 by kylomas

13lack13lade · September 9, 2013

Exactly what i was after, have tested and works great!

I am going to sit here and go through the code basically line by line so i can see how things are correctly structured.

really appreciate your time & patience kylomas... Thank you!

:ILA2:

kylomas · September 10, 2013

This comment make it all worth the effort...

I am going to sit here and go through the code basically line by line so i can see how things are correctly structured.

Good Luck,

kylomas

13lack13lade · September 11, 2013

Hey Kylomas,

1 Final thing.. ive gone through and ive got _ExcelWriteCell for $team to a cell however it only ever will enter 1 email address even if there are multiple email address it was sent it, i know its because they come through on different lines but is there a easy solution for this? *EDIT* if emailing to mulitple address it opens the same number of spreadsheets to write in that one cell rather than combinding them into the one cell.

Or depending how hard it would be:

depending on which email addresses are returned ExcelWriteCell ( "X" into the corressponding column depending on team (so A-D for example for the 4 teams then if it emails to Asia & Europe teams A2 & B2 get "X" or _NowDate in the cell..

Second one is the better option but i can imagine is alot more work so even if you are able to assist me with a possible quick fix or way of seperating the lines or something like that, that would be great! can always improve the process once my autoit skills have improved heh..

Edited September 11, 2013 by 13lack13lade

kylomas · September 11, 2013

13lack13lade,

You have everything you need to complete your task. Now it's time for you to produce something. If you run into troubles post your code with what went wrong and what was expected.

kylomas

13lack13lade · September 11, 2013

*EDIT*

No worries got everything working however have come across some issues...

Worked fine the first few times but now it is saying that cannot attach file, file is in use? have tried rebooting etc etc, not sure why its doing it. well clearly the file is in use but not sure whats causing it.

It works fine sending a .xlsx but doesnt want to send .xlsm files..

Edited September 13, 2013 by 13lack13lade

Read Excel Cells - Send email depending on whats in there

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

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members