Malicious Office (OLE) / .XLS — malware analysis report

Static analysis result for SHA-256 981aef90d88abc75…

MALICIOUS

Office (OLE) / .XLS

2.76 MB Created: 2015-04-03 14:52:53 Authoring application: Microsoft Excel First seen: 2026-06-21
MD5: 64a82f615735c98c6316432b50948530 SHA-1: 909281db64cca80f36ebd531cd9eaf83c57fbe0a SHA-256: 981aef90d88abc7548fdf52ffa5c06b739dbd4e04599b293ab550b5b3745b272
698 Risk Score

Heuristics 18

  • MSCOMCTL.TreeView — CVE-2012-0158 high CVE likely CVE_2012_0158
    MSCOMCTL.TreeView — CVE-2012-0158
  • VBA macros detected medium 11 related findings OLE_VBA_MACROS
    Document contains VBA macro code
  • Potential Shell call in VBA critical OLE_VBA_SHELL
    Potential Shell call in VBA
    Matched line in script
        Shell Environ("windir") & "\explorer.exe """ & GetLocalPath(ActiveWorkbook.path, True) & "", vbNormalFocus
  • WScript.Shell usage critical OLE_VBA_WSCRIPT
    WScript.Shell usage
    Matched line in script
            strFolder = GetLocalPath(CreateObject("WScript.Shell").specialfolders("Desktop"), True)
  • LOLBin reference in VBA critical OLE_VBA_LOLBIN
    LOLBin reference in VBA
    Matched line in script
                r = ShellExecute(0, "open", "rundll32.exe", "url.dll,FileProtocolHandler " & strURL, 0, 1)
  • VBA stages a PowerShell/LOLBin download-and-run command critical OLE_VBA_BITSTRANSFER_DROPPER
    The macro assembles a download command using a PowerShell or LOLBin download primitive (Start-BitsTransfer, Invoke-WebRequest, Net.WebClient, bitsadmin, certutil, ...) that fetches a remote payload, then executes it -- writing it to a script file and running it, or launching it directly from an auto-exec handler. The keywords are commonly split with PowerShell backtick / cmd caret escapes to evade scanners; this detection de-escapes the source first. A high-confidence downloader/dropper, stronger than the individual Shell / download keywords on their own.
    Matched line in script
    Private Sub Workbook_Open()
  • VBA downloads and writes a file to disk critical OLE_VBA_HTTP_DROP_EXEC
    VBA reads an HTTP response body and writes it to disk (ADODB.Stream SaveToFile). Combined with the auto-exec/Shell paths this is a download-drop dropper even when the COM ProgIDs are built dynamically to evade keyword scanning.
    Matched line in script
            b = .responseBody
  • VBA email-worm self-replication (Outlook mass-mailer) critical OLE_VBA_EMAIL_WORM_SELF_REPLICATION
    VBA macro drives Outlook to mass-mail itself: it automates Outlook.Application, programmatically creates a mail item, and spreads by attaches a file to the outgoing message, sends the message programmatically. Harvesting recipients from the address book / inbox and auto-attaching the carrier to outgoing messages is the defining behavior of the Melissa / LoveLetter / W97M mass-mailer worm lineage — there is no benign document use, independent of any AV signature.
    Matched line in script
        Set OutMail = OutApp.CreateItem(0)
  • CreateObject call high OLE_VBA_CREATEOBJ
    CreateObject call
    Matched line in script
        Set regexObject = CreateObject("vbscript.regexp")
  • Payload URL assembled from a Chr()/Asc() string expression (5 URLs) high OLE_VBA_EXPR_DROPPER_URL
    A VBA macro builds its stage-2 download URL character by character from string literals concatenated with Chr()/Asc()/StrReverse() results — often nested (Chr(Asc(Chr(Asc("h")))) = "h") and split across the + and & operators, sometimes written out via Print #n, into a second-stage VBScript/PowerShell file. The URL is assembled at run time and never appears contiguously on disk, and there is no numeric array to brute-force, so a literal scan and the array recoverers both miss it. A bounded expression evaluator resolved it; surfaced as an IOC. Self-validating: only a valid host URL that is not already present verbatim in the macro is reported, so a benign macro cannot false-positive.
  • VBA p-code auto-exec with execution tokens high OLE_VBA_PCODE_AUTOEXEC_EXEC
    Compiled VBA/cache stream contains an auto-execution token together with shell/download/object-execution tokens. This catches p-code-only or source-extraction-failure macro documents where visible source is unavailable.
  • Workbook_Open macro low OLE_VBA_WBOPEN
    Workbook_Open macro
    Matched line in script
    Private Sub Workbook_Open()
  • Environ() call (env variable access) low OLE_VBA_ENVIRON
    Environ() call (env variable access)
    Matched line in script
        b = Environ("HOME")
  • Reference to ShellExecute API high SC_STR_SHELLEXEC
    Reference to ShellExecute API
  • Reference to Windows Script Host high SC_STR_WSCRIPT
    Reference to Windows Script Host
  • ASP webshell / backdoor source high WEBSHELL_ASP
    The file contains classic ASP webshell code — eval/Execute over Request input, or WScript.Shell.Run of request data — i.e. server-side remote-command-execution backdoor source.
  • NOP-equivalent sled detected medium SC_NOP_EQUIV_SLED
    Long run of 0x40 bytes
    Disassembly hidden — these bytes score as degenerate, not coherent x86 code (single mnemonic 'inc' is 65% of instructions — a sled or padding/filler run, not program logic).
  • Embedded URL info EMBEDDED_URL
    One or more URLs were extracted from the document. The URL itself is not a detection — see the per-URL labels for which channel (macro, JS, link annotation, document body, ...) reached each URL.
    URL https://nausp-wapp2188/Tools/CatReview/catreview2.aspx?id= Referenced by macro
    • https://www.symbility.net/ux/site/#/claims;quickSearch=Referenced by macro
    • https://github.com/VBA-tools/VBA-JSONReferenced by macro
    • http://www.vbaccelerator.com/home/VB/Code/Techniques/RunTime_Debug_Tracing/VB6_Tracer_Utility_zip_cStringBuilder_cls.aspReferenced by macro
    • https://github.com/VBA-tools/VBA-JSON/pull/82Referenced by macro
    • https://github.com/VBA-tools/VBA-UtcConverterReferenced by macro
    • https://www.symbility.net/ux/site/#/claims;quickSearch=0Referenced by macro
    • http://schemas.microsoft.com/office/2006/metadata/contentTypeReferenced by macro
    • http://schemas.microsoft.com/office/2006/metadata/properties/metaAttributesReferenced by macro
    • http://schemas.microsoft.com/office/2006/metadata/propertiesReferenced by macro
    • http://www.w3.org/2001/XMLSchemaReferenced by macro
    • http://schemas.microsoft.com/office/2006/documentManagement/typesReferenced by macro
    • http://schemas.microsoft.com/office/infopath/2007/PartnerControlsReferenced by macro
    • http://schemas.openxmlformats.org/package/2006/metadata/core-propertiesReferenced by macro
    • http://www.w3.org/2001/XMLSchema-instanceReferenced by macro
    • http://purl.org/dc/elements/1.1/Referenced by macro
    • http://purl.org/dc/terms/Referenced by macro
    • http://schemas.microsoft.com/internal/obdReferenced by macro
    • http://dublincore.org/schemas/xmls/qdc/2003/04/02/dc.xsdReferenced by macro
    • http://dublincore.org/schemas/xmls/qdc/2003/04/02/dcterms.xsdReferenced by macro
    • http://schemas.openxmlformats.org/officeDocument/2006/customXmlReferenced by macro
    • http://schemas.microsoft.com/sharepoint/v3/contenttype/formsReferenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/ClaimFolder/OpenClaim.aspx?UIC=M%3d1%26ClaimNum%3dReferenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/Claim/ClaimDetails.aspx?UIC=ClaimID%3dReferenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/Policy/PolicyDetails.aspx?UIC=ClaimID%3dReferenced by macro
    • https://chubbgroup-my.sharepoint.com/personal/john_kube_chubb_com/Documents/Desktop/Referenced by macro
    • https://naclaimsystem.aceins.com/PROD_AFS.Claims/Desktop/HomePage/HomePage.aspxReferenced by macro
    • https://claimscommunications.chubb.com/api/claim/Referenced by macro
    • http://www.opensource.org/licenses/mit-license.php)�Referenced by macro
    • http://code.google.com/p/vba-json/Referenced by macro
    • http://msdn.microsoft.com/en-us/library/windows/desktop/ms724421.aspxReferenced by macro
    • http://msdn.microsoft.com/en-us/library/windows/desktop/ms724949.aspxReferenced by macro
    • http://msdn.microsoft.com/en-us/library/windows/desktop/ms725485.aspxReferenced by macro
    • http://support.microsoft.com/kb/269370Referenced by macro
    • http://www.ietf.org/rfc/rfc4627.txtReferenced by macro
    • https://support.microsoft.com/en-us/kb/272138Referenced by macro
    • https://claimws.chubb.com/CHUBB.Claims/Desktop/FileNotes/FNViewAttachment.aspx?UIC=M%3d1%26FileNoteID%3d%26A%3d13%26ClaimID%3d%26AttachmentID%3dReferenced by macro
    • https://claimscommunications.chubb.com/api/claims/Referenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/Claim/ClaimDetails.aspx?UIC=ClaimID%3d%26CovMatchErrInd%3dFalse%26ReloadTree%3dFalse%26M%3d1%26RestateFinForCat%3dFalse%26_NewUOW%3dTrue%26ReloadMenu%3dFalse%26A%3d1Referenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/Policy/PolicyDetails.aspx?UIC=ClaimID%3d%26PolID%3dA8CC2EB01DB19854%26CovMatchErrInd%3dFalse%26LOB%3d%26M%3d1%26ClaimStatus%3dOP%26_NewUOW%3dTrue%26AdminClmPerInd%3dFalseReferenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/ClaimFolder/OpenClaim.aspx?UIC=M%3d1%26ClaimNum%3d%26A%3d3%26AuthError%3dFalse%26FromOutside%3dTrueReferenced by macro
    • https://claimws.chubb.com/Chubb.Claims/Desktop/ClaimFolder/OpenClaim.aspx?UIC=M%3d1%26ClaimNum%3d0%26A%3d3%26AuthError%3dFalse%26FromOutside%3dTrueReferenced by macro
    • http://www.opensource.org/licenses/mit-license.phpReferenced by macro

