VBA email script doing weird....

[Lukums]

Hey guys... my script is doing weird things and can't workout why!

What is happening when ALL rows = correct criteria and the email function triggers it's actually placing "DONE" against all lines... instead of just that Row (i).... and moving down to the next.

The thing is I'm only saying to do this in "E" as a once off so I can't workout the function.

Sub SendMail()

Dim i As Long
Dim lastrow As Integer
   
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


Sheets("Sheet1").Select


For i = 4 To lastrow


 If Sheets("Sheet1").Range("D" & i).Value <= 7 Then
        Sheets("Sheet1").Range("E" & i).Value = "DONE"
        ActiveWorkbook.FollowHyperlink "mailto:" & Cells(i, 1) & _
        "?Subject=Shed to be delivered shortly&Body=Dear " & _
        Cells(i, 2) & ", Your shed has been scheduled for DELIVERY within the next 7 DAYS if you need to make any changes to please please contact xxx immediately on (02) xxx The delivery date is " & _
        Cells(i, 3) & "."
    End If
next i

 

[Michael M]

Re: vBA email script doing weird....

Not sure if it is related but is Sheet1 the Activesheet ?
If not your code will find the lastrow of whatever sheet is active BEFORE SHeet1 is selected !!
Maybe this would be better....UNTESTED

Sub MM1()
Dim i As Long, lastrow As Long
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1")
    For i = 4 To lastrow
     If .Range("D" & i).Value <= 7 Then
            .Range("E" & i).Value = "DONE"
            ActiveWorkbook.FollowHyperlink "mailto:" & Cells(i, 1) & _
            "?Subject=Shed to be delivered shortly&Body=Dear " & _
            .Cells(i, 2) & ", Your shed has been scheduled for DELIVERY within the next 7 DAYS if you need to make any changes to please please contact xxx immediately on (02) xxx The delivery date is " & _
            .Cells(i, 3) & "."
        End If
    Next i
End With
End Sub

 

[Lukums]

Re: vBA email script doing weird....

Hey Michael,

No good does exactly the same thing.

It's flagging ALL other lines if <= 7 as DONE. Which is super weird...

It only flags when: This triggers:

ActiveWorkbook.FollowHyperlink "mailto:" & Cells(i, 1) & _
"?Subject=Shed to be delivered shortly&Body=Dear " & _
.Cells(i, 2) & ", Your shed has been scheduled for DELIVERY within the next 7 DAYS if you need to make any changes to please please contact xxx immediately on (02) xxx The delivery date is " & _
.Cells(i, 3) & "."
End If
Next i

 

[Michael M]

Re: vBA email script doing weird....

Is <= 7 a calculation ??