Send a mail with attachments but the attachments has a unknown part in the filename

[Nikki001]

Hi everyone,

For my project I need to send a mail with the Error Log and the Excel files concerned by the error log. 

I already found the script (Made my Jos I think?) that can send the mail with the files attached, it works great (I'm adding it to the post for others to use.)

Here's my problem : The excel files i need to send have a date in the file name that'll change every day a new file is send it, so today's file would look like : Excel 20230220.xlsx and tomorrow's will look like : Excel 20230221.xlxs . 

The mail script requires for the attached files to be send in ".." with a ";" (semicolon) seperating the files. 

Since my excel has a pattern : excel *.xlsx; I was wondering how could I add this in the script lines for the attached files that would work ? 

I already tried to put the WHOLE path with the pattern at the end, but I get this error msg :  ### COM Error !  Number: 80020009   ScriptLine: 82   Description:La syntaxe du nom de fichier, de répertoire ou de volume est incorrecte = Basically saying that the syntaxe, folder or file volume is incorrect. I imagine it's because of the "*". Thank you for your help.

expandcollapse popup

;Send Email
; Variables
;##################################
$SmtpServer = ""                  ; address for the smtp-server to use - REQUIRED
$FromName = ""                    ; name from who the email was sent
$FromAddress = ""                 ; address from where the mail should come
$ToAddress = ""                   ; destination address of the email - REQUIRED
$Subject = ""                     ; 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 = ""                   ; 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 = 0                       ; 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

 

[Jos]

That udf doesn't support filemasks and will require exactfiles names.

 

[water]

Use _FileListToArray to create the list of files to attach. _FileListToArray supports wildcards.

[Nikki001]

13 minutes ago, water said:

Use _FileListToArray to create the list of files to attach. _FileListToArray supports wildcards.

I'm not sure if I didn't do it correctly or if it doesn't work this way, because it tells me that I have a badly formatted variable or macros : 

 

$excelfiles = _FileListToArray($inputfolder & "\" & $searchpattern, "*")
If @error = 1 Then
                MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.")
                Exit
        EndIf
        If @error = 4 Then
                MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.")
                Exit
        EndIf

$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" $excelfiles; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed

 

[water]

To get all txt files in the specified folder you should use

$searchpattern = "*.txt"
$excelfiles = _FileListToArray($inputfolder, $searchpattern, $FLTA_FILES)

 

[Nikki001]

17 hours ago, water said:

To get all txt files in the specified folder you should use

$searchpattern = "*.txt"
$excelfiles = _FileListToArray($inputfolder, $searchpattern, $FLTA_FILES)

 

they're excel files and txt files, but that probably doesn't change much. However I'm pretty sure I can't input a $ variable in the line for the files attachments.. Like in here : 

 

23 hours ago, Nikki001 said:

$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" $excelfiles; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed

 

[water]

Use _ArrayToString to create a variable holding all files returned by _FileListToArray. Use ; as delimiter and start with row 1 (as row 0 holds the counter).

Edited February 21, 2023 by water

[mistersquirrle]

9 hours ago, Nikki001 said:

they're excel files and txt files, but that probably doesn't change much. However I'm pretty sure I can't input a $ variable in the line for the files attachments.. 

I don't see why you couldn't use a variable, however your syntax here is wrong. To concatenate two strings use an ampersand ( & ). You need to do something like this:

$excelfiles = 'C:\File1.txt;'
$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" & $excelfiles ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
ConsoleWrite($AttachFiles & @CRLF)

 

Edited February 21, 2023 by mistersquirrle

[Nikki001]

12 hours ago, mistersquirrle said:

I don't see why you couldn't use a variable, however your syntax here is wrong. To concatenate two strings use an ampersand ( & ). You need to do something like this:

$excelfiles = 'C:\File1.txt;'
$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" & $excelfiles ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
ConsoleWrite($AttachFiles & @CRLF)

 

Here's the variable : 

 

$excelfiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx" 

; Here's the code for the input files : 

$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" & $excelfiles

;as you can see I have both a .txt file AND a .xlxs file, I use a variable for the excel file because, right now I only have one BUT I might get more than one in the folder at the time in the future. 

;and THIS is the error I get in the command when I launch the script using the variables above. 

+> File attachment added: C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx
### COM Error !  Number: 80020009   ScriptLine: 93   Description:La syntaxe du nom de fichier, de répertoire ou de volume est incorrecte.
>Exit code: 0


;So, I know I can add .txt files, because that works, BUT I don't think I can add excel files (and as Jos, the creater of the UDF said above, the UDF ;doesn't allow for "*" files to work). My $searchpattern in the code NEEDS to have a "*" because, as I stated, the excel file all have the same pattern ;as filename but have a date added to that filename that will change based on the day the file is sent, and it's needed to archive the excel file. 


;So, I'm trying to find a way to add my excels files KNOWING that the files will have a "*" part in the $searchpattern that will still work, maybe ;converting the excel file to another format ? I know you can flatten .txt files, is there a way to do that for excel files too that would work for the ;script ?

 

Edited February 22, 2023 by Nikki001

[Jos]

So why are you trying to use * again when you know it doesn't work?
Use the proposed script logic to find the exact finame(s) and then put that in the $AttachFiles variable.

.. or use FileFindFirstFile() / FileFindNextFile() to loop through the FIle mask and form the $variable content.

Edited February 22, 2023 by Jos

[Nikki001]

1 minute ago, Jos said:

So why are you trying to use * again when you know it doesn't work?
Use the proposed script logic to find the exact finame(s) and then put that in the $AttachFiles variable.

I'm not, I'm trying to find a way around that, which is why I tried the _FileListToArray function but it didn't work, probably because I used it wrong. 

My initial question was just to know if "*" could be used, to which you replied no, now i'm trying to do something else. And people, which I'm thankful for, that are replying are replying without realising that I'm not asking this for a .txt file, but for an excel file that has an "*" in it's name. 

 

Anyway, point being : I can't use "*", I need to find another way. I'm going to mark this post as solved as you clearly answered my original question and this is starting to become something that doesn't reply to the original question. 

 

Thanks again everyone for your imput, I appreciate it. 

Have a good day. 

[Nikki001]

14 minutes ago, Jos said:

or use FileFindFirstFile() / FileFindNextFile() to loop through the FIle mask and form the $variable content.

 

I did : 

 

$search = FileFindFirstFile("C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx")
$file = FileFindNextFile($search)

$AttachFiles = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;" & $file

 

But then I get this error : 

 

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" /ErrorStdOut "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Envoie Mail Log error.au3"    
+> File attachment added: C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt
!> File not found to attach: C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx
>Exit code: 0

 

which I have trouble understanding why the path doesn't go in the "Fichier logError" folder and stops at "testCompletBoisseaux" folder. 

It clearly found the .xlxs as it shows the full name, but now it's a matter of "why" it can't be added if it is found

Edited February 22, 2023 by Nikki001

[Jos]

Is that filename correct and is that file not opened by Excel at the time you run the script?

[Nikki001]

2 hours ago, Jos said:

Is that filename correct and is that file not opened by Excel at the time you run the script?

Yes, I copy pasted the path and filename from it's properties to make sure I didn't make a mistake, and Excel is not on when I run the script, since I know it won't work.

[Jos]

Ok .... lets do some debugging as I see way to fragmented information...for example: FileFindNextFile() doesn't return the full path so doubt you get that last error message from that piece of code. 
Try this piece of code by running it from SciTE and give me the result shown in the SciTE outputpane:

$basedir = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\"
$AttachFiles = $basedir & "logError.txt"

; Assign a Local variable the search handle of all files in the current directory.
Local $hSearch = FileFindFirstFile($basedir & "IMPORT BASE ARTICLES ET RECPTIONS *.xlsx")

; Check if the search was successful, if not display a message and return False.
If $hSearch = -1 Then
    ConsoleWrite("Error: No files/directories matched the search pattern." & @CRLF)
    Exit
EndIf

; Assign a Local variable the empty string which will contain the files names found.
Local $sFileName = "", $iResult = 0

While 1
    $sFileName = FileFindNextFile($hSearch)
    ; If there is no more file matching the search.
    If @error Then ExitLoop

    If FileExists($basedir & $sFileName) Then
        If FileCopy($basedir & $sFileName, @TempDir & "\" & $sFileName, 1) Then
            ConsoleWrite('File copied successfully to temp : ' & @TempDir & "\" & $sFileName & @CRLF)
            ; Display the file name.
            $AttachFiles &= ';' & $basedir & $sFileName
            ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $AttachFiles = ' & $AttachFiles & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
            FileDelete(@TempDir & "\" & $sFileName)
        Else
            ConsoleWrite('!copy failed: ' & $basedir & $sFileName & @CRLF)
        EndIf
    Else
        ConsoleWrite("!File doesn't exists: " & $basedir & $sFileName & @CRLF)
    EndIf
WEnd
ConsoleWrite('>>> $AttachFiles = ' & $AttachFiles & @CRLF ) ;### Debug Console

 

Edited February 22, 2023 by Jos

[Nikki001]

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" /ErrorStdOut "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\testforum.au3"    
File copied successfully to temp : C:\Users\NIKKI~1.COM\AppData\Local\Temp\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx
@@ Debug(26) : $AttachFiles = C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx
>Error code: 0
>>> $AttachFiles = C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\logError.txt;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx
>Exit code: 0

Here you go  

[Jos]

Pretty sure the path was wrong before when I compare these...   agree?  

;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx
;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx

...  so add that code you just tested with to your script as something like this:

$basedir = "C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\"
$AttachFiles = $basedir & "logError.txt"

; Assign a Local variable the search handle of all files in the current directory.
Local $hSearch = FileFindFirstFile($basedir & "IMPORT BASE ARTICLES ET RECPTIONS *.xlsx")
; Check if the search was successful, if not display a message and return False.
If $hSearch = -1 Then
    ConsoleWrite("Error: No files/directories matched the search pattern." & @CRLF)
    Exit
EndIf
; Assign a Local variable the empty string which will contain the files names found.
Local $sFileName = "", $iResult = 0
While 1
    $sFileName = FileFindNextFile($hSearch)
    ; If there is no more file matching the search.
    If @error Then ExitLoop
    $AttachFiles &= ';' & $basedir & $sFileName
WEnd
ConsoleWrite('>>> $AttachFiles = ' & $AttachFiles & @CRLF ) ;### Debug Console
; -- here your function call to _InetSmtpCom() with the $AttachFiles variable.

 

Edited February 22, 2023 by Jos

[Nikki001]

Just now, Jos said:

Pretty sure the path was wrong before when I compare these...   agree?  

;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx
;C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\IMPORT BASE ARTICLES ET RECPTIONS 20230208.xlsx

I agree, but that's where I had trouble understanding, because in my code I have this : 

 

$inputfolder ="C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\" 
$searchpattern = "IMPORT BASE ARTICLES ET RECPTIONS *.xlsx"
$errorfile = "logError.txt"


$search = FileFindFirstFile("C:\Users\nikki.combee\Desktop\Stage\AutoIT tests\Test AutoIT Excel\testCompletBoisseaux\Fichier logError\IMPORT BASE ARTICLES ET RECPTIONS *.xlsx")
$file = FileFindNextFile($search)

 

It should give the right path, I tried it as $inputfolder & "\" & $searchpattern but also using the whoooole path as in the $search, yet it give me the wrong path in the outputpane ? 

 

Anyway, I'll follow your advice and see the result it gives me 

[Nikki001]

It works now with the added code you posted last, thank you so much this was really the bane of my existence at this point. I'm so relieved it works, I really needed this for my internship. 

 

Thank you everyone that participated in this topic, sorry if I seemed unwilling or irritated, looking back on some replies I feel like I was. 

 

Have a nice day everyone. 

[Jos]

You are welcome....   and the lesson for today is:
Don't assume anything and always check everything, both exact string content as the results of each performed function.  

Jos