Extracted artifacts 1

Files carved from inside the sample during analysis.

FilenameKindSourceSize
macros.bas vba-macro oletools.olevba.extract_macros (decoded VBA source) 1068678 bytes
SHA-256: 784a2355d7420abaa2c125dfa59a5baaff6c7761490ac7af0e4e6cacd28354e0
Preview script
First 1,000 lines of the extracted script
Attribute VB_Name = "ThisWorkbook"
Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("SOL").Select
End Sub

Private Sub Workbook_Open()
    On Error Resume Next
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.CalculateFull
    Call ChkAutoSv  'Turn off AutoSave
    'Stop
    'Hide Templates if visible.
    ThisWorkbook.Worksheets("SBF").Visible = False
    ThisWorkbook.Worksheets("Template").Visible = False
    ThisWorkbook.Worksheets("ALETemplate").Visible = False
    ThisWorkbook.Worksheets("Features").Visible = False
    'Determine if first time opening new SOL
    If Worksheets("Features").Range("F30").Value = "New" Then
        Call CreateInitialSOL   'Forces file to save once opened as a XLS
        Worksheets("Features").Range("F30").Value = "Existing"
    End If
    Application.EnableEvents = True
End Sub
Sub EnableSOLEvents()
    Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    Call SaveAsPDF
    'MsgBox "Please use the Save As PDF Button to print the SOL."
End Sub

Attribute VB_Name = "Sheet1"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Private Sub Worksheet_Activate()
    Call UpdateSOLCover
    Call CycleDetailsColumnUpdates
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if Deductible was entered correctly.
    On Error GoTo ErrorOut
    With Target
        If Not Application.Intersect(.Cells, Me.Range("G11:G30")) Is Nothing Then
            'Worksheets("Features").Range("E34").Value = ""
            If Target.Value <> "" Then
                If UCase(Worksheets("SOL").Range("B" & Target.row).Value) Like "*" & "DEDUCTIBLE" & "*" Then
                    If Target.Value > 0 Then
                        MsgBox "Deductible should be entered as a negative number." & vbNewLine & vbNewLine & "Pleave review the deductible amount.", vbCritical
                        Worksheets("SOL").Range("G" & Target.row).Select
                    End If
                End If
            End If
            Cancel = True
        End If
    End With
    Exit Sub
ErrorOut:
    Cancel = True
End Sub



Attribute VB_Name = "Sheet2"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "SOL_HTML_Generation"
Option Explicit
'   +-------------------------------------------------------+
'   |           HTML Generation for File Notes:             |
'   +-------------------------------------------------------+

