Outlook macro to collect/extract data from "Subject field" of numerous messages into a text file

Question

Our firewall users send messages requesting unblocking certain websites that they believe shouldn't be blocked. Their messages subject fields contain such websites urls. In fact, one url is sent per message. Due to the increase in number of users, hundreds or may be thousands of messages are expected to be received per day.

Is there an Outlook macro that will collect or extract such urls (from received messages subject fields) into one single text file without having to open any message?

Deeply appreciating any assistance with this matter.

Thanks in advance

Answer 1

Please write this code to your Outlook VBA module. Change some names of folders and destination file in some lines below. For other information see comments inside sub.

Sub Retrieve_http()

'our Outlook folder- deifinitions
    Dim myItem As MailItem
    Dim myFolder As Folder
    Dim myNamespace As NameSpace
    Set myNamespace = Application.GetNamespace("MAPI")
    'put your folders name here
    '1st one is store folder which should refer to firewall_support@iuass.org
    'second is possibly 'inbox folder'
    Set myFolder = myNamespace.folders("firewall_support@iuass.org").folders("inbox")

'destination file
    Dim resFile As String
        resFile = "c:\Users\Kazik\Desktop\httpRequest.txt"
    Dim ff As Byte
        ff = FreeFile()
    'creating or opening it- each time you run this macro we will append data
    'until deletion of either file or its content
    Open resFile For Append As #ff

    For Each myItem In myFolder.items
        If InStr(1, myItem.Subject, "http://") > 0 And _
            InStr(1, myItem.Subject, "classified under:") > 0 Then
                'write to file
                Write #ff, myItem.Subject

        End If
    Next
    Close #ff
End Sub

EDIT to include appropriate deletion process and reference of the code to the picture.

The following picture present Outlook window (Polish version) where: Business Mail is one of Top Folders (which refers to separate .pst file). 'Skrzynka odbiorcza' is just 'inbox'.

Code which searches for certain emails, retrieves subject of emails and deletes emails afterwards looks as follow:

Sub Retrieve_http()

'our Outlook folder- deifinitions
    Dim myItem As MailItem
    Dim myFolder As Folder
    Dim myNamespace As NameSpace
    Set myNamespace = Application.GetNamespace("MAPI")
    'put your folders name here
    Set myFolder = myNamespace.folders("Business Mail").folders("skrzynka odbiorcza")

'destination file
    Dim resFile As String
        resFile = "c:\Users\Kazik\Desktop\httpRequest.txt"
    Dim ff As Byte
        ff = FreeFile()
    'creating or opening it- each time you run this macro we will append data
    'until deletion of either file or its content
    Open resFile For Append As #ff
    Dim i!
    For i = myFolder.items.Count To 1 Step -1
        If InStr(1, myFolder.items(i).Subject, "http://") > 0 And _
            InStr(1, myFolder.items(i).Subject, "classified under") > 0 Then
                'write to file
                Write #ff, myFolder.items(i).Subject
                'delete item
                myFolder.items(i).Delete
        End If
    Next
    Close #ff
End Sub