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.
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
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