Excel VBA Loop Routine Outlook email not printing first message

Rocky Swartz

New Member
Joined
Aug 25, 2005
Messages
15
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.

Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top