Sid's script posted on 2011-03-23


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.

Doug FisherAsked:
Who is Participating?
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:
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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

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

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,
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)
        If InStr(1, MyArray(i), "NAME:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "NAME:")
            SenderName = Trim(strTemp(1))
        End If
        If InStr(1, MyArray(i), "SHIPPING ADDRESS1:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS1:")
            Address1 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS1:", ""))
        End If
        If InStr(1, MyArray(i), "SHIPPING ADDRESS2:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS2:")
            Address2 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS2:", ""))
        End If
        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

        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
    oXLApp.DisplayAlerts = True
    oXLApp.ScreenUpdating = True
    '~~> Close and save
    oXLBook.Close savechanges:=True
    Set oXLSheet = Nothing
    Set oXLBook = Nothing
    Set oXLApp = Nothing
    Exit Sub
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window


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.