CODEWITHSUNDEEP
vbaexcel
Niisa
Niisa

Reputation: 11

Microsoft Excel VBA Run Time Error 5

I am so new on Excel VBA. I am working on Outlook automation to get data from Outlook email body, generate a report and send out to team. I just started trying out some recipes on getting data from Outlook email body and export to Excel. I tried extracting specific data in the email body to export to Excel sheet, it seems like I kept getting Run Time Error 5 running:

vText = Trim(M.SubMatches(1))

E.g.

Sub GetData()
    Dim folders As Outlook.folders
    Dim folder As Outlook.MAPIFolder
    Dim iRow As Integer
    Dim Pst_Folder_Name As String
    Dim MailboxName As String
    Dim subFolderName As String
    Dim xlSheet As Object
    Dim Reg As Object
    Dim sText As String
    Dim vText As Variant
    Dim M As Object
    Dim M1 As Object

    MailboxName = "[email protected]"

    Pst_Folder_Name = "My Folders"

    subFolderName = "HTD Ticketing System"

    Set folder = Outlook.Session.folders(MailboxName).folders(Pst_Folder_Name).folders(subFolderName)
    If folder = "" Then
        MsgBox "Invalid Data in Input"
        GoTo end_lbl1:
    End If

    Sheets(1).Activate

    For iRow = 1 To folder.Items.Count
        Sheets(1).Cells(iRow, 1).Select

        sText = folder.Items.Item(iRow).Body

        Set Reg = CreateObject("VBScript.RegExp")

        With Reg
            .Pattern = "(http.)"
        End With

        If Reg.Test(sText) Then
            Set M1 = Reg.Execute(sText)
            For Each M In M1
                vText = Trim(M.SubMatches(1))
            Next
        End If

        Sheets(1).Cells(iRow, 2) = vText

        Set M = Nothing
        Set M1 = Nothing
        Set Reg = Nothing
    Next iRow

    end_lbl1:

End Sub

Thanks

Niisa

Upvotes: 1

Views: 308

Answers (1)

Alex K.
Alex K.

Reputation: 175816

The SubMatches collection is zero indexed so simply change to:

vText = Trim(M.SubMatches(0))

Currently you will only get the first match, if you want them all you need Reg.Global = True.

Upvotes: 3

Related Questions