{"id":51740,"date":"2023-05-21T09:39:17","date_gmt":"2023-05-21T08:39:17","guid":{"rendered":"https:\/\/ekiwi-blog.de\/51740\/vba-save-e-mail-attachment\/"},"modified":"2023-05-21T09:39:17","modified_gmt":"2023-05-21T08:39:17","slug":"vba-save-e-mail-attachment","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/51740\/vba-save-e-mail-attachment\/","title":{"rendered":"VBA Save e-mail attachment"},"content":{"rendered":"<p><em>Outlook<\/em> is a very widespread programme, especially in the professional field, for managing and processing e-mails and of course much more, if you only think of the calendar and task management.<\/p>\n<p>A big advantage of Outlook over other e-mail clients is that it comes with <abbr title=\"Visual Basic for Applications\">VBA<\/abbr> (Visual Basic for Applications), which makes it possible to automate many additional tasks if the appropriate functions cannot already be done via Outlook rules and integrated functions.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of content<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ekiwi-blog.de\/en\/51740\/vba-save-e-mail-attachment\/#Save_email_attachment_automatically_with_VBA\" >Save email attachment automatically with VBA<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ekiwi-blog.de\/en\/51740\/vba-save-e-mail-attachment\/#VBA_code_to_save_Outlook_e-mail_attachment\" >VBA code to save Outlook e-mail attachment<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ekiwi-blog.de\/en\/51740\/vba-save-e-mail-attachment\/#Call_Outlook_macro_via_quick_access\" >Call Outlook macro via quick access<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"Save_email_attachment_automatically_with_VBA\"><\/span>Save email attachment automatically with VBA<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>One task that can save you a lot of clicks is saving email attachments. If you have typical recurring e-mails whose attachments always have to be saved under the same path, you have to click through to this path each time with the &#8220;<em>Save as<\/em>&#8221; dialogue.<\/p>\n<p>Using VBA, you can write a <a href=\"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/\" title=\"VBA macro: Automatically add a file attachment to an e-mail message\">macro to save the e-mail attachment<\/a>. You can also assign a <u>shortcut<\/u> to the macro or insert a button in the bar for quick access. This way, you only need one click or keystroke to save the e-mail attachment. Such a macro can also be combined with other macros so that, for example, the email attachment is printed by VBA at the same time.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"VBA_code_to_save_Outlook_e-mail_attachment\"><\/span>VBA code to save Outlook e-mail attachment<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The following VBA code can be used to save email attachments under a fixed folder path. Only the <a href=\"https:\/\/ekiwi-blog.de\/en\/49094\/print-outlook-e-mail-too-wide-contents-are-cut-off\/\" title=\"Print Outlook e-mail: too wide contents are cut off\">e-mail attachments of the selected mail are<\/a> saved.<\/p>\n<p>In Outlook, you can access the <em><u>Visual Basic Editor<\/u><\/em> by pressing <strong>Ctrl+F11<\/strong>. It is also possible via the <em>menu <\/em><strong>Developer Tools ? Visual Basic<\/strong>.<\/p>\n<pre><code>\r\nPublic Sub SaveAttachments()\r\n\r\n    Dim objOL As Outlook.Application\r\n    Dim objMsg As Outlook.MailItem\r\n    Dim objAttachments As Outlook.Attachments\r\n    Dim objSelection As Outlook.Selection\r\n    Dim i As Long\r\n    Dim lngCount As Long\r\n    Dim strFile As String\r\n    Dim strFolderpath As String\r\n    \r\n    Instantiate Outlook Application object\r\n    Set objOL = CreateObject(\"Outlook.Application\")\r\n    \r\n    ' Get the collection of selected objects (e-mails)\r\n    Set objSelection = objOL.ActiveExplorer.Selection\r\n    \r\n    ' Set the folder path where the e-mail attachment should be saved\r\n    strFolderpath = \"c:\\Users\\AnyUser\\Documents\\subfolder\\bills\\year_2020\\\"\r\n    \r\n    ' Check each selected object (e-mail) to see if it has an attachment. If attachment exists,\r\n    ' then save under the folder path.\r\n    For Each objMsg In objSelection\r\n    \r\n        ' Determine the attachments of the selected object (e-mail).\r\n        Set objAttachments = objMsg.Attachments\r\n        lngCount = objAttachments.Count\r\n    \r\n        If lngCount &gt; 0 Then\r\n    \r\n            ' We use a backwards counter here, but it should also work the other way round.\r\n    \r\n            For i = lngCount To 1 Step -1\r\n    \r\n                Save attachment before deleting from item.\r\n                Determine file name\r\n                strFile = objAttachments.Item(i).FileName\r\n    \r\n                ' Combine folder path with file name\r\n                strFile = strFolderpath &amp; strFile\r\n    \r\n                ' Save attachment as a file\r\n                objAttachments.Item(i).SaveAsFile strFile\r\n    \r\n            Next i\r\n    \r\n        End If\r\n    Next\r\n    \r\nExitSub:\r\n    Set objAttachments = Nothing\r\n    Set objMsg = Nothing\r\n    Set objSelection = Nothing\r\n    Set objOL = Nothing\r\nEnd Sub\r\n<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Call_Outlook_macro_via_quick_access\"><\/span>Call Outlook macro via quick access<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>You can now call or execute the macro, provided it is <em>Public<\/em>, via the <strong>Developer Tools ? Macro ? Macros dialogue<\/strong>. Or you can place a button in the quick access. Using the small arrow pointing downwards in the quick access, select <em>&#8220;More commands &#8230;&#8221;<\/em> or go to <strong>File ? Options ? Quick Access Toolbar<\/strong>.<\/p>\n<figure id=\"attachment_6531\" aria-describedby=\"caption-attachment-6531\" style=\"width: 419px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-weitere-button-einfuegen.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-6531 size-full\" title=\"Add More Commands to Quick Access\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-weitere-button-einfuegen.png\" alt=\"Screenshot Assign Outlook Quick Access Button to Run Macro\" width=\"419\" height=\"366\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-weitere-button-einfuegen.png 419w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-weitere-button-einfuegen-300x262.png 300w\" sizes=\"auto, (max-width: 419px) 100vw, 419px\" \/><\/a><figcaption id=\"caption-attachment-6531\" class=\"wp-caption-text\">Assign Outlook Quick Access Button to Run Macro<\/figcaption><\/figure>\n<p>In the dialogue window that then opens, limit the commands to macros and add the macro in question.<\/p>\n<figure id=\"attachment_6530\" aria-describedby=\"caption-attachment-6530\" style=\"width: 936px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-makro-einfuegen.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-6530 size-full\" title=\"Quick Access Macro\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-makro-einfuegen.png\" alt=\"Screenshot Outlook Options Toolbar for Quick Access\" width=\"936\" height=\"678\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-makro-einfuegen.png 936w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-makro-einfuegen-300x217.png 300w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2020\/03\/outlook-schnellzugriff-makro-einfuegen-768x556.png 768w\" sizes=\"auto, (max-width: 936px) 100vw, 936px\" \/><\/a><figcaption id=\"caption-attachment-6530\" class=\"wp-caption-text\">Select the macro and add it to the quick access via the Add button<\/figcaption><\/figure>\n<p>Using the &#8220;<em>Change<\/em>&#8221; <a href=\"https:\/\/ekiwi-blog.de\/en\/17232\/vba-button-change-font-size\/\" title=\"VBA: button change font size\">button<\/a>, you can then assign an <strong>icon<\/strong> or adjust the <strong>text<\/strong>.<\/p>","protected":false},"excerpt":{"rendered":"<p>Outlook is a very widespread programme, especially in the professional field, for managing and processing e-mails and of course much<\/p>\n","protected":false},"author":2,"featured_media":6532,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[3036,2252,2753,1857,1569,1861],"class_list":["post-51740","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-attachment-en","tag-code-en","tag-instruction","tag-microsoft-en-2","tag-outlook-en","tag-vba-en-2"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/51740","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=51740"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/51740\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/6532"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=51740"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=51740"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=51740"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}