LOPD Outlook Discovery Download Script

1 README.txt

Instructions for running code from Microsoft Outlook
	Enable the developer tab in the Outlook ribbon
		File -> Options -> Customize Ribbon -> Developer -> Add
	Enable macros
		Developer tab -> Macro Security -> Macro Settings -> Enable all macros
	Open the developer editor
		Developer tab -> Visual Basic
	Start/open the macro file
		Project1 -> Microsoft Outlook Objects -> ThisOutlookSession
	Copy and paste the code from github
	Open the output to see logs
		View -> Imeediate Window
	Run the script (the green play button)

Examples given have been anonymized. Next task will be to use the emailDetails.txt file to download the given filenames to the given directories, because outlook and VBA give an error message trying to download such large files. 

If you end up inheriting this project, reach out to me and I would be happy to walk you through what I've done so far and what needs to happen next. Good luck! 
-Rushi Shah ([email protected])

2 VbaProject.OTM

Sub ReadEmails()
    Dim objNS As Outlook.NameSpace: Set objNS = GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    Dim Item As Object
    Dim strURL As String
    Dim clientDetails As String
    
    Dim RegLink As RegExp: Set RegLink = New RegExp
    With RegLink
        .Pattern = "\[Click here to download\] <(.*)>"
        .Global = True
        .IgnoreCase = False
    End With
    
    Dim RegClientDetails As RegExp: Set RegClientDetails = New RegExp
    With RegClientDetails
        .Pattern = "This download may take a while depending on your internet connection\.((\n|.)*)Please note that your access to this link will expire on"
        .Global = True
        .IgnoreCase = False
        .MultiLine = True
    End With
    
    
    For Each Item In olFolder.Items
        If TypeOf Item Is Outlook.MailItem Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            If RegLink.Test(oMail.Body) Then
                Dim M1 As MatchCollection: Set M1 = RegLink.Execute(oMail.Body)
                Dim M2 As MatchCollection: Set M2 = RegClientDetails.Execute(oMail.Body)
                If M1.Count = 1 And M2.Count = 1 Then
                        clientDetails = Replace(M2.Item(0).SubMatches(0), vbCrLf, "")
                        strURL = M1.Item(0).SubMatches(0)
                        Debug.Print clientDetails
                        Debug.Print strURL
                        FollowLink strURL, clientDetails
                        Else
                            Debug.Print "Couldn't find either client details or download URL in email"
                End If
            End If
            Debug.Print "-----"
        End If
    Next
End Sub

Sub FollowLink(url, title)

    Dim IE As InternetExplorer
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate url
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE: Wend
    End With
    
    ' https://stackoverflow.com/a/64374969
    Do While (IE.document.getElementsByTagName("a").Length < 1)
        T0 = Now + TimeValue("00:00:01")
        Do Until Now > T0
          DoEvents
        Loop
        Debug.Print ("Waiting a second for the page to finish loading, couldn't find links")
    Loop
    
    Debug.Print ("Done loading")
    
    Set tags = IE.document.getElementsByTagName("a")
    Set ps = IE.document.getElementsByTagName("p")
    Debug.Print "Found " & tags.Length & " download links"
    Debug.Print "Found " & ps.Length & " paragraph tags"
    
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Dim folderPath As String
    ' Change this as needed
    folderPath = "C:\Users\felony.intern\Downloads\" & Trim(title)
    If Not oFSO.FolderExists(folderPath) Then
        MkDir folderPath
        Else
            Debug.Print "Folder path already exists"
    End If
    
    Dim ind As Integer
    ind = 1
    
    Open folderPath & "/emailDetails.txt" For Output As #1

    For Each tagx In tags
        Debug.Print tagx.href
        Debug.Print ps(ind).textContent
        Dim savePath As String
        savePath = folderPath & "/" & ps(ind).textContent
        Debug.Print savePath
        
        ' DownloadFile doesn't work for some reason on big files
        ' so instead just write the details of what is supposed to happen to a text file
        ' DownloadFile tagx.href, savePath
        Print #1, savePath
        Print #1, tagx.href
        Print #1, "----"
        
        ind = ind + 1
    Next
    
    Close #1
    IE.Quit
    Set IE = Nothing
End Sub

' https://www.excelvbasolutions.com/2014/09/download-file-from-url-using-vba.html
Sub DownloadFile(url, savePath)
    Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")
    objXmlHttpReq.Open "GET", url, False
    objXmlHttpReq.Send
    
    If objXmlHttpReq.Status = 200 Then
        Debug.Print "Got 200"
        Set objStream = CreateObject("ADODB.Stream")
        objStream.Open
        objStream.Type = 1
        objStream.Write objXmlHttpReq.responseBody
        objStream.SaveToFile savePath, 2
        objStream.Close
        Debug.Print "Finished saving at " & savePath
    Else
        Debug.Print objXmlHttpReq.Status
    End If
