run-time error “440”: The object does not support

2019-08-16 22:47发布

I'm getting a

run-time error "440": The object does not support this method.

The macro is designed to send multiple different emails and works until it gets to about email 500. The line the error is on is either .To=em or .BCC="" Any ideas what would cause it work so many time and then give me the error?

Sub TM_Email()


    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim Subject As String
    Dim LastRow As Long
    Dim em As String
    Dim y As Long
    Dim Result As Integer


    y = Range("A1").Value

    LastRow = Range("A1048576").End(xlUp).Row

    em = Range("H2").Value

    Subject = "Urgent - action needed!"

    'On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sendrng = ActiveSheet.Range("A2:D" & LastRow)

    'Remember the activesheet
    Set AWorksheet = ActiveSheet

    With Sendrng

        ' Select the worksheet with the range you want to send
        .Parent.Select

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope



            With .Item
                .SentOnBehalfOfName = "CompanyEmail"
                .To = em
                .CC = " "
                .BCC = " "
                .Subject = Subject

                If y = 2 Then

                .display

                Result = MsgBox("Does this look ok?", vbYesNo)

                    If Result = vbNo Then


            ActiveWorkbook.EnvelopeVisible = False

                    End
                    End If

                End If

                .Send
            End With

        End With

    End With

    AWorksheet.Select


StopMacro:


With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub

标签: excel vba
0条回答
登录 后发表回答