We help IT Professionals succeed at work.

Sid's script posted on 2011-03-23

Hello

I have copied and pasted the script to parse data from Outlook into Excel but when I run the rule, I get 'Compile error: Next with For' and on the line 'Next i', the word Next is highlighted.  Any help on this would be greatly appreciated.

Cheers.
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please copy and paste the code here. Then select it and press the CODE button.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
or at least post a link to the code ...
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Best you post the code your are running there, in case it accidentally got changed.

I did notice one duplicate variable definition, did you delete one of those SenderName declarations?

    Dim SenderName As String, SentTime As String, MailBody As String, strFileName As String
    Dim MyArray() As String, SenderName As String, Address1 As String, Address2 As String

Open in new window


»bp

Author

Commented:
Hi.  This is a direct copy of the code Sid had posted.
Lyubomir DimitrovReporting & Excel Support

Commented:
Hi

there is an example data, did you try running this one? I just check that .doc file it is pretty explanatory, perhaps you missed a step.

Good Luck,
Lyubo
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Okay, did some testing here.  If I took the script in this comment, and only changed the path to the Excel file, when it was executed by the rule I got the following error, as I would expect.
sshot-343.pngI corrected this removing just the extra DIM of the variable SenderName (my code below), and then retried.  It ran successfully and updated the Excel file with the data.

I'm posting the code that worked for me below.  When you put the code into the Outlook VB Editor, I would suggest that you then do Debug --> Compile and make sure there are no syntax errors.  If there are report back here...

Sub CaptureData(MyMail As MailItem)
    Dim SenderName As String, SentTime As String, MailBody As String, strFileName As String
    Dim MyArray() As String, Address1 As String, Address2 As String
    Dim strTemp() As String, TlNo As String, MobNo As String
    
    Dim LastRow As Long
    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    On Error GoTo Whoa
    
    '~~>  Change File Name Here
    strFileName = "B:\EE\EE29066220\Data.xls"
    
    '~~> Extraction of Details
    strID = MyMail.EntryID
    MailBody = MyMail.Body
    
    MyArray = Split(MailBody, vbNewLine)
    For i = LBound(MyArray) To UBound(MyArray)
        'NAME:
        If InStr(1, MyArray(i), "NAME:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "NAME:")
            SenderName = Trim(strTemp(1))
        End If
        
        'Address1
        If InStr(1, MyArray(i), "SHIPPING ADDRESS1:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS1:")
            Address1 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS1:", ""))
        End If
        
        'Address2
        If InStr(1, MyArray(i), "SHIPPING ADDRESS2:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS2:")
            Address2 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS2:", ""))
        End If
        
        'DAYTIME TELEPHONE NUMBER:
        If InStr(1, MyArray(i), "DAYTIME TELEPHONE NUMBER:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "DAYTIME TELEPHONE NUMBER:")
            TlNo = Trim(Replace(MyArray(i), "DAYTIME TELEPHONE NUMBER:", ""))
        End If

        'DAYTIME CELL PHONE NUMBER:
        If InStr(1, MyArray(i), "DAYTIME CELL PHONE NUMBER:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "DAYTIME CELL PHONE NUMBER:")
            MobNo = Trim(Replace(MyArray(i), "DAYTIME CELL PHONE NUMBER:", ""))
        End If
    Next i
    
    '~~> Create a new instance of Excel
    Set oXLApp = New Excel.Application
    '~~> Open Excel File
    Set oXLBook = oXLApp.Workbooks.Open(strFileName)
    '~~> Work with First Workbook
    Set oXLSheet = oXLBook.Worksheets(1)
    oXLApp.Visible = False
    oXLApp.DisplayAlerts = False
    oXLApp.ScreenUpdating = False
    
    LastRow = oXLSheet.Range("A" & oXLApp.Rows.Count).End(xlUp).Row + 1
    
    '~~> Index Number
    oXLSheet.Range("A" & LastRow) = SenderName
    oXLSheet.Range("B" & LastRow) = Address1
    oXLSheet.Range("C" & LastRow) = Address2
    oXLSheet.Range("D" & LastRow) = TlNo
    oXLSheet.Range("E" & LastRow) = MobNo
    
LetsContinue:
    oXLApp.DisplayAlerts = True
    oXLApp.ScreenUpdating = True
    
    '~~> Close and save
    oXLBook.Close savechanges:=True
    
    '~~> CLEANUP (VERY IMPROTANT)
    Set oXLSheet = Nothing
    Set oXLBook = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window


»bp