End Sub

3 InputEmailExample.html

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><div dir="ltr"><div class="gmail_quote"><div lang="EN-US" link="#2B1EB1" vlink="#2B1EB1"><div class="m_-5144707012015583962WordSection1"><div><div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif"> Evidence.com &lt;<a href="mailto:[email protected]" target="_blank">[email protected]</a>&gt;
<br>
<b>Sent:</b> Tuesday, June 22, 2021 5:34 PM<br>
<b>To:</b> 2nd Felony, Discovery &lt;<a href="mailto:[email protected]" target="_blank">[email protected]</a>&gt;<br>
<b>Subject:</b> Evidence.com - Evidence Download Link<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><u></u>&nbsp;<u></u></p>
<div align="center">
<table border="0" cellspacing="0" cellpadding="0" width="100%" style="width:100.0%;border-collapse:collapse">
<tbody>
<tr>
<td valign="top" style="padding:0in 0in 0in 0in">
<div align="center">
<table border="0" cellspacing="0" cellpadding="0" width="0" style="width:6.25in;background:#1d252d;border-collapse:collapse">
<tbody>
<tr>
<td width="16" rowspan="3" valign="top" style="width:12.0pt;padding:0in 0in 0in 0in">
<p class="MsoNormal">&nbsp;<u></u><u></u></p>
</td>
<td width="568" valign="top" style="width:426.0pt;padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:13.5pt"><span style="font-size:13.5pt">&nbsp;<u></u><u></u></span></p>
</div>
</td>
<td width="16" rowspan="3" valign="top" style="width:12.0pt;padding:0in 0in 0in 0in">
<p class="MsoNormal">&nbsp;<u></u><u></u></p>
</td>
</tr>
<tr>
<td width="568" valign="top" style="width:426.0pt;padding:0in 0in 0in 0in">
<table border="0" cellspacing="0" cellpadding="0" style="border-collapse:collapse">
<tbody>
<tr>
<td style="padding:0in 0in 0in 0in">
<p class="MsoNormal"><a href="https://my.EVIDENCE.com" target="_blank"><span style="font-family:&quot;Helvetica&quot;,sans-serif;color:gold;text-decoration:none"><img border="0" width="241" height="21" style="width:2.5083in;height:.2166in" id="m_-5144707012015583962_x0000_i1025" src="https://my.EVIDENCE.com/html/uix/images/email/AxonEvidence.png" alt="Axon Evidence.com"></span></a><u></u><u></u></p>
</td>
<td width="34" valign="top" style="width:25.5pt;padding:0in 0in 0in 0in">
<p class="MsoNormal">&nbsp;<u></u><u></u></p>
</td>
<td style="padding:0in 0in 0in 0in"></td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td width="568" valign="top" style="width:426.0pt;padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:13.5pt"><span style="font-size:13.5pt">&nbsp;<u></u><u></u></span></p>
</div>
</td>
</tr>
</tbody>
</table>
</div>
<p class="MsoNormal" align="center" style="text-align:center"><span style="display:none"><u></u>&nbsp;<u></u></span></p>
<div align="center">
<table border="0" cellspacing="0" cellpadding="0" width="0" style="width:6.25in;background:white;border-collapse:collapse" id="m_-5144707012015583962email_content">
<tbody>
<tr>
<td colspan="3" style="padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:18.0pt"><span style="font-size:18.0pt">&nbsp;<u></u><u></u></span></p>
</div>
</td>
</tr>
<tr>
<td width="36" valign="top" style="width:27.0pt;padding:0in 0in 0in 0in">
<p class="MsoNormal">&nbsp;<u></u><u></u></p>
</td>
<td width="528" valign="top" style="width:5.5in;padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:22.5pt"><span style="font-size:22.5pt">&nbsp;<u></u><u></u></span></p>
</div>
<p class="MsoNormal"><img border="0" id="m_-5144707012015583962_x0000_i1026" src="https://my.EVIDENCE.com/index.aspx?class=Partner&amp;proc=ViewLogo&amp;action=getpartnerlogo&amp;format=xml&amp;partner_id=6f15c8c4dd0f41689566fce84e31cf03"><u></u><u></u></p>
<p class="MsoNormal"><img border="0" width="60" height="37" style="width:.625in;height:.3833in" id="m_-5144707012015583962_x0000_i1027" src="https://my.EVIDENCE.com/html/uix/images/email/yellowBar.png"><u></u><u></u></p>
<p class="MsoNormal" style="line-height:25.5pt"><b><span style="font-size:22.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d;text-transform:uppercase"><a href="http://my.evidence.com" target="_blank">my.evidence.com</a>
<u></u><u></u></span></b></p>
<div>
<div>
<p class="MsoNormal" style="line-height:22.5pt"><span style="font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<p class="MsoNormal" style="line-height:15.0pt"><span style="font-size:8.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">Dear REDACTED (Badge ID: REDACTED),
<u></u><u></u></span></p>
<div>
<p class="MsoNormal" style="line-height:10.5pt"><span style="font-size:10.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<p class="MsoNormal" style="line-height:15.0pt"><b><span style="font-size:8.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">REDACTED</span></b><span style="font-size:8.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d"> (Badge ID:
<b>REDACTED</b>) from <b>2nd Judicial District (NM) Attorney's Office</b> (<a href="https://da2ndnm.evidence.com" target="_blank">https://da2ndnm.evidence.com</a>) has sent you a link to download evidence on Evidence.com. After clicking on the link below you will download
<b>2</b> zip or iso files containing the requested evidence. This download may take a while depending on your internet connection.<br>
<br>
DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER
<br>
<br>
<b>Please note that your access to this link will expire on March 17, 2024 17:20:21 (-06:00).</b><br>
Afterwards, you will need to contact the sender to request access.<br>
<br>
Download link:<br>
<a href="https://google.com/DOWNLOAD_PAGE" target="_blank">[Click
 here to download]</a><u></u><u></u></span></p>
