{"id":5607,"date":"2015-05-19T22:25:19","date_gmt":"2015-05-19T13:25:19","guid":{"rendered":"http:\/\/www.ka-net.org\/blog\/?p=5607"},"modified":"2015-06-01T17:57:15","modified_gmt":"2015-06-01T08:57:15","slug":"office-365-api%e3%82%92vba%e3%81%8b%e3%82%89%e5%91%bc%e3%81%b3%e5%87%ba%e3%81%993","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=5607","title":{"rendered":"Office 365 API\u3092VBA\u304b\u3089\u547c\u3073\u51fa\u3059(3)"},"content":{"rendered":"

\u524d\u3005\u56de\u306e\u8a18\u4e8b<\/a>\u3067Office 365\u3068Azure AD\u306e\u7d10\u3065\u3051\u3092\u3001\u524d\u56de\u306e\u8a18\u4e8b<\/a>\u3067API\u3092\u547c\u3073\u51fa\u3059\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306e\u4e0b\u6e96\u5099\u3068\u3057\u3066\u3001\u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\u306e\u8a2d\u5b9a\u3068\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID\u306e\u53d6\u5f97\u3001\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306e\u6a29\u9650\u8a2d\u5b9a\u3092\u884c\u3044\u307e\u3057\u305f\u3002
\n\u4eca\u56de\u306f\u3044\u3088\u3044\u3088VBA\u30de\u30af\u30ed\u304b\u3089Office 365 API\u3092\u547c\u3073\u51fa\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

API\u3092\u547c\u3073\u51fa\u3059\u6d41\u308c<\/h4>\n

Office 365 API\u3092\u5229\u7528\u3059\u308b\u306b\u306f\u3001authorization code\u3092\u53d6\u5f97\u3057\u3066access token\u3092\u53d6\u5f97\u3057\u3066\u2026\u3068\u3044\u3063\u305f\u4f5c\u696d\u304c\u5fc5\u8981\u306b\u306a\u308a\u307e\u3059\u304c\u3001\u3069\u306e\u3088\u3046\u306a\u5de5\u7a0b\u3092\u7d4c\u308c\u3070\u826f\u3044\u306e\u304b\u306f\u300cOffice 365 app authentication concepts<\/a>\u300d\u306b\u3042\u308b\u8868\u3092\u898b\u308b\u3068\u5206\u304b\u308a\u3084\u3059\u3044\u3067\u3059\u3002<\/p>\n

\"Office_365_API_VBA_03_01\"<\/a><\/p>\n

\u5927\u96d1\u628a\u306b\u8a00\u3046\u3068\u3001<\/p>\n

    \n
  1. Azure AD\u306e\u30a8\u30f3\u30c9\u30dd\u30a4\u30f3\u30c8\u306b\u30a2\u30af\u30bb\u30b9\u3057\u307e\u3059\u3002<\/li>\n
  2. \u7121\u4e8b\u306b\u8a8d\u8a3c\u3067\u304d\u308b\u3068authorization code\u304c\u53d6\u5f97\u3067\u304d\u307e\u3059\u3002<\/li>\n
  3. \u53d6\u5f97\u3057\u305fauthorization code\u3092\u4f7f\u3063\u3066\u3001Azure AD\u306e\u30a8\u30f3\u30c9\u30dd\u30a4\u30f3\u30c8\u306bPOST\u3057\u307e\u3059\u3002<\/li>\n
  4. \u554f\u984c\u304c\u7121\u3051\u308c\u3070access token\u3084refresh token\u3092\u53d6\u5f97\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/li>\n
  5. access token\u3092\u4f7f\u3063\u3066API\u3092\u547c\u3073\u51fa\u3057\u307e\u3059\u3002<\/li>\n<\/ol>\n

    \u3053\u3093\u306a\u611f\u3058\u3067\u3059\u3002
    \n\u3088\u304f\u3042\u308bOAuth 2.0 \u8a8d\u8a3c\u306eWeb API\u3068\u5927\u4f53\u540c\u3058\u6d41\u308c\u3067\u3059\u306e\u3067\u3001Web API\u306e\u547c\u3073\u51fa\u3057\u306b\u6163\u308c\u3066\u3044\u308b\u4eba\u306b\u306f\u96e3\u3057\u304f\u306a\u3044\u3060\u308d\u3046\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

    VBA\u30de\u30af\u30ed\u306e\u30b3\u30fc\u30c9<\/h4>\n

    \u4e0a\u8a18\u306e\u6d41\u308c\u3092\u8e0f\u307e\u3048\u3066\u66f8\u3044\u305f\u30b3\u30fc\u30c9\u304c\u4e0b\u8a18\u306b\u306a\u308a\u307e\u3059\u3002
    \n\u300c    Outlook Mail REST API<\/a>\u300d\u3092\u4f7f\u3063\u3066\u30e6\u30fc\u30b6\u30fc\u306e\u30e1\u30fc\u30eb\u4e00\u89a7\u3092\u53d6\u5f97\u3059\u308b\u30de\u30af\u30ed\u3067\u3059\u3002<\/p>\n

    \u5b9f\u884c\u3059\u308b\u969b\u306f\u300cclient_id<\/span>\u300d\u3068\u300credirect_uri<\/span>\u300d\u3092\u305d\u308c\u305e\u308c\u81ea\u5206\u304c\u53d6\u5f97\u30fb\u8a2d\u5b9a\u3057\u305f\u3082\u306e\u306b\u7f6e\u304d\u63db\u3048\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n

    \u307e\u305f\u3001\u30b3\u30fc\u30c9\u4e2dScriptControl<\/span>\u3092\u4f7f\u3063\u3066\u3044\u308b\u305f\u3081\u300164\u30d3\u30c3\u30c8\u7248\u306eOffice\u3067\u306f\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u307e\u3059\u3002<\/p>\n

    Option Explicit\r\n\r\nPublic Sub SampleOutlookAPI()\r\n'\u30b5\u30f3\u30d7\u30eb - Outlook Mail REST API\u547c\u3073\u51fa\u3057\r\n'\u203b ScriptControl\u3092\u4f7f\u3063\u3066\u3044\u308b\u305f\u3081\u300132\u30d3\u30c3\u30c8\u74b0\u5883\u306e\u307f\u5bfe\u5fdc\r\n'\u203b \u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\u3092localhost\u306b\u3057\u3066\u3044\u308b\u5834\u5408\u306f\u3001\u30ed\u30fc\u30ab\u30eb\u30b5\u30fc\u30d0\u30fc(XAMPP\u4ed6)\u306e\u8d77\u52d5\u304c\u5fc5\u8981\u306a\u5834\u5408\u304c\u3042\u308a\u307e\u3059\u3002\r\n  Dim url_auth As String\r\n  Dim url_token As String\r\n  Dim url_api As String\r\n  Dim q As String\r\n  Dim code As String\r\n  Dim js As String\r\n  Dim access_token As String\r\n  Dim dat As Variant\r\n  Dim ary As Variant, ary2 As Variant\r\n  Dim messages As Object\r\n  Dim message As Object\r\n  Dim i As Long\r\n  Dim value, Subject, BodyPreview 'JSON\u30d1\u30fc\u30b9\u7528\u30c0\u30df\u30fc\r\n  Const READYSTATE_COMPLETE = 4\r\n  \r\n  '**********************************************************************\r\n  '\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID & \u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI\r\n  Const client_id As String = "(\u53d6\u5f97\u3057\u305f\u30af\u30e9\u30a4\u30a2\u30f3\u30c8 ID)"\r\n  Const redirect_uri As String = "(\u8a2d\u5b9a\u3057\u305f\u30ea\u30c0\u30a4\u30ec\u30af\u30c8 URI)"\r\n  '**********************************************************************\r\n  \r\n  'authorization code\u53d6\u5f97\r\n  code = "" '\u521d\u671f\u5316\r\n  url_auth = "https:\/\/login.microsoftonline.com\/common\/oauth2\/authorize?response_type=code" & _\r\n             "&redirect_uri=" & EncodeURL(redirect_uri) & _\r\n             "&client_id=" & client_id & _\r\n             "&resource=" & EncodeURL("https:\/\/outlook.office365.com\/")\r\n  With CreateObject("InternetExplorer.Application")\r\n    .Visible = True\r\n    .Navigate url_auth\r\n    While .ReadyState <> READYSTATE_COMPLETE Or _\r\n          .Busy = True Or _\r\n          (StrComp(Left(.LocationURL, Len(redirect_uri)), redirect_uri) <> 0)\r\n      DoEvents\r\n    Wend\r\n    q = .document.parentWindow.Location.Search\r\n    q = Mid(q, 2) '"?"\u524a\u9664\r\n    ary = Split(q, "&")\r\n    For i = LBound(ary) To UBound(ary)\r\n      ary2 = Split(ary(i), "=")\r\n      If LCase(ary2(0)) = "code" Then\r\n        code = ary2(1)\r\n        Exit For\r\n      End If\r\n    Next\r\n    .Quit\r\n  End With\r\n  If Len(Trim(code)) < 1 Then Exit Sub\r\n  \r\n  'access token\u53d6\u5f97\r\n  js = "": access_token = "" '\u521d\u671f\u5316\r\n  url_token = "https:\/\/login.microsoftonline.com\/common\/oauth2\/token"\r\n  dat = "grant_type=authorization_code" & _\r\n        "&code=" & code & _\r\n        "&client_id=" & client_id & _\r\n        "&redirect_uri=" & EncodeURL(redirect_uri)\r\n  With CreateObject("MSXML2.XMLHTTP")\r\n    .Open "POST", url_token, False\r\n    .setRequestHeader "Content-Type", "application\/x-www-form-urlencoded"\r\n    .send dat\r\n    Select Case .Status\r\n      Case 200: js = .responseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  js = "(" & js & ")"\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    access_token = .CodeObject.eval(js).access_token\r\n  End With\r\n  If Len(Trim(access_token)) < 1 Then Exit Sub\r\n  \r\n  'Outlook Mail REST API\u547c\u3073\u51fa\u3057\r\n  js = "" '\u521d\u671f\u5316\r\n  url_api = "https:\/\/outlook.office365.com\/api\/v1.0\/me\/messages"\r\n  With CreateObject("MSXML2.XMLHTTP")\r\n    .Open "GET", url_api, False\r\n    .setRequestHeader "Authorization", "Bearer " & access_token\r\n    .send\r\n    Select Case .Status\r\n      Case 200: js = .responseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  js = "(" & js & ")"\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    Set messages = .CodeObject.eval(js).value\r\n    For Each message In messages\r\n      Debug.Print message.Subject, message.BodyPreview '\u4ef6\u540d\u30fb\u672c\u6587\u30d7\u30ec\u30d3\u30e5\u30fc\u5217\u6319\r\n    Next\r\n  End With\r\nEnd Sub\r\n\r\nPrivate Function EncodeURL(ByVal Target As String) As String\r\n'URL\u30a8\u30f3\u30b3\u30fc\u30c9\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    EncodeURL = .CodeObject.encodeURIComponent(Target)\r\n  End With\r\nEnd Function\r\n<\/pre>\n
    \u4e0a\u8a18\u30b3\u30fc\u30c9\u3092\u5b9f\u884c\u3059\u308b\u3068\u3001\u4e0b\u56f3\u306e\u3088\u3046\u306a\u30ed\u30b0\u30a4\u30f3\u753b\u9762\u304c\u8868\u793a\u3055\u308c\u3001<\/p>\n
    \"Office_365_API_VBA_03_02\"<\/a><\/p>\n
    \u8a8d\u8a3c\u306b\u6210\u529f\u3059\u308b\u3068authorization code\u3084access token\u306e\u53d6\u5f97\u304c\u884c\u308f\u308c\u3001API\u306e\u5b9f\u884c\u7d50\u679c\u3068\u3057\u3066\u30e6\u30fc\u30b6\u30fc\u304c\u53d7\u4fe1\u3057\u305f\u30e1\u30fc\u30eb\u306e\u4ef6\u540d\u3068\u672c\u6587\u306e\u4e00\u90e8\u304c\u30a4\u30df\u30c7\u30a3\u30a8\u30a4\u30c8 \u30a6\u30a3\u30f3\u30c9\u30a6\u306b\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n
    \"Office_365_API_VBA_03_03\"<\/a><\/p>\n
    API\u306e\u5b9f\u884c\u7d50\u679c\u306fJSON\u5f62\u5f0f\u3067\u8fd4\u3063\u3066\u304d\u307e\u3059\u304c\u3001\u3069\u306e\u3088\u3046\u306a\u69cb\u9020\u306b\u306a\u3063\u3066\u3044\u308b\u304b\u306f\u4e0b\u8a18Web\u30b5\u30a4\u30c8\u3092\u3054\u53c2\u7167\u304f\u3060\u3055\u3044\u3002<\/p>\n
    \u30fbOutlook Mail REST API reference<\/strong>
    \n    https:\/\/msdn.microsoft.com\/office\/office365\/APi\/mail-rest-operations#GetMessages<\/a><\/p>\n
    \u3055\u3066\u3001\u3053\u308c\u3067\u3088\u3046\u3084\u304fVBA\u304b\u3089Office 365 API\u3092\u547c\u3073\u51fa\u3059\u3053\u3068\u304c\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002
    \n\u3053\u308c\u307e\u30673\u56de\u306b\u6e21\u3063\u3066\u8a18\u4e8b\u3092\u66f8\u3044\u3066\u304d\u305f\u308f\u3051\u3067\u3059\u304c\u3001\u4eca\u56de\u306e\u8a18\u4e8b\u306b\u306f\u5143\u30cd\u30bf\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n
    \u305d\u308c\u306f\u3001Microsoft\u306e\u677e\u5d0e\u3055\u3093\u304c\u66f8\u3044\u305f\u30b5\u30f3\u30d7\u30eb\u30b3\u30fc\u30c9<\/a>\u3067\u3001\u201cOffice 365 API \u3092 Excel VBA \u304b\u3089\u4f7f\u3046\u201d<\/span>\u3068\u3044\u3046\u3082\u306e\u3067\u3059\u3002<\/p>\n
    \u4eca\u56de\u306e\u8a18\u4e8b\u306f\u3001\u3053\u306e\u5143\u30cd\u30bf\u3068\u3084\u3063\u3066\u3044\u308b\u3053\u3068\u306f\u307b\u307c\u540c\u3058\u3067\u3059\u304c\u3001\u81ea\u5206\u3067\u5b9f\u969b\u306b\u624b\u3092\u52d5\u304b\u3057\u3066\u307f\u308b\u3053\u3068\u3067\u3001365 API\u3078\u306e\u7406\u89e3\u306f\u6df1\u307e\u3063\u305f\u3088\u3046\u306b\u601d\u3044\u307e\u3059\u3002<\/p>\n
    \u7279\u306b\u3001SDK\u3082\u7528\u610f\u3055\u308c\u3066\u304a\u3089\u305a\u3001\u4f55\u3092\u3084\u308b\u306b\u3082\u4e00\u3005\u6a5f\u80fd\u3092\u5b9f\u88c5\u3057\u306a\u3044\u3068\u3044\u3051\u306a\u3044VBA\u3067\u30b3\u30fc\u30c9\u3092\u66f8\u304f\u3053\u3068\u306b\u3088\u3063\u3066\u3001API\u3092\u547c\u3073\u51fa\u3059\u306b\u306f\u3069\u3093\u306a\u6a5f\u80fd\u304c\u5fc5\u8981\u306a\u306e\u304b\u304c\u5206\u304b\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n
    JSON\u306e\u30d1\u30fc\u30b9\u7b49\u624b\u9593\u304c\u639b\u304b\u308b\u305f\u3081\u3001\u201cVBA\u304b\u3089\u306e365 API\u547c\u3073\u51fa\u3057\u306f\u4fbf\u5229\uff01\u201d<\/span>\u3068\u3001\u80f8\u3092\u5f35\u3063\u3066\u4eba\u306b\u85a6\u3081\u3089\u308c\u308b\u3082\u306e\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u304c\u3001\u958b\u767a\u74b0\u5883\u306f\u624b\u8efd\u306a\u3082\u306e\u306a\u306e\u3067\u3001\u8208\u5473\u304c\u3042\u308b\u65b9\u306f\u8a66\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
    \u53c2\u8003Web\u30b5\u30a4\u30c8<\/h4>\n