1

I created a subroutine which automates some Excel tasks, creates new files based on the data and then sends an e-mail. Below is what my code looks like. Every time I run this piece of code it throws a runtime error 438:Object doesn't support this property or method. I'm very new to VBA. Can anyone help me with this please?

Sub Foo()
    Dim FName As String

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\simohapatra\Desktop\AUTOMATION SM\Raw output.txt", Destination:= _
        Range("$A$1"))

        .Name = "Checkout"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
    1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .ActiveSheet.QueryTables.Refresh BackgroundQuery:=False
    End With

    Cells.Select
    Range("a1").Activate
    Selection.Cut
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\Users\simohapatra\Desktop\AUTOMATION SM\Final output"


    FName = "C:\Users\simohapatra\Desktop\AUTOMATION SM\Final output\SM Automation test output between " & Format(Date - 8, "d-mmm") & " and " & Format(Date - 1, "d-mmm") & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=FName, FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWorkbook.Close True

    Windows("1. Carol shipping query.xlsm").Activate

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "simohapatra@ebay.com"
        .CC = "edeepak@ebay.com"
        .BCC = ""
        .Subject = "SM Automation test done"
        .Body = "Hi SM, this is to inform that the test results are saved at   C:\Users\simohapatra\Desktop\AUTOMATION SM\Final output."
        '.Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send ' <--------------------------------This is causing troubble
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    Dim sKillExcel As String
    sKillExcel = "TASKKILL /F /IM Excel.exe"
    Shell sKillExcel, vbHide
End Sub
Community
  • 1
  • 1
siddhant
  • 11
  • 1
  • on what line does the error occur? – RealCheeseLord Aug 31 '17 at 12:22
  • I would recommend early-binding `OutApp` by adding a reference to `Microsoft Outlook 15.0 Object Library` (VBA IDEA > Tools > References). I have the strong suspicion that something is going on with your `MailItem` that you are getting from `OutApp`. You also wrap the entire block with an `On Error Resume Next` which means you're supressing warnings that could otherwise help you further identify the source of the issue. – Brandon Barney Aug 31 '17 at 12:30
  • 1
    @RealCheeseLord The OP put a comment near the bottom to point out the problem area. I almost missed it as well. – Brandon Barney Aug 31 '17 at 13:11
  • do you see a prompt asking permission to send email? – cyboashu Aug 31 '17 at 13:41

0 Answers0