<div>
<p class="MsoNormal" style="line-height:10.5pt"><span style="font-size:10.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:15.0pt"><span style="font-size:8.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">Sincerely,
<br>
The Axon Team<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:22.5pt"><span style="font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:15.0pt"><span style="font-size:15.0pt;font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:15.0pt;background:#e2e1e7"><span style="font-size:8.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:10.5pt"><span style="font-size:10.5pt;font-family:&quot;Open Sans&quot;;color:#1d252d">&nbsp;<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:10.5pt"><span style="font-size:7.5pt;font-family:&quot;Open Sans&quot;;color:#56555b">SECURITY NOTICE: Evidence.com will not send you emails that request your username, password, security questions, or any other sensitive information.
 If you receive an email requesting information, it is most likely an attempt to gain access to your account. Furthermore, please verify that any hyperlinks in the message above begin with the proper URL e.g. &quot;<a href="https://agencyname.evidence.com" target="_blank">https://agencyname.evidence.com</a>&quot;.
 Please forward emails that appear suspicious to <a href="mailto:[email protected]" target="_blank">
[email protected]</a>.<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal" style="line-height:6.0pt"><span style="font-size:6.0pt;font-family:&quot;Open Sans&quot;;color:#56555b">&nbsp;<u></u><u></u></span></p>
</div>
</div>
</td>
<td width="36" valign="top" style="width:27.0pt;padding:0in 0in 0in 0in">
<p class="MsoNormal">&nbsp;<u></u><u></u></p>
</td>
</tr>
<tr>
<td colspan="3" style="padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:22.5pt"><span style="font-size:22.5pt">&nbsp;<u></u><u></u></span></p>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</td>
</tr>
<tr>
<td style="padding:0in 0in 0in 0in">
<div>
<p class="MsoNormal" style="line-height:15.0pt">&nbsp;<u></u><u></u></p>
</div>
</td>
</tr>
</tbody>
</table>
</div>
<p class="MsoNormal"><u></u>&nbsp;<u></u></p>
</div>
</div>

</div></div>

4 OutputLogExample.txt

DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER
https://google.com/DOWNLOAD_PAGE
Done loading
Found 2 download links
Found 3 paragraph tags
Folder path already exists
https://google.com/DOWNLOAD_LINK_1
FILE_NAME_1
C:\Users\felony.intern\Downloads\DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER/FILE_NAME_1
https://google.com/DOWNLOAD_LINK_2
FILE_NAME_2
C:\Users\felony.intern\Downloads\DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER/FILE_NAME_2
-----

5 emailDetails.txt

C:\Users\felony.intern\Downloads\DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER/FILE_NAME_1
https://google.com/DOWNLOAD_LINK_1
----
C:\Users\felony.intern\Downloads\DEFENDANT_NAME, CASE_NUMBER,PD_NUMBER, APD_NUMBER,DA_NUMBER/FILE_NAME_2
https://google.com/DOWNLOAD_LINK_2
----