Excel VBA Loop Routine Outlook email not printing first message

[Rocky Swartz]

Hi
I have a problem with a routine that:-
1) loops through a customer table (CustCode(col1),FirstName(col3),EMail(col5)
2) make a copy (based on CustCode) of a master workbook and sends it to a designated directory
3) uses outlook to send the new workbook to the sender (using firstName, Emial)

There is no problem with the lop, the copying of files except that the first email has the attachment and no e-mail body. The second email has the right file, but the email and name of the first emails. This continues until the last ro is reached.

Private Sub MakeCopies_Click()
Dim folderPath As String
Dim fileName As String
Dim book As Workbook
Dim ws As Worksheet
Dim custName As String
Dim custCode As String
Dim lastRow As Integer
Dim custTotal As Long
Dim activeRow As Long
Dim orderMonth As String
Dim custMail As String
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String
Dim attachName As String
folderPath = "D:\Books\"
attachName = "D:\Docs\important info.xlsx"
   
 Sheets("Customers").Select
        
       With ActiveSheet
            lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
            orderMonth = Range("I5").Value
        End With
    custTotal = lastRow - 1
    activeRow = 2
    Set OutApp = CreateObject("Outlook.Application")
    Do While activeRow <> custTotal + 2
   
        custCode = Sheets("Customers").Cells(activeRow, 1).Value
        custName = Sheets("Customers").Cells(activeRow, 3).Value
        custMail = Sheets("Customers").Cells(activeRow, 5).Value
        fileName = (folderPath & custCode & "-" & orderMonth & "-Order.xlsx")
        FileCopy "D:\Master\OrderMaster.xlsx", fileName
 
        Set OutMail = OutApp.CreateItem(0)       
        On Error Resume Next
        With OutMail
            .to = custMail
            .CC = ""
            .BCC = ""
            .Subject = "Testing: Just Nuts " & orderMonth & " Order Form Automation"
            .Body = strBody
                strBody = "Hi " & [custName] & vbNewLine & vbNewLine & _
                "Please find the Just Nuts" & [orderMonth] & " order form attached." & vbNewLine & _
                "This month we are using a new system to help us " & vbNewLine & _
                "colate the orders in a more efficiant way.  There is a" & vbNewLine & _
                "'How To' file attached, to assist you in completing the order form." & vbNewLine & _
                "" & vbNewLine & _
                "Thank you" & vbNewLine & _
                "" & vbNewLine & _
                "Kim"
            .Attachments.Add fileName
            .Attachments.Add attachName
            .Send
        End With
        On Error GoTo 0
        Set OutMail = Nothing

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        'Debug.Print fileName
        activeRow = activeRow + 1
    Loop
Set OutApp = Nothing
End Sub

When I run the code on break mode, all the variables are pulling through with the right values before the send command.

Not sure why it would keep the files sent in the right order, but use the previous send variable for the name and e-mail address.

Appreciate if someone would assist

 

[nardagus]

As for missing mail body in a first mail. You define "strBody" variable after .Body = strBody. So on first mail strBody is empty.
And since you define this variable after .Body, that's why second mail gets data from a previous customer.

 

[Rocky Swartz]

Thank you Nard. I jnew it was something as trivial as that. Takes an expert to see it though

 

[nardagus]

Glad I could help.