For-Next loop is not working correctly (while sending mail via Thunderbird from Excel)

[aloisk]

Hi all.
I have created VBA code for sending mails with different attachments to different addresses, via Thunderbird. The code looks correct but while creating particular mail bodies it uses still the values from the first line. I.e. while it should send 20 mails to 20 adresses (in each mail different attachment), it create 20 same mails (to the same first adress with the same first attachment).

And the strange fact is that in the debugging window all looks correct and the values are changing.

<code>Option Explicit  
Sub SendMailThunder_Click()  
Dim strEmpfaenger1 As String  
Dim strBetr As String  
Dim strBody As String  
Dim strFile2 As Variant  
Dim strTh As String  
Dim strCommand As Variant  
Dim Name As String
Dim Result As Variant
Dim List As Variant
Dim PS As Long
Dim y As Long

Set List = ThisWorkbook.Worksheets("Ridici")
'  number of items in the collumn
PS = List.Cells(Rows.Count, 11).End(xlUp).Row

With List
    For y = 4 To PS

    '  Name of attachment
    Name = .Cells(y, 12).Value

    '  selected email to particular driver
    strEmpfaenger1 = .Cells(y, 15).Value

    strBetr = .Range("O1")
    strBody = .Range("O2")

     strTh = "C:\Users\alois.konecny\AppData\Local\Mozilla Thunderbird\thunderbird.exe"

    '  path to attachment
        cesta = .Range("N1")

    '   attachment including path
    priloha = "\" & Nazev & ".xls"

    result = cesta & priloha
    strFile2 = result
    strCommand = strCommand & " -compose " & "to=" & Chr(34) & strEmpfaenger1 & Chr(34)
    strCommand = strCommand & ",subject=" & Chr(34) & strBetr &  Chr(34)
    strCommand = strCommand & ",body=" & Chr(34) & strBody & Chr(34)
    strCommand = strCommand & ",attachment=" & "file:///" & Replace(strFile2, "\", "/")
    Shell strTh & strCommand, vbNormalFocus
    Next y

End With
End Sub </code>

I will appreciate any help.

 

[Dendro]

I'm seeing some possible problems in your code
:

<code>Option Explicit  
Sub SendMailThunder_Click()  
Dim strEmpfaenger1 As String  
Dim strBetr As String  
Dim strBody As String  
Dim strFile2 As Variant  
Dim strTh As String  
Dim strCommand As Variant  
Dim Name As String
Dim Result As Variant
Dim List As Variant
Dim PS As Long
Dim y As Long

Set List = ThisWorkbook.Worksheets("Ridici")-> why are you doing this?
'  number of items in the collumn
PS = List.Cells(Rows.Count, 11).End(xlUp).Row

With List
    For y = 4 To PS

    '  Name of attachment
    Name = .Cells(y, 12).Value -> you have declared Name as a String, you should assign .Cells(y,12)[COLOR=#ff0000].Text[/COLOR] to it

    '  selected email to particular driver
    strEmpfaenger1 = .Cells(y, 15)[COLOR=#ff0000].Value[/COLOR]-> same here

    strBetr = .Range("O1")-> assigning a range to a string variable: <code>.Range("O1")[COLOR=#ff0000].Text[/COLOR]</code>
    strBody = .Range("O2")-> [COLOR=#ff0000]same here[/COLOR]

     strTh = "C:\Users\alois.konecny\AppData\Local\Mozilla Thunderbird\thunderbird.exe"

    '  path to attachment
        cesta = .Range("N1")-> not declared, i think this should be a string so you should be using <code>.Range("N1")[COLOR=#ff0000].Text[/COLOR] again</code>

    '   attachment including path
    priloha = "\" & Nazev & ".xls" ->prihola is not declared, declare it as a String

    result = cesta & priloha -> [COLOR=#ff0000]R[/COLOR]esult, should be declared as [COLOR=#ff0000]string, not as variant[/COLOR]
    strFile2 = result -> not really necessary, but no problem
    strCommand = strCommand & " -compose " & "to=" & Chr(34) & strEmpfaenger1 & Chr(34)
    strCommand = strCommand & ",subject=" & Chr(34) & strBetr &  Chr(34)
    strCommand = strCommand & ",body=" & Chr(34) & strBody & Chr(34)
    strCommand = strCommand & ",attachment=" & "file:///" & Replace(strFile2, "\", "/")
    Shell strTh & strCommand, vbNormalFocus
    Next y

End With
End Sub </code>

 

[aloisk]

Thank you Dendro for your answer. I changed the code according to your suggestion (as you can see bellow) but it still does not work. Please any other suggestions?

Option Explicit
Sub SendMailThunder_Click_XX()
Dim strEmpfaenger1 As String
Dim strBetr As String
Dim strBody As String
Dim strFile2 As Variant
Dim strTh As String
Dim strCommand As Variant
Dim Name As String
Dim Result As String
Dim List As Variant
Dim PS As Long
Dim y As Long
Dim Cesta As String
Dim Priloha As String

Set List = ThisWorkbook.Worksheets("Ridici")
'  number of items in the collumn
PS = List.Cells(Rows.Count, 11).End(xlUp).Row

With List
    For y = 4 To PS

    '  Name of attachment
    Name = .Cells(y, 12).Text '   -> you have declared Name as a String, you should assign .Cells(y,12).Text to it

    '  selected email to particular driver
    strEmpfaenger1 = .Cells(y, 15).Text ' -> same here

    strBetr = .Range("O1").Text ' -> assigning a range to a string variable: .Range("O1").Text
    strBody = .Range("O2").Text ' -> same here

     strTh = "C:\Users\alois.konecny\AppData\Local\Mozilla Thunderbird\thunderbird.exe"

    '  path to attachment
        Cesta = .Range("N1").Text ' -> not declared, i think this should be a string so you should be using .Range("N1").Text again

    '   attachment including path
    Priloha = "\" & Name & ".xls" ' ->prihola is not declared, declare it as a String

    Result = Cesta & Priloha ' -> Result, should be declared as string, not as variant
    strFile2 = Result ' -> not really necessary, but no problem
    strCommand = strCommand & " -compose " & "to=" & Chr(34) & strEmpfaenger1 & Chr(34)
    strCommand = strCommand & ",subject=" & Chr(34) & strBetr & Chr(34)
    strCommand = strCommand & ",body=" & Chr(34) & strBody & Chr(34)
    strCommand = strCommand & ",attachment=" & "file:///" & Replace(strFile2, "\", "/")
    Shell strTh & strCommand, vbNormalFocus
    Next y

End With
End Sub

 

[Dendro]

The problem is that I'm not that familiar with the Shell-command. But what i do see is that you are overwriting the strCommand each time which looks like it should be of type String if I look at the data you are assigning to it?Why not make this into one line?
think it could be usefull to look at what this function gives

Shell [COLOR=#ff0000]strTh & strCommand[/COLOR], vbNormalFocus

You are executing "C:\Users\alois.konecny\AppData\Local\Mozilla Thunderbird\thunderbird.exe"
which seems OK but what will we have as a result in strCommand? What are you trying to achieve with the '&' between them?

 

[aloisk]

Yes, the strCommand should be different in each loop, the result looks like (according to Debug.Print Window) this
-compose to="alois.k@email.cz",subject="Zkušební mail",body="Lojzův email - tělo",attachment=file:///C:/Users/alois.konecny/Desktop/Ridici/Jindřich_2015_09_09.xls
In the Debug window the result changes correctly, but in the real mail all remains still the same.

Unfortunately I am not familiar with the Shell command as well.

I tried also to change the type of strCommand to String but it didn´t help.

 

[Dendro]

i think shell command line should change, take a look at the syntax here:
Open and Close File with VBA Shell - wellsr.com

 

[aloisk]

I looked at it and as I see my command is similar to Variant 2. So it should be OK.
Other suggestion pls?

 

[Dendro]

Could you enter a Msgbox within your for loop showing the y value? and see what the results are? This to see whether the y value is actually incrementing, and by how much?

 

[aloisk]

The y value is increasing in each step for 1 - as it was intended. And according to it the other values are changing too. Both is the same in the debug print window as well as in the MsgBox.