Function GetHTMLStyle()
    GetHTMLStyle = "<style type=""text/css"">.tg {width:100%;border-collapse:collapse;border-color:#aaa;border-spacing:0;border-style:solid;border-width:0px;border-collapse: collapse;}.tg td{background-color:#fff;border-color:#aaa;border-style:solid;border-width:1px;color:#333; font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:none;}.tg th{backgro" & _
        "und-color:#f38630;border-color:#aaa;border-style:solid;border-width:1px;color:#fff; font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:none;}.tg .tg-dvi6{background-color:#c5d9f1;border-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middle}.tg .tg-dvi7{background-color:#c5d9f1;bo" & _
        "rder-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middle}.tg .tg-g1mc{background-color:#e6b8b7;border-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middle}.tg .tg-fyds{background-color:#ffff00;border-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middle}.tg .tg-5t9y{backg" & _
        "round-color:#f6fabc;border-color:#000000;color:#000000;font-weight:bold;text-align:left;vertical-align:middle}.tg .tg-cqub{background-color:#c4d79b;border-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middle}.tg .tg-4bf4{background-color:#92d050;border-color:#000000;color:#000000;font-weight:bold;text-align:center;vertical-align:middl" & _
        "e}.tg .tg-epx5{background-color:#ebf1de;border-color:#000000;color:#000000;font-weight:bold;text-align:left;vertical-align:middle}.tg .tg-pnl2{border-color:#000000;color:#000000;text-align:left;vertical-align:middle}.tg .tg-1j9e{background-color:#cffbcd;border-color:#000000;color:#000000;font-weight:bold;text-align:left;vertical-align:middle}.tg .tg-p98z{background-" & _
        "color:#cffbcd;border-color:#000000;color:#000000;text-align:left;vertical-align:middle}.tg .tg-w22m{background-color:#ffffff;border-color:#000000;color:#000000;font-weight:bold;text-align:left;vertical-align:middle}.tg .tg-jpnr{background-color:#f2dcdb;border-color:#000000;color:#000000;font-weight:bold;text-align:left;vertical-align:middle}.tg .tg-8c31{background-c" & _
        "olor:#ffffff;border-color:#000000;color:#000000;text-align:left;vertical-align:middle}.tg .tg-space{text-align:left;vertical-align:top;border-style:none;border-width:0px;border-left: none}.tg .tg-detail{text-align:left;vertical-align:top;border-style:none;border-width:0px;font-weight:bold}.tg .tg-vendordesc{border-color:#000000;color:#000000;text-align:left;vertical" & _
        "-align:middle;word-break:break-word}.tg tr:hover td {background-color: #DCE6F1;font-weight:bold;}</style>"
End Function
Function GetCoverageLineDetail(strWorksheetName As String)
    Dim strHTML As String
    Dim strHTML2 As String
    Dim cell As Range
    Dim myRng As Range
    Dim DataFound As Integer
    DataFound = 0
    Dim strLastRow As Integer
    strLastRow = FindLastRow(strWorksheetName)
    Set myRng = ActiveWorkbook.Sheets(strWorksheetName).Range("A7:A" & strLastRow)
    If TypeName(myRng) <> "Range" Then Exit Function
    For Each cell In myRng.Rows
        'If cell.Value <> "" Or cell.Offset(0, 1).Value <> "" Or cell.Offset(0, 2).Value <> "" Or cell.Offset(0, 3).Value > 0 Or cell.Offset(0, 4).Value <> "" Then
        'If Cell.Value <> 0 Or Cell.Offset(0, 1).Value <> 0 Or Cell.Offset(0, 2).Value <> 0 Or Cell.Offset(0, 3).Value > 0 Or Cell.Offset(0, 4).Value <> "" Then
        If cell.Value <> 0 Or cell.Offset(0, 2).Value <> 0 Or cell.Offset(0, 3).Value <> 0 Or cell.Offset(0, 4).Value > 0 Or cell.Offset(0, 5).Value <> "" Then
            DataFound = DataFound + 1   'Mark indicator that data was located.
            strHTML = strHTML & "<tr><td class=""tg-pnl2"">" & (CleanCurrency(cell.Value)) & "</td>"
            strHTML = strHTML & "<td class=""tg-pnl2"">" & (CleanCurrency(cell.Offset(0, 2).Value)) & "</td>"
            strHTML = strHTML & "<td class=""tg-pnl2"">" & (CleanCurrency(cell.Offset(0, 3).Value)) & "</td>"
            strHTML = strHTML & "<td class=""tg-pnl2"">" & (CleanCurrency(cell.Offset(0, 4).Value)) & "</td>"
            strHTML = strHTML & "<td class=""tg-vendordesc"">" & Trim(cell.Offset(0, 5).Value) & "</td></tr>"
        End If
    Next cell
    'Determine if Details Sheet contains data. If yes then add initial Details, totals, and headers.
    If DataFound > 0 Then
        strHTML2 = "<tr><td class=""tg-detail"" colspan=""5"">" & strWorksheetName & ":</td></tr>"
        strHTML2 = strHTML2 & "<tr><th class=""tg-jpnr"">" & (CleanCurrency(Worksheets(strWorksheetName).Range("A5").Value)) & "</th>"
        strHTML2 = strHTML2 & "<th class=""tg-5t9y"">" & (CleanCurrency(Worksheets(strWorksheetName).Range("C5").Value)) & "</th>"
        strHTML2 = strHTML2 & "<th class=""tg-epx5"">" & (CleanCurrency(Worksheets(strWorksheetName).Range("D5").Value)) & "</th>"
        strHTML2 = strHTML2 & "<th class=""tg-1j9e"">" & (CleanCurrency(Worksheets(strWorksheetName).Range("E5").Value)) & "</th>"
        strHTML2 = strHTML2 & "<td class=""tg-pnl2""></td>" 'Blank
        strHTML2 = strHTML2 & "</tr><tr>"
        strHTML2 = strHTML2 & "<td class=""tg-g1mc"">" & Trim(Worksheets(strWorksheetName).Range("A6").Value) & "</td>"
        strHTML2 = strHTML2 & "<td class=""tg-fyds"">" & Trim(Worksheets(strWorksheetName).Range("C6").Value) & "</td>"
        strHTML2 = strHTML2 & "<td class=""tg-cqub"">" & Trim(Worksheets(strWorksheetName).Range("D6").Value) & "</td>"
        strHTML2 = strHTML2 & "<td class=""tg-4bf4"">" & Trim(Worksheets(strWorksheetName).Range("E6").Value) & "</td>"
        strHTML2 = strHTML2 & "<td class=""tg-dvi6"">" & Trim(Worksheets(strWorksheetName).Range("F6").Value) & "</td></tr>"
        strHTML = strHTML2 & strHTML & "<td class=""tg-space"" colspan=""5""></td>"
    End If
    GetCoverageLineDetail = strHTML
End Function

