Loop Not Working Now But Was Working Before

[jalrs]

Sub myloop()

Dim OutApp As Object
Dim OutMail As Object
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long, i As Long

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set ws1 = ThisWorkbook.Worksheets("Readme")
Set ws2 = ThisWorkbook.Worksheets("MACRO 2")

ws2.Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next

        For i = 2 To lr

             With OutMail
    
                .to = Cells(i, 2).Value
                .CC = Cells(i, 3).Value
                .Subject = Cells(i, 4).Value
                .Body = Cells(i, 7).Value
                .Display
                .Attachments.Add = Cells(i, 9).Value
        
            End With
    
        Next i

    On Error GoTo 0

Set OutMail = Nothing

ws1.Activate

End Sub

This Loop is not working now but it was working before unless it never was but I thought it was. It should open 2 email windows since i have 2 values on column A, starting in row 2, ending in row 3. Instead it opens only one e-mail window.

Thanks

 

[6StringJazzer]

I'm sure this was never working. You only have one email item in your code and you keep updating the same one, wiping out the previous data. Move creation of the email item into your loop so you use a fresh item each time. Update in red:

Sub myloop()

Dim OutApp As Object
Dim OutMail As Object
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long, i As Long

Set OutApp = CreateObject("Outlook.Application")

Set ws1 = ThisWorkbook.Worksheets("Readme")
Set ws2 = ThisWorkbook.Worksheets("MACRO 2")

ws2.Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next

        For i = 2 To lr

             Set OutMail = OutApp.CreateItem(0)
             With OutMail
   
                .to = Cells(i, 2).Value
                .CC = Cells(i, 3).Value
                .Subject = Cells(i, 4).Value
                .Body = Cells(i, 7).Value
                .Display
                .Attachments.Add = Cells(i, 9).Value
       
            End With
   
        Next i

    On Error GoTo 0

Set OutMail = Nothing

ws1.Activate

End Sub

 

[jalrs]

I'm sure this was never working. You only have one email item in your code and you keep updating the same one, wiping out the previous data. Move creation of the email item into your loop so you use a fresh item each time. Update in red:

Sub myloop()

Dim OutApp As Object
Dim OutMail As Object
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long, i As Long

Set OutApp = CreateObject("Outlook.Application")

Set ws1 = ThisWorkbook.Worksheets("Readme")
Set ws2 = ThisWorkbook.Worksheets("MACRO 2")

ws2.Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next

        For i = 2 To lr

             Set OutMail = OutApp.CreateItem(0)
             With OutMail
  
                .to = Cells(i, 2).Value
                .CC = Cells(i, 3).Value
                .Subject = Cells(i, 4).Value
                .Body = Cells(i, 7).Value
                .Display
                .Attachments.Add = Cells(i, 9).Value
      
            End With
  
        Next i

    On Error GoTo 0

Set OutMail = Nothing

ws1.Activate

End Sub

Yes thanks. in order to things being propery idented, should I move set outmail = nothing below the end with?

 

[6StringJazzer]

Yes thanks. in order to things being propery idented, should I move set outmail = nothing below the end with?

You can but it's not necessary. We are reusing the object to point to a new instance each time, then setting to Nothing when everything is done. (What you did earlier was to use the same instance every time.) You can set it nothing on each time through the loop but there is no benefit to doing that.

 

[jalrs]

Thanks for the answer.

One last question: By being outside does the macro run faster? If so, I'll let it outside

Thanks

 

[6StringJazzer]

Yes. It's faster if you only do it once.

 

[jalrs]

Yes. It's faster if you only do it once.

Thanks!