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.
Doug FisherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please copy and paste the code here. Then select it and press the CODE button.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
or at least post a link to the code ...
Doug FisherAuthor Commented:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Doug FisherAuthor Commented:
Hi.  This is a direct copy of the code Sid had posted.
Lyubomir DimitrovReporting & Excel SupportCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.