Excel VBA Loop Routine Outlook email not printing first message

Rocky Swartz

New Member
Aug 25, 2005
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"
       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 & _
            .Attachments.Add fileName
            .Attachments.Add attachName
        End With
        On Error GoTo 0
        Set OutMail = Nothing

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        'Debug.Print fileName
        activeRow = activeRow + 1
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


Active Member
Apr 23, 2012
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. :)
Last edited:

Rocky Swartz

New Member
Aug 25, 2005
Thank you Nard. I jnew it was something as trivial as that. Takes an expert to see it though

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...