0

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!? :(

1 Answer 1

2

Array returns a variant containing an array. You need to declare JCMails and JCNames simply as being of type Variant -- NOT as arrays.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.