Sending email via excel vba - the variables do not refresh in loop

[Lalit Bhandry]

Below prog. runs successfully except that email picks up the same details as in first row.
The flags work well, the variable too works well except email sent picks the first row only.
how can the command line be made variable,

strCommand = strCommand & " -compose " & "to=" & Chr(34) & Eadd & Chr(34)
strCommand = strCommand & ",subject=" & Chr(34) & Subj & Chr(34)
strCommand = strCommand & ",body=" & Chr(34) & eBody & Chr(34)
strCommand = strCommand & ",attachment=" & "G:\Staff Reports 2015\Pmail\" & Trim(nam)

Sub Sendout()
'
' op Macro
' Macro recorded by lalit
'
'
A = 5

Dim nam As String
Dim Flag As String
Dim Subj As String
Dim Eadd As String
Dim eBody As String

Tnam = Cells(A, 2).Value

nam = Cells(A, 2).Value
Flag = Cells(A, 1).Value
Subj = Cells(A, 3).Value
Eadd = Cells(A, 4).Value
Eadd1 = Cells(A, 5).Value
eBody = Cells(2, 11).Value


Do While Tnam <> ""

           If Flag = "Y" Then


strTh = "C:\Program Files (x86)\Mozilla Thunderbird\Thunderbird.exe "

strCommand = strCommand & " -compose " & "to=" & Chr(34) & Eadd & Chr(34)
strCommand = strCommand & ",subject=" & Chr(34) & Subj & Chr(34)
strCommand = strCommand & ",body=" & Chr(34) & eBody & Chr(34)
strCommand = strCommand & ",attachment=" & "G:\Staff Reports 2015\Pmail\" & Trim(nam)

Shell strTh & strCommand, vbNormalFocus

SendKeys "^+{ENTER}", True

               End If

A = A + 1

nam = Cells(A, 2).Value
Flag = Cells(A, 1).Value
Subj = Cells(A, 3).Value
Eadd = Cells(A, 4).Value
Eadd1 = Cells(A, 5).Value
eBody = Cells(2, 11).Value
Tnam = Cells(A, 2).Value

'tesing if variable takes the new value, it does
Workbooks("LB_test.xls").Sheets("Mreco").Activate
ActiveSheet.Cells(A, 15).Select
ActiveCell.FormulaR1C1 = nam

Loop


End Sub

Thank you.

 

[Hui]

Lalit

Firstly, Welcome to the Chandoo.org Forums

Is the line
eBody = Cells(2, 11).Value
correct?

Shouldn't it be:
eBody = Cells(A, 11).Value

It is used twice in the code

 

[Lalit Bhandry]

Lalit

Firstly, Welcome to the Chandoo.org Forums

Is the line
eBody = Cells(2, 11).Value
correct?

Shouldn't it be:
eBody = Cells(A, 11).Value

It is used twice in the code

Oops! sorry, it is invaribale Cells(2,11) is right as I have txt message in just one cell and hence i have frozen it.

 

[Hui]

Can you post the file as the code works ok for me,
But I don't have your data

 

[Lalit Bhandry]

Can you post the file as the code works ok for me,
But I don't have your data

I have sent you the file, hope you got it. Thx.

 

[Lalit Bhandry]

I have sent you the file, hope you got it. Thx.

Hi, any break thro' in my query. thx.

 

[Hui]

The code runs through the Loop and the If correctly on my PC

Can you explain what isn't working?

 

[Lalit Bhandry]

The code runs through the Loop and the If correctly on my PC

Can you explain what isn't working?

Dear Hui,
The program runs through perfectly. The only problem is

Email gets created but with the same attachment, subject and email address. If I have flagged Y to various row, it just picks up the first file from the flag. Hence all the email created has the same file attached with the same subject in subject.

Hope I was clear this time.

Thanks

 

[Lalit Bhandry]

Dear Hui,
The program runs through perfectly. The only problem is

Email gets created but with the same attachment, subject and email address. If I have flagged Y to various row, it just picks up the first file from the flag. Hence all the email created has the same file attached with the same subject in subject.

Hope I was clear this time.

Thanks

Thanks I found the solution . Accidentally I found it after writing a simple code
strCommand = " " beofre endif. It picks up the relevant file and subject.

 

[Jimmyshio]

Good day to everyone !!

I need help here to auto send my customer's reminders via thunderbird email.
Below is the vba codes and I'm still unable to auto send. Wonder why ?

Sub TestEmail()

    Dim sCmd As String
    Dim sTo As String
    Dim sCc As String
    Dim sSubject As String
    Dim sBody As String
  
    sTo = Range("G2")
    sCc = Range("G3")
    sSubject = Range("G4")
    sBody = Range("A1")
  
    sCmd = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe"
    sCmd = sCmd & " -compose " & Chr$(34) & "mailto:" & sTo & "?"
    sCmd = sCmd & "cc=" & Chr$(34) & sCc & Chr$(34) & "&"
    sCmd = sCmd & "subject=" & Chr$(34) & sSubject & Chr$(34) & "&"
    sCmd = sCmd & "body=" & Chr$(34) & sBody & Chr$(34)
        
    Call Shell(sCmd, vbNormalFocus)
    SendKeys "^+{ENTER}", True

End Sub