Function GetSOLSummary()
    'Keep - 1/12/2024
    Dim strSOLTable As String
    'Header - Row 1
    strSOLTable = "<table class=""tg""><thead> <tr> <td class=""tg-detail"" colspan=""5"">Statement of Loss:</td></tr>"
    strSOLTable = strSOLTable & "<tr>"
    If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-w22m"">Totals:</th>"
    If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-w22m"">" & (CleanCurrency(Worksheets("SOL").Range("D9").Text)) & "</th>"  'Reserves
    If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-jpnr"">" & (CleanCurrency(Worksheets("SOL").Range("E9").Text)) & "</th>"  'Replacement Cost
    If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-5t9y"">" & (CleanCurrency(Worksheets("SOL").Range("G9").Text)) & "</th>"  'Actual Cash Value
    If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-epx5"">" & (CleanCurrency(Worksheets("SOL").Range("H9").Text)) & "</th>"  'Paid Amount
    If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<th class=""tg-1j9e"">" & (CleanCurrency(Worksheets("SOL").Range("I9").Text)) & "</th>"  'Current Payment
    strSOLTable = strSOLTable & "</tr>"
    'Header - Row 2
    strSOLTable = strSOLTable & "<tr>"
    If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-dvi6"">Coverage Line:</td>"
    If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-dvi7"">" & Trim(Worksheets("SOL").Range("D10").Text) & "</td>"  'Reserves
    If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-g1mc"">" & Trim(Worksheets("SOL").Range("E10").Text) & "</td>" 'Replacement Cost
    If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-fyds"">" & Trim(Worksheets("SOL").Range("G10").Text) & "</td>" 'Actual Cash Value
    If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-cqub"">" & Trim(Worksheets("SOL").Range("H10").Text) & "</td>" 'Paid Amount
    If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-4bf4"">" & Trim(Worksheets("SOL").Range("I10").Text) & "</td>" 'Current Payment
    strSOLTable = strSOLTable & "</tr>"
    'Coverage lines & Amounts
    Dim c As Range
    For Each c In ActiveWorkbook.Sheets("SOL").Range("B11:B30")
        If Trim(c.Value) = "" Then
            Else
            strSOLTable = strSOLTable & "<tr>"
            If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-pnl2"">" & Trim((CleanCurrency(Worksheets("SOL").Range("B" & c.row).Text))) & "</td>" 'Coverage Line Name
            If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-pnl2"">" & (CleanCurrency(Worksheets("SOL").Range("D" & c.row).Text)) & "</td>"   'Reserves
            If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-pnl2"">" & (CleanCurrency(Worksheets("SOL").Range("E" & c.row).Text)) & "</td>"   'RCV
            If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-pnl2"">" & (CleanCurrency(Worksheets("SOL").Range("G" & c.row).Text)) & "</td>"   'ACV
            If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-pnl2"">" & (CleanCurrency(Worksheets("SOL").Range("H" & c.row).Value2)) & "</td>" 'Prior Payments
            If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<td class=""tg-p98z"">" & (CleanCurrency(Worksheets("SOL").Range("I" & c.row).Value2)) & "</td>" 'Current Payment
            strSOLTable = strSOLTable & "</tr>"
        End If
    Next c
    strSOLTable = strSOLTable & "</tbody></table><br>"
    GetSOLSummary = strSOLTable
End Function
Function GenerateSOLFileNote()
    'HTML Code for ClaimConnect
    Dim strSOLTable As String
    strSOLTable = strSOLTable & "<style type=""text/css"">"
    strSOLTable = strSOLTable & ".tftable {font-size:10px;color:#333333;width:100%;border-width: 1px;border-color: 333333;border-collapse: collapse;}"
    strSOLTable = strSOLTable & ".tftable th {font-size:10px;background-color:#cffbcd;border-width: 1px;padding: 8px;border-style: solid;border-color: 333333;text-align:center;}"
    strSOLTable = strSOLTable & ".tftable tr {background-color:#ffffff;}"
    strSOLTable = strSOLTable & ".tftable td {font-size:10px;border-width: 1px;padding: 8px;border-style: solid;border-color: 333333;}"
    strSOLTable = strSOLTable & ".tftable tr:hover {background-color:#DCE6F1;}"
    strSOLTable = strSOLTable & "</style>"
    strSOLTable = strSOLTable & "<table class=""tftable"" border=""1"">"
    'Header - Row 1
    strSOLTable = strSOLTable & "<tr>"
    'Columns: 2,4,5,7,8,9
    If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#FFFFFF;""><b>Total:</b></th>"                                                  'Coverage Line
    If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#FFFFFF;"">" & (CleanCurrency(Worksheets("SOL").Range("D9").Text)) & "</th>"    'Reserves
    If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#f2dcdb;"">" & (CleanCurrency(Worksheets("SOL").Range("E9").Text)) & "</th>"    'Replacement Cost
    If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#f6fabc;"">" & (CleanCurrency(Worksheets("SOL").Range("G9").Text)) & "</th>"    'ACV
    If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#ebf1de;"">" & (CleanCurrency(Worksheets("SOL").Range("H9").Text)) & "</th>"    'Paid
    If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<th>" & (CleanCurrency(Worksheets("SOL").Range("I9").Text)) & "</th>"                                        'Amount Due
    '---------------
    strSOLTable = strSOLTable & "</tr>"
    'Header - Row 1
    strSOLTable = strSOLTable & "<tr>"
    If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#c5d9f1;""><b>Coverage Line</b></th>"
    If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#c5d9f1;""><b>" & Worksheets("SOL").Range("D10").Text & "</b></th>"
    If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#f2dcdb;""><b>" & Worksheets("SOL").Range("E10").Text & "</b></th>"
    If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#f6fabc;""><b>" & Worksheets("SOL").Range("G10").Text & "</b></th>"
    If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<th style=""background-color:#ebf1de;""><b>" & Worksheets("SOL").Range("H10").Text & "</b></th>"
    If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<th><b>" & Worksheets("SOL").Range("I10").Text & "</b></th>"
    strSOLTable = strSOLTable & "</tr>"
    'Coverage lines & Amounts
    Dim c As Range
    For Each c In Range("B11:B30")
        If Trim(c.Value) = "" Then
            Else
            strSOLTable = strSOLTable & "<tr>"
            If Columns(2).Hidden = False Then strSOLTable = strSOLTable & "<td>" & (CleanCurrency(Worksheets("SOL").Range("B" & c.row).Text)) & "</td>"
            If Columns(4).Hidden = False Then strSOLTable = strSOLTable & "<td>" & (CleanCurrency(Worksheets("SOL").Range("D" & c.row).Text)) & "</td>"
            If Columns(5).Hidden = False Then strSOLTable = strSOLTable & "<td>" & (CleanCurrency(Worksheets("SOL").Range("E" & c.row).Text)) & "</td>"
            If Columns(7).Hidden = False Then strSOLTable = strSOLTable & "<td>" & (CleanCurrency(Worksheets("SOL").Range("G" & c.row).Text)) & "</td>"
            If Columns(8).Hidden = False Then strSOLTable = strSOLTable & "<td>" & (CleanCurrency(Worksheets("SOL").Range("H" & c.row).Text)) & "</td>"
            If Columns(9).Hidden = False Then strSOLTable = strSOLTable & "<th>" & (CleanCurrency(Worksheets("SOL").Range("I" & c.row).Text)) & "</th>"
            strSOLTable = strSOLTable & "</tr>"
        End If
    Next c
    strSOLTable = strSOLTable & "</tbody></table>"
    GenerateSOLFileNote = strSOLTable
End Function
Function RemoveHTML(Text As String) As String
    Dim regexObject As Object
    Set regexObject = CreateObject("vbscript.regexp")
    With regexObject
        .Pattern = "<!*[^<>]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With
    'RemoveHTML = regexObject.Replace(text, "")
    RemoveHTML = regexObject.Replace(Text, vbNewLine)
    'TextOutFileNote = RemoveHTML
End Function
Private Sub TestHTML()
    Dim strTest As String
    strTest = GetSOLSummary
    'MsgBox strTest
    'MsgBox RemoveHTML(GetSOLSummary)
    Clipboard RemoveHTML(GetSOLSummary)
End Sub

