Excel/Access VBA から Slack へメッセージを投稿する方法

ExcelやAccess のVBA を使ってSlackへ投稿するサンプルをメモ。
誰が、何をし、どうなったか？、特にエラー通知をいち早く知りたい時があったので先日実装してみました。

VBA から Slackへ投稿するサンプル

' Slackに投稿する主関数
'Private Function PostSlack(ByVal channel As String, ByVal webhookUrl As String, ByVal msgText As String)
    Dim sJson As String
    Dim httpReq As Object
    
    ' JSON
    '
    sJson = "{"
    sJson = sJson & "   ""channel"": """ & channel & ""","
    sJson = sJson & "   ""text"": """ & msgText & """"
    sJson = sJson & "}"
        
    Set httpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With httpReq
        .Option(4) = 13056 'WinHttpRequestOption_SslErrorIgnoreFlags
        .Open "POST", webhookUrl, False
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .Send (sJson)
        PostSlack = .responseText
    End With
    
    Set httpReq = Nothing

End Function

' 外部からサクセスできる関数
'
Public Sub SendSlack()
    Dim channel as String
    Dim webHookUrl as String
    Dim msgText as String
    Dim ret as Variant

    channel = "#driverlicence"
    webHookUrl = "https://hooks.slack.com/services/xxxxxxxxxxx"
    msgText = "テストだよ"

    ret = PostSlack(channel, webHookUrl , msgText)

    msgbox ret

End Sub