CODEWITHSUNDEEP
vbaoutlookoutlook-2010
0m3r
0m3r

Reputation: 12499

How to fix Outlook script rule Error

I'm trying to run my code using rule script to only processes newly arrived message but it keeps throwing Error

enter image description here

What am I doing wrong on my code?

Option Explicit
Public Sub Test(Item As Outlook.MailItem)
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
    Dim i As Long
    Dim rCount As Long
    Dim XStarted As Boolean
    Dim FileName As String
    Dim FilePath As String '// SaveAs CSV File Path
    Dim sPath As String '// .CSV File Path

    '// the path of the workbook
    sPath = "C:\temp\temp.csv"

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        XStarted = True
    End If

'    On Error GoTo 0
    '// Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(sPath)
    Set xlSheet = xlWB.Sheets("Report")

    '// Process received Mail
    sText = Item.Body
    vText = Split(sText, Chr(13)) ' Chr(13)) carriage return

    '// Find the next empty line of the worksheet
    rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(xlUp).Row
    rCount = rCount + 1

    '// Check each line of text in the message body
    For i = UBound(vText) To 0 Step -1

        '// Customer Name
        If InStr(1, vText(i), "Customer") > 0 Then
            vItem = Split(vText(i), Chr(9)) ' Chr(9) horizontal tab
            xlSheet.Range("A" & rCount) = Trim(vItem(1))
        End If

        '// Ref Number
        If InStr(1, vText(i), "Order #") > 0 Then
            vItem = Split(vText(i), Chr(9))
            xlSheet.Range("B" & rCount) = Trim(vItem(1))
        End If

        '// Service Level
        If InStr(1, vText(i), "Service Level") > 0 Then
            vItem = Split(vText(i), Chr(9))
            xlSheet.Range("J" & rCount) = Trim(vItem(1))
        End If
    Next i
    FilePath = Environ("USERPROFILE") & "\Documents\Temp\"
    FileName = Sheets(1).Range("B2").Value
    xlWB.SaveAs FileName:=FilePath & FileName

    '// Close & SaveChanges
    xlWB.Close SaveChanges:=True
    If XStarted Then
        xlApp.Quit
    End If

    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    Set Item = Nothing
 End Sub

Upvotes: 0

Views: 1024

Answers (1)

0m3r
0m3r

Reputation: 12499

Per Patrick.

You may have changed the VBA Project name. Go to Outlook Rules, Edit, re-assign the macro.

Also, it should FileName = xlWB.Sheets(1).Range("B2").Value And get rid of Application.StatusBar = ...

Thanks...

Upvotes: 1

Related Questions