Attribute VB_Name = "frmSOLSummary"
Attribute VB_Base = "0{7635B9CB-D5B9-4B35-835B-167C0C072711}{1D60640F-3052-4CEA-91DD-534C157DFBB6}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = False
Option Explicit
Private Sub UserForm_Initialize()
    If DetermineClaimSystem = "ClaimVision" Then OptionButton1 = True
    If DetermineClaimSystem = "ClaimCare" Then OptionButton2 = True
    If DetermineClaimSystem = "ClaimConnect" Then OptionButton3 = True
    If OptionButton1 = True Then
        Frame6.Visible = True
        OptionButtonExcel.Value = True
        Frame5.Visible = False
        Else
        Frame6.Visible = False
        Frame5.Visible = True
    End If
    TextBoxClaimNumber = Trim(ActiveWorkbook.Sheets("SOL").Range("C6").Text)
    'CheckBox1.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("B10").text)
    CheckBox2.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("D10").Text)
    CheckBox3.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("E10").Text)
    CheckBox4.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("G10").Text)
    CheckBox5.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("H10").Text)
    CheckBox6.Caption = Trim(ActiveWorkbook.Sheets("SOL").Range("I10").Text)
    With cboNoteType
        .AddItem "Statement of Loss Summary"
        .AddItem "Reserve File Note"
        .AddItem "Payment File Note"
        .AddItem "Other"
        .Value = "Statement of Loss Summary"
    End With
    With cboAuthority
        .AddItem "Select"
        .AddItem "Yes"
        .AddItem "No"
        .Value = "Select"
    End With
    Call ClaimCategory
End Sub
Private Sub OptionButton1_Click()
    Call AuthorityRequest
    Call FileNoteTitle
    Call FileNoteTypeSelection
End Sub
Private Sub OptionButton2_Click()
    Call AuthorityRequest
    Call FileNoteTitle
    Call FileNoteTypeSelection
End Sub
Private Sub OptionButton3_Click()
    Call AuthorityRequest
    Call FileNoteTitle
    Call FileNoteTypeSelection
End Sub
Private Sub cboAuthority_Change()
    Call AuthorityRequest
    Call FileNoteTitle
    Call FileNoteTypeSelection
End Sub
Private Sub cboNoteType_Change()
    Call AuthorityRequest
    Call FileNoteTitle
    Call FileNoteTypeSelection
End Sub

Sub FileNoteTitle()
    If cboNoteType = "Statement of Loss Summary" Then TextBoxSubject = "Statement of Loss Summary"
    If cboNoteType = "Reserve File Note" Then
        If cboAuthority = "Yes" Then
            TextBoxSubject = "Reserve Authority Request"
            TextBoxFileNote = "Requesting reserve authority based on Statement of Loss as follows:"
            Else
            TextBoxSubject = "Reserve Summary"
            TextBoxFileNote = "Setting reserves based on Statement of Loss as follows:"
        End If
    End If
    If cboNoteType = "Payment File Note" Then
        If cboAuthority = "Yes" Then
            TextBoxSubject = "Payment Authority Request"
            TextBoxFileNote = "Requesting payment authority based on Statement of Loss as follows:"
            Else
            TextBoxSubject = "Payment Summary"
            TextBoxFileNote = "Issuing payment based on Statement of Loss as follows:"
        End If
    End If
    If cboNoteType = "Other" Then
        If cboAuthority = "Yes" Then
            TextBoxSubject = "Authority Request"
            TextBoxFileNote = ""
            Else
            TextBoxSubject = ""
            TextBoxFileNote = ""
        End If
    End If
End Sub
Private Sub cmdCancel_Click()
    Unload frmSOLSummary
End Sub
Private Sub cmdOK_Click()
    Dim strHTMLCode As String
    Dim strFileSubject As String
    Dim strCurrentSelection As String
    Dim StrAttachment As String
    strCurrentSelection = ActiveWorkbook.Sheets("SOL").Range("C10").Text
    'If CheckBox1 = False Then Worksheets("SOL").Range("B:B").EntireColumn.Hidden = True Else Worksheets("SOL").Range("B:B").EntireColumn.Hidden = False
    If CheckBox2 = False Then Worksheets("SOL").Range("C:C").EntireColumn.Hidden = True Else Worksheets("SOL").Range("D:D").EntireColumn.Hidden = False
    If CheckBox3 = False Then Worksheets("SOL").Range("D:D").EntireColumn.Hidden = True Else Worksheets("SOL").Range("E:E").EntireColumn.Hidden = False
    If CheckBox4 = False Then Worksheets("SOL").Range("E:E").EntireColumn.Hidden = True Else Worksheets("SOL").Range("G:G").EntireColumn.Hidden = False
    If CheckBox5 = False Then Worksheets("SOL").Range("F:F").EntireColumn.Hidden = True Else Worksheets("SOL").Range("H:H").EntireColumn.Hidden = False
    If CheckBox6 = False Then Worksheets("SOL").Range("G:G").EntireColumn.Hidden = True Else Worksheets("SOL").Range("I:I").EntireColumn.Hidden = False
    '============
    'FileNote HTML Body:
    Dim strClaimNumber As String, strCategory As String, strSubCategory As String, strFileNoteText As String, strFileNoteSubject As String, strSendToExaminer As String, strSendToSupervisor As String
    strClaimNumber = TextBoxClaimNumber
    strCategory = DetermineCategoryCode
    strSubCategory = DetermineSubCategoryCode
    strFileNoteSubject = TextBoxSubject
    If CheckBoxSendToClaimOwner = True Then strSendToExaminer = "1" Else CheckBoxSendToClaimOwner = "0"
    If CheckBoxSendToSupervisor = True Then strSendToSupervisor = "1" Else strSendToSupervisor = "0"
    'Body
    'strFileNoteText = TextBoxFileNote & vbNewLine & vbNewLine & GenerateSOLFileNote
    'strFileNoteText = TextBoxFileNote & vbNewLine & vbNewLine & GenerateSOLFileNote
    'strFileNoteText = "<pre style=""white-space: pre-line; word-break: break-word;"">" & "<font face=""verdana"">" & TextBoxFileNote & vbNewLine & "</font></pre>" & GenerateSOLFileNote & "<pre style=""white-space: pre-line; word-break: break-word;""><font face=""verdana"">" & vbNewLine & vbNewLine & "</font></pre>"
    strFileNoteText = "<pre style=""white-space: pre-line; word-break: break-word;"">" & "<font face=""verdana"">" & Trim(TextBoxFileNote) & vbNewLine & "</font></pre>" & GenerateSOLFileNote & "<pre style=""white-space: pre-line; word-break: break-word;""><font face=""verdana"">" & vbNewLine & vbNewLine & "</font></pre>"
    
    'FileNote Subject:
    strFileSubject = TextBoxSubject
    
    'ClaimVision
    If OptionButton1 = True Then
        'No longer used.
        Unload frmSOLSummary
        Exit Sub
    End If
    'ClaimCare
    If OptionButton2 = True Then
        'No longer used.
        Unload frmSOLSummary
        Exit Sub
    End If
    'ClaimConnect
    If OptionButton3 = True Then
        StrCCONErrorCount = 0
        AddClaimConnectFileNote strClaimNumber, strCategory, strSubCategory, strFileNoteText, strFileNoteSubject, strSendToSupervisor, strSendToExaminer
    End If
    'Unload frmSOLSummary
    Worksheets("SOL").Range("B:G").EntireColumn.Hidden = False
    'Confirmation
    Dim answer As Integer
    Dim answer2 As Integer
    answer = MsgBox("Was the file note successfully added to the claim?", vbQuestion + vbYesNo + vbDefaultButton1, "File Note Added To Claim?")
      If answer = vbYes Then
        Unload frmSOLSummary
        Exit Sub
      Else
        answer2 = MsgBox("Would you like to Retry or Cancel?", vbRetryCancel)
        If answer2 = vbRetry Then
          Call cmdOK_Click
        Else
          Exit Sub
        End If
      End If
