I want this code to filter out some different values and copy the results to a new spreadsheet saving it as the persons name (with date) and then send it as an attachment. I have two arrays, one containing names and the other containing e-mails.
However, I've run into some issues. I'm getting an error 'cannot assign to array' on the line of code: JCMails = Array("[email protected]", "[email protected]").
Sub LOOKUP()
Dim myFilename As String
Dim JCNames(2) As String
Dim JCMails(2) As String
JCNames = Array("x", "Y")
JCMails = Array("[email protected]", "[email protected]")
Dim i As Long
Dim j As Integer
j = 0
For i = 0 To UBound(JCNames)
myFilename = JCNames(i) & Format(Now(), "ddmmyyyy")
Range("G1").Select
ActiveSheet.Range("A:N").AutoFilter Field:=7, Criteria1:=JCNames(i)
With ActiveSheet
lRows = .Cells(.Rows.Count, "B").End(xlUp).Row
lCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(Cells(1, 1), Cells(lRows, lCols)).Copy
End With
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Columns.AutoFit
ActiveWorkbook.SaveAs Filename:=myFilename, FileFormat:=xlOpenXMLWorkbook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = JCMails(i)
.CC = ""
.BCC = ""
.Subject = "delays for" & JCNames
.Body = "Hi there" & JCNames
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
i = i + 1
j = j + 1
Next i
end sub
please can someone tell me what am I doing wrong with my loop!? :(