VBA Not Recognizing Email Address in Excel Spreadsheet Cell

[Hobolord]

Hello,

I am working with excel 2013 on Windows 7 on a PC.

I have an excel spreadsheet with 3 columns. Column A has a file name (e.g. "Workbook1.xlsm"). Column B has email addresses (e.g. "Hobo@excel.com;Lord@Question.org"). Column C has a filepath to the file in Column A (e.g. "Y:\Event1\Reports").

The purpose of the code is to create an email for each row in column A, attach the file, and send it to the recipient. I have used this code successfully before, but for some reason, when I try now, I am getting the below error on the ".Recipients.Add cell.Offset(, 1).Value" line:

"Run-time error '440':

There must be at least one name or contact group in the To, CC, or Bcc box."

Here is the code:

Sub Email()


    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
        
    Dim OutApp As Object
    Dim fLoc As String
    Dim cell As Range, rng As Range
    Dim vFile As Variant, vFiles As Variant
    Dim Email As String
     
    Workbooks.Open ("Y:\Event1\Reports\AREA STATEMENTS.xlsx")
    Workbooks("AREA STATEMENTS").Activate
    Sheets("One").Activate
    
    'Make the file flat (removes formulas)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Application.CutCopyMode = False
    
        
    With ThisWorkbook.ActiveSheet
        Set rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    
    ' Read in the data and create a new message with attachment for each Excel entry
    For Each cell In rng
    
        'File path in column C
        fLoc = cell.Offset(, 2).Value
        If Right(fLoc, 1) <> "\" Then fLoc = fLoc & "\"
        
    'Email Body Text
        Email = "Hello All," & vbNewLine & vbNewLine _
             & "Attached you will find your statement." & vbNewLine & vbNewLine _
             & "Thank you," & vbNewLine & vbNewLine _
             & "Hobo"
            
        
        'Create a new Email for each recpient
        With OutApp.CreateItem(0)
            'From
            .SentOnBehalfOfName = "Hobo@excel.com"
            'Recipient
            .Recipients.Add cell.Offset(, 1).Value
            'Subject
            .Subject = "STATEMENT"
            'Body
            .Body = Email
            'Attach each file
            vFiles = Split(cell.Value, ";")
            For Each vFile In vFiles
                If Len(Dir(fLoc & vFile)) Then
                    .Attachments.Add fLoc & vFile
                Else
                 Call ActivateThisWB
                    'AppActivate (ThisWorkbook.Parent)
                    MsgBox "Could not locate file: " & vbCr & fLoc & vFile, , "File Not Found"
                End If
            Next vFile
            
            .Display
           '.Send
        End With
    Next cell
    
    
 Windows("AREA STATEMENTS.xlsx").Close


        
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True



End Sub

Does anyone see any glaring mistakes that I am missing? Or anything to help?

Thank you,

Hobo

 

[FormR]

Hi, shouldn't that line just be:

.To = cell.Offset(, 1).Value

 

[Hobolord]

Hmm, I switched it to your suggestion, and that created the email... but it didn't actually put the email address in the "To" box, nor did it add the attachment.

 

[FormR]

Hi, which workbook contains the email address etc. The workbook that contains the macro or the one you open in the code?

 

[Hobolord]

The workbook opened in the code "AREA STATEMENTS.xlsx" contains the email address/file names, etc. That would make sense that the sheet isn't activated... how would I correct that?

 

[FormR]

Maybe try changing this line:

With ThisWorkbook.ActiveSheet

To:

With ActiveWorkbook.ActiveSheet

 

[Hobolord]

WOW. I knew it had to be a noob mistake. Thanks so much for your help! The macro works as intended now.