End Sub
Sub ClaimCategory()
    cboCategory.Clear
    cboSubCategory.Visible = True
    LabelSubCategory.Visible = True
    'ClaimVision:
    If OptionButton1 = True Then
        With cboCategory
            .AddItem "Damages"
            .AddItem "Reserve"
            .AddItem "Payment"
            .AddItem "Mgmt Review"
        End With
        cboSubCategory.Clear
        cboSubCategory.Visible = False
        LabelSubCategory.Visible = False
    End If
    '=========================
    'ClaimCare:
    If OptionButton2 = True Then
        With cboCategory
            .AddItem "Damages"
            .AddItem "Evaluation"
            .AddItem "Payment"
            .AddItem "Reserve"
            .AddItem "Authority"
            .AddItem "Communications"
        End With
    End If
    '=========================
    'ClaimConnect:
    If OptionButton3 = True Then
        With cboCategory
            .AddItem "Damages"
            .AddItem "Authority"
            .AddItem "Payment"
            .AddItem "Reserve"
            .AddItem "Supervisor Review"
        End With
    End If
End Sub
Sub ClaimSubCategory()
    cboSubCategory.Clear
    'ClaimVision
    'SubCategory not in use. Hide from Selections.
    '=========================
    'ClaimCare
    If OptionButton2 = True Then
        With cboSubCategory
            If cboCategory = "Damages" Then
                .AddItem "Documentation"
                .Value = "Documentation"
            End If
            If cboCategory = "Evaluation" Then
                .AddItem "Evaluation"
                .Value = "Evaluation"
            End If
            If cboCategory = "Payment" Then
                .AddItem "Documentation"
                .Value = "Documentation"
            End If
            If cboCategory = "Reserve" Then
                .AddItem "Documentation"
                .Value = "Documentation"
            End If
            If cboCategory = "Authority" Then
                .AddItem "Request"
                .Value = "Request"
            End If
            If cboCategory = "Communications" Then
                .AddItem "Documentation"
                .Value = "Documentation"
            End If
        End With
    End If
    '=========================
    'ClaimConnect
    If OptionButton3 = True Then
        With cboSubCategory
            If cboCategory = "Damages" Then
                .AddItem "Documentation"
                .Value = "Documentation"
            End If
            If cboCategory = "Authority" Then
                .AddItem "File Review"
                .AddItem "Indemnity"
                .AddItem "Reserve"
                .AddItem "Other"
            End If
            If cboCategory = "Payment" Then
                .AddItem "File Review"
                .AddItem "Indemnity"
                .Value = "Indemnity"
            End If
            If cboCategory = "Reserve" Then
                .AddItem "Analysis"
                .AddItem "File Review"
                .AddItem "Indemnity Analysis"
                .Value = "Analysis"
            End If
            If cboCategory = "Supervisor Review" Then
                .AddItem "Other"
                .Value = "Other"
            End If
        End With
    End If
End Sub
Private Sub cboCategory_Change()
    Call ClaimSubCategory
End Sub
Sub AuthorityRequest()
    If cboNoteType = "Statement of Loss Summary" Then
        LabelRequestAuthority.Visible = False
        cboAuthority.Visible = False
        Else
        LabelRequestAuthority.Visible = True
        cboAuthority.Visible = True
    End If
    If LabelRequestAuthority.Visible = True Then
        If cboAuthority = "Yes" Then
            CheckBoxSendToSupervisor = True
            Else
            CheckBoxSendToSupervisor = False
        End If
        Else
        CheckBoxSendToSupervisor = False
    End If
End Sub
Sub FileNoteTypeSelection()
    If cboNoteType = "Statement of Loss Summary" Then
        'ClaimVision
        If OptionButton1 = True Then
            Call ClaimCategory
            cboCategory.Value = "Damages"
            Call ClaimSubCategory
        End If
        'ClaimCare
        If OptionButton2 = True Then
            Call ClaimCategory
            cboCategory.Value = "Damages"
            Call ClaimSubCategory
        End If
        'ClaimConnect
        If OptionButton3 = True Then
            Call ClaimCategory
            cboCategory.Value = "Damages"
            Call ClaimSubCategory
        End If
    End If
    '========================
    If cboNoteType = "Reserve File Note" Then
        'ClaimVision
        If OptionButton1 = True Then
            Call ClaimCategory
            cboCategory.Value = "Reserve"
            Call ClaimSubCategory
        End If
        'ClaimCare
        If OptionButton2 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Reserve"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "Request"
                Else
                cboSubCategory.Value = "Documentation"
            End If
        End If
        'ClaimConnect
        If OptionButton3 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Reserve"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "Reserve"
                Else
                cboSubCategory.Value = "Analysis"
            End If
        End If
    End If
    '========================
    If cboNoteType = "Payment File Note" Then
        'ClaimVision
        If OptionButton1 = True Then
            Call ClaimCategory
            cboCategory.Value = "Payment"
            Call ClaimSubCategory
        End If
        'ClaimCare
        If OptionButton2 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Payment"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "Request"
                Else
                cboSubCategory.Value = "Documentation"
            End If
        End If
        'ClaimConnect
        If OptionButton3 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Payment"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "Indemnity"
                Else
                cboSubCategory.Value = "Indemnity"
            End If
        End If
    End If
    '========================
    If cboNoteType = "Other" Then
        'ClaimVision
        If OptionButton1 = True Then
            Call ClaimCategory
            cboCategory.Value = "Damages"
            Call ClaimSubCategory
        End If
        'ClaimCare
        If OptionButton2 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Payment"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "Request"
                Else
                cboSubCategory.Value = "Documentation"
            End If
        End If
        'ClaimConnect
        If OptionButton3 = True Then
            Call ClaimCategory
            If cboAuthority = "Yes" Then
                cboCategory.Value = "Authority"
                Else
                cboCategory.Value = "Damages"
            End If
            Call ClaimSubCategory
            If cboAuthority = "Yes" Then
                cboSubCategory.Value = "File Review"
                Else
                cboSubCategory.Value = "Documentation"
            End If
        End If
    End If
End Sub
Function DetermineCategoryCode()
    Dim strCategory As String
    Dim strCategoryOut As String
    strCategory = cboCategory
    'ClaimVision
    If OptionButton1 = True Then
        If cboCategory = "Damages" Then strCategoryOut = "9"
        If cboCategory = "Reserve" Then strCategoryOut = "34"
        If cboCategory = "Payment" Then strCategoryOut = "28"
        If cboCategory = "Mgmt Review" Then strCategoryOut = "26"
    End If
    'ClaimCare
    If OptionButton2 = True Then
        strCategoryOut = cboCategory
    End If
    'ClaimConnect
    If OptionButton3 = True Then
    '9=Damages '35=Supervisor
        strCategoryOut = cboCategory
        If cboCategory = "Damages" Then strCategoryOut = "9"
        If cboCategory = "Authority" Then strCategoryOut = "3"
        If cboCategory = "Payment" Then strCategoryOut = "25"
        If cboCategory = "Reserve" Then strCategoryOut = "30"
        If cboCategory = "Supervisor Review" Then strCategoryOut = "35"
    End If

    DetermineCategoryCode = strCategoryOut
End Function
Function DetermineSubCategoryCode()
    Dim strSubCategory As String
    Dim strSubCategoryOut As String
    Dim strCategoryOut As String
    strSubCategory = cboSubCategory
    'ClaimVision
    If OptionButton1 = True Then
        strSubCategoryOut = ""
    End If
    'ClaimCare
    If OptionButton2 = True Then
        strSubCategoryOut = cboSubCategory
    End If
    'ClaimConnect
    If OptionButton3 = True Then
        strCategoryOut = cboCategory
        If cboCategory = "Damages" Then
            If cboSubCategory = "Documentation" Then strSubCategoryOut = "1"
        End If
        If cboCategory = "Authority" Then
            If cboSubCategory = "File Review" Then strSubCategoryOut = "3"
            If cboSubCategory = "Indemnity" Then strSubCategoryOut = "4"
            If cboSubCategory = "Other" Then strSubCategoryOut = "5"
            If cboSubCategory = "Reserve" Then strSubCategoryOut = "6"
        End If
        If cboCategory = "Payment" Then
            If cboSubCategory = "File Review" Then strSubCategoryOut = "3"
            If cboSubCategory = "Indemnity" Then strSubCategoryOut = "4"
        End If
        If cboCategory = "Reserve" Then
            If cboSubCategory = "Analysis" Then strSubCategoryOut = "1"
            If cboSubCategory = "File Review" Then strSubCategoryOut = "5"
            If cboSubCategory = "Indemnity Analysis" Then strSubCategoryOut = "6"
        End If
        If cboCategory = "Supervisor Review" Then
            If cboSubCategory = "Other" Then strSubCategoryOut = "1"
        End If
    End If
    DetermineSubCategoryCode = strSubCategoryOut
End Function





Attribute VB_Name = "frmImportClaimInfo"
Attribute VB_Base = "0{2E9666A6-E37C-4D60-B072-F30904851BFA}{08137150-CC95-4D98-9268-63744DA5BFE6}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = False
Option Explicit
Private Sub cmdImport_Click()
    TextBoxClaimNumber = Trim(TextBoxClaimNumber)
    LabelDataLoading.Visible = True
    cmdImport.Visible = False
    cmdCancel.Visible = False
    'Call ShowOrHideSubmission 'Uncomment for debug
    If IsNumeric(TextBoxClaimNumber) = True Then
        If Len(TextBoxClaimNumber) >= 11 Then
            Call GetClaimID 'Claim Number is Valid - Get Data
            Else
            If Left(TextBoxClaimNumber, 2) = "77" Then
                'ClaimCare
                SearchAPI TextBoxClaimNumber
                frmImportClaimInfo.Hide
                Else
                'ClaimConnect
                SearchAPI TextBoxClaimNumber
                frmImportClaimInfo.Hide
            End If
        End If
        Else
        SearchAPI TextBoxClaimNumber
        frmImportClaimInfo.Hide
    End If
End Sub
Function GetClaimInfo(strURL)
    On Error GoTo ErrorOut
    Dim XMLreq As Object
    Dim POSTdata As String
    Dim sResp As String
    Dim i As Integer
    Set XMLreq = CreateObject("MSXML2.XMLHTTP.6.0")
    With XMLreq
        '.Open "Post", strURL, False
        .Open "Get", strURL, False
        .send
    End With
    sResp = XMLreq.responseText
    Set XMLreq = Nothing
    GetClaimInfo = sResp
    Exit Function
ErrorOut:
    GetClaimInfo = "Error Downloading"
End Function
Sub ShowOrHideSubmission()
    If cmdImport.Visible = True Then
        LabelDataLoading.Visible = True
        cmdImport.Visible = False
        cmdCancel.Visible = False
    Else
        LabelDataLoading.Visible = False
        cmdImport.Visible = True
        cmdCancel.Visible = True
    End If
End Sub
Sub GetClaimID()
    'Get Claim ID Value based on Claim Number
    Dim strClaimNumber As String
    Dim strResult As String
    Dim strStartText As String
    Dim strEndText As String
    Dim strURL As String
    Dim strClaimID As String
    Dim strClaimDetailsURL As String
    Dim strPolicyDetailsURL As String
    strClaimNumber = TextBoxClaimNumber
    strURL = "https://claimws.chubb.com/Chubb.Claims/Desktop/ClaimFolder/OpenClaim.aspx?UIC=M%3d1%26ClaimNum%3d"
    strURL = strURL & strClaimNumber & "%26A%3d3%26AuthError%3dFalse%26FromOutside%3dTrue"
    strResult = GetClaimInfo(strURL)
    If (InStr(strResult, "Error Downloading")) <> 0 Then
        MsgBox "Unable to access ClaimVision."
        Call ShowOrHideSubmission
        Exit Sub
    End If
    If (InStr(strResult, "that was entered does not exist")) <> 0 Then
        MsgBox "Claim number is not valid."
        Call ShowOrHideSubmission
        Exit Sub
    End If
    If (InStr(strResult, "not authorized to view that claim")) <> 0 Then
        MsgBox "Authorization denied. You are not authorized to view that claim."
        Call ShowOrHideSubmission
        Exit Sub
    End If
    ' Get Claim ID
    strStartText = "<frame name='fraTree' src='../../"
    strEndText = "' marginwidth=0 marginheight=0 scrolling=no frameborder=0>"";"
    strClaimID = SuperMid(strResult, strStartText, strEndText)
    strClaimID = SuperMid(strClaimID, "ClaimID%3d", "%26M%3d1%26ClaimNum")
    TextBoxClaimID = strClaimID
    strClaimDetailsURL = "https://claimws.chubb.com/Chubb.Claims/Desktop/Claim/ClaimDetails.aspx?UIC=ClaimID%3d" & strClaimID & "%26CovMatchErrInd%3dFalse%26ReloadTree%3dFalse%26M%3d1%26RestateFinForCat%3dFalse%26_NewUOW%3dTrue%26ReloadMenu%3dFalse%26A%3d1"
    strPolicyDetailsURL = "https://claimws.chubb.com/Chubb.Claims/Desktop/Policy/PolicyDetails.aspx?UIC=ClaimID%3d" & strClaimID & "%26PolID%3dA8CC2EB01DB19854%26CovMatchErrInd%3dFalse%26LOB%3d%26M%3d1%26ClaimStatus%3dOP%26_NewUOW%3dTrue%26AdminClmPerInd%3dFalse"
    '-----
    ClaimDetails strClaimDetailsURL
    PolicyDetails strPolicyDetailsURL
    'Exit Sub   'Uncomment for Debug
    '=================
    '  Final Cleanup
    '=================
    Call CleanData
    

    
    TextBoxDOL.Text = Format(TextBoxDOL, "mm/dd/yyyy")
    TextBoxInspected.Text = Format(TextBoxInspected, "mm/dd/yyyy")
    TextBoxPolicyStart.Text = Format(TextBoxPolicyStart, "mm/dd/yyyy")
    TextBoxPolicyEnd.Text = Format(TextBoxPolicyEnd, "mm/dd/yyyy")
    TextBoxReported.Text = Format(TextBoxReported, "mm/dd/yyyy")
    If IsNumeric(TextBoxClaimNumber) = True And Len(TextBoxClaimNumber) = 11 Then   'Add leading Zero to claim number
        TextBoxClaimNumber = "0" & TextBoxClaimNumber
    End If
    CleanTextBox TextBoxNamedInsured
    CleanTextBox TextAgencyName
    CleanTextBox TextBoxCATCode
    CleanTextBox TextBoxLossDescription
    If Len(TextBoxLossZip) = 10 And Right(TextBoxLossZip, 4) = "0000" Then TextBoxLossZip = Left(TextBoxLossZip, 5)
    If Len(TextMailZip) = 10 And Right(TextMailZip, 4) = "0000" Then TextMailZip = Left(TextMailZip, 5)
    If Len(TextAgencyZip) = 10 And Right(TextAgencyZip, 4) = "0000" Then TextAgencyZip = Left(TextAgencyZip, 5)
    Call ShowOrHideSubmission
    Call UpdateSOLCoverPage
    Call GetLinePaymentsAndResereves
    MsgBox "Claim information has been imported from ClaimVision." & vbNewLine & vbNewLine & "Please review imported data for adjustments."
    'Unload Me
    frmImportClaimInfo.Hide
End Sub
Sub ClaimDetails(strURL As String)
    Dim strCVData As String
    Dim strResponse As String
    Dim strStartText As String
    Dim strEndText As String
    Dim strResult As String
    strCVData = GetClaimInfo(strURL)
    strResponse = strCVData
    '==================================
    'DateContacted
        strResponse = strCVData
        '"<input type=""hidden"" name=""txtNdteDateContacted"" value=""10/01/2022"">"
        strStartText = "<input type=""hidden"" name=""txtNdteDateContacted"" value="
        strEndText = """>"
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = """"
        strEndText = """>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxDateContacted = Trim(strResult)
    '==================================
    'DateInspected (If CAT Claim)
        strResponse = strCVData
        strStartText = "<input type=""hidden"" name=""txtNdteDateInspected"" value="
        strEndText = """>"
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = """"
        strEndText = """>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxInspected = Trim(strResult)
    '==================================
    'CAT Exposure (If CAT Claim)
        strResponse = strCVData
        strStartText = "<input type=""hidden"" name=""txtNcurExposure"" value="""
        strEndText = """ LabelId=""lblExposure"">"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxCATExposure = Trim(strResult)

    '==================================
    ' CAT Code Description
        strResponse = strCVData
        ' "<input type=""hidden"" name=""txtN___CGDescription"" value=""CAT 2261 - Hurricane Ian (formerly Temp CAT 22TA)"" LabelId=""lblDescription"">"
        strStartText = "<input type=""hidden"" name=""txtN___CGDescription"" value="""
        strEndText = """ LabelId=""lblDescription"">"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxCATCode = Trim(strResult)
    '==================================
    'Date of Loss
        strResponse = strCVData
        ' "<input type=""hidden"" name=""txtRdteDateOfLoss"" value=""9/28/2022"" LabelId=""lblDateOfLoss"">"
        strStartText = "<input type=""hidden"" name=""txtRdteDateOfLoss"" value="""
        strEndText = """ LabelId=""lblDateOfLoss"">"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxDOL = Trim(strResult)
    '==================================
    'Date Reported
        strResponse = strCVData
        ' "<input type=""hidden"" name=""txtRdteDateLossReported"" value=""10/01/2022"" LabelId=""lblDateReported"">"
        strStartText = "<input type=""hidden"" name=""txtRdteDateLossReported"" value="""
        strEndText = """ LabelId=""lblDateReported"">"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxReported = Trim(strResult)
    '==================================
    'Loss Description
        strResponse = strCVData
        ' "<Textarea wrap='VIRTUAL' READONLY NAME='txtR___LossDescription' id='txtR___LossDescription' LabelId='lblLossDescription' Rows='5' Cols='85' onkeypress=""return MaxLength(this,400)"" maxlength=""400"" valMaxLength=""400"" >Signature client - dock was destroyed</textarea>"
        strStartText = "<Textarea wrap='VIRTUAL' READONLY NAME='txtR___LossDescription' id='txtR___LossDescription' LabelId='lblLossDescription' Rows='5' Cols='85' onkeypress=""return MaxLength(this,400)"" maxlength=""400"" valMaxLength=""400"" "
        strEndText = "</textarea>"
        'strResult = SuperMid(strResponse, strStartText, strEndText, False)
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = ">"
        strEndText = "</textarea>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxLossDescription = Trim(strResult)
    '==================================
    ' Policy Number
        strResponse = strCVData
        ' "<INPUT TYPE='hidden' NAME='hidN___PolicyNumber' VALUE=""001109811008"">"
        strStartText = "<INPUT TYPE='hidden' NAME='hidN___PolicyNumber' VALUE="
        strEndText = """>"
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = """"
        strEndText = """>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxPolicyNumber = Trim(strResult)
    '==================================
    ' Effective Date
        strResponse = strCVData
        ' "<INPUT TYPE='hidden' NAME='hidN___PolEffDate' VALUE=""2/28/2022"">"
        strStartText = "<INPUT TYPE='hidden' NAME='hidN___PolEffDate' VALUE="
        strEndText = """>"
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = """"
        strEndText = """>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxPolicyStart = Trim(strResult)
    '==================================
    ' Expiration Date
        strResponse = strCVData
        ' "<INPUT TYPE='hidden' NAME='hidN___PolExpDate' VALUE=""2/28/2023"">"
        strStartText = "<INPUT TYPE='hidden' NAME='hidN___PolExpDate' VALUE="
        strEndText = """>"
        strResponse = SuperMid(strResponse, strStartText, strEndText, True)
        strStartText = """"
        strEndText = """>"
        strResult = SuperMid(strResponse, strStartText, strEndText, False)
        TextBoxPolicyEnd = Trim(strResult)
…

Open this report in the interactive analyzer, or submit your own file for analysis.