从Outlook下载附件并在Excel中打开 [英] Download attachment from Outlook and Open in Excel

查看：183 发布时间：2017/9/3 16:45:35 excel excel-vba outlook email-attachments

本文介绍了从Outlook下载附件并在Excel中打开的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试下载，然后使用Excel中的VBA在Outlook电子邮件中打开Excel电子表格附件。
我如何：

下载第一封电子邮件中唯一的附件（最新的电子邮件）在我的Outlook收件箱中

将保存在具有指定路径的文件中（例如：C：...）

使用以下的当前日期重新命名附件名称 + 以前的文件名

将电子邮件另存为其他标有C：...路径的文件夹

将Outlook中的电子邮件标记为已读

打开 Excel中的excel附件

我还希望能够将以下内容保存为分配给各个变量的单个字符串：

发件人电子邮件地址

收到日期

发送日期
主题

电子邮件的消息

虽然这可能会更好地在一个单独的问题/自己寻找。

代码我目前是从其他论坛在线，可能不是很有帮助。然而，这里有一些我已经在做的工作：

  Sub SaveAttachments（）
 Dim olFolder As Outlook .MAPIFolder 
 Dim att As Outlook.Attachment 
 Dim strFilePath As String 
 Dim fsSaveFolder As String 
 
 fsSaveFolder =C：\test\
 
 strFilePath =C：\temp\
 
设置olFolder = Application.GetNamespace（MAPI）GetDefaultFolder（olFolderInbox）
 
对于每个msg在olFolder.Items 
虽然msg.Attachments.Count> 0 
 bflag = False 
如果右$（msg.Attachments（1）.Filename，3）=msg然后
 bflag = True 
 msg.Attachments（1）。 SaveAsFile strFilePath& strTmpMsg 
 Set msg2 = Application.CreateItemFromTemplate（strFilePath& strTmpMsg）
 End If 
 sSavePathFS = fsSaveFolder& msg2.Attachments（1）.Filename 
 
 
如果
 End Sub

解决方案

我可以一次性给你完整的代码，但不能帮助你从中学习;）所以让我们分解你的请求，然后我们将解决他们1比1.这将是一个非常长的职位，所以要耐心:) $ /

共有5个部分将覆盖所有7（是7而不是6）点数，所以你不必为你的第七点创建一个新的问题。

PART - 1

创建与Outlook的连接

检查是否有未读邮件

检索细节，如发件人电子邮件地址，收到日期，发送日期，主题，电子邮件的消息

请参阅此代码示例。我从Excel中解析Outlook，然后检查是否有未读的项目，如果我正在检索相关的详细信息。

  Const olFolderInbox As Integer = 6 
 
 Sub ExtractFirstUnreadEmailDetails（）
 Dim oOlAp As Object，oOlns As Object，oOlInb As Object 
 Dim oOlItm As Object 
 
 ~~> Outlook变量为电子邮件
 Dim eSender As String，dtRecvd As String，dtSent As String 
 Dim sSubj As String，sMsg As String 
 
'~~>获取Outlook实例
设置oOlAp = GetObject（，Outlook.application）
设置oOlns = oOlAp.GetNamespace（MAPI）
设置oOlInb = oOlns.GetDefaultFolder（olFolderInbox）
 
'~~>检查是否有任何实际的未读电子邮件
如果oOlInb.Items.Restrict（[UnRead] = True）。Count = 0然后
 MsgBox否收件箱中的未读电子邮件
退出子
结束如果
 
'~~>将相关信息存储在变量
对于每个oOlItm在oOlInb.Items.Restrict（[UnRead] = True）
 eSender = oOlItm.SenderEmailAddress 
 dtRecvd = oOlItm.ReceivedTime 
 dtSent = oOlItm.CreationTime 
 sSubj = oOlItm.Subject 
 sMsg = oOlItm.Body 
退出
下一个
 
 Debug.Print eSender 
 Debug.Print dtRecvd 
 Debug.Print dtSent 
 Debug.Print sSubj 
 Debug.Print sMsg 
 End Sub

所以，请妥善保管您在变量中存储细节的请求。

PART - 2

现在转到您的下一个请求

从Outlook收件箱中的第一封电子邮件（最新电子邮件）中下载唯一的附件

将附件保存在具有指定路径的文件中（例如：C：。 ..）

使用：current date + prev重命名附件名称ious文件名

请参阅此代码示例。我再次使用Outlook从Outlook然后检查是否有未读的项目，如果有进一步的检查，如果它有一个附件，如果它已经下载到相关的文件夹。

  Const olFolderInbox As Integer = 6 
'~~>附件路径
 Const AttachmentPath As String =C：\
 
 Sub DownloadAttachmentFirstUnreadEmail（）
 Dim oOlAp As Object，oOlns As Object，oOlInb As Object 
 Dim oOlItm As Object，oOlAtch As Object 
 
'~~>附件的新文件名
 Dim NewFileName As String 
 NewFileName = AttachmentPath&格式（日期，DD-MM-YYYY）&  - 
 
'~~>获取Outlook实例
设置oOlAp = GetObject（，Outlook.application）
设置oOlns = oOlAp.GetNamespace（MAPI）
设置oOlInb = oOlns.GetDefaultFolder（olFolderInbox）
 
'~~>检查是否有任何实际的未读电子邮件
如果oOlInb.Items.Restrict（[UnRead] = True）。Count = 0然后
 MsgBox否收件箱中的未读电子邮件
退出子
结束如果
 
'~~>从第一个未读电子邮件中提取附件
对于每个oOlItm在oOlInb.Items.Restrict（[UnRead] = True）
'~~>检查邮件实际是否有附件
如果oOlItm.Attachments.Count<> 0然后
对于每个oOlAtch在oOlItm.Attachments 
'~~>下载附件
 oOlAtch.SaveAsFile NewFileName& oOlAtch.Filename 
退出
下一个
 Else 
 MsgBox第一个项目没有附件
结束如果
退出
 Next 
 End Sub

PART - 3

转到您的下一个请求

将电子邮件另存为不同的包含C：...路径的文件夹

请参阅此代码示例。这将电子邮件保存为C：\

  Const olFolderInbox As Integer = 6 
'~~>电子邮件的路径+文件名
 Const sEmail As String =C：\ExportedEmail.msg
 
 Sub SaveFirstUnreadEmail（）
 Dim oOlAp As Object，oOlns As Object ，oOlInb As Object 
 Dim oOlItm As Object，oOlAtch As Object 
 
'~~>获取Outlook实例
设置oOlAp = GetObject（，Outlook.application）
设置oOlns = oOlAp.GetNamespace（MAPI）
设置oOlInb = oOlns.GetDefaultFolder（olFolderInbox）
 
'~~>检查是否有任何实际的未读电子邮件
如果oOlInb.Items.Restrict（[UnRead] = True）。Count = 0然后
 MsgBox否收件箱中的未读电子邮件
退出子
结束如果
 
'~~>保存第一个未读电子邮件
对于每个oOlItm在oOlInb.Items.Restrict（[UnRead] = True）
 oOlItm.SaveAs sEmail，3 
退出
下一个
 End Sub

PART - 4

转到您的下一个请求

将Outlook中的电子邮件标记为读取 / li>

请参阅此代码示例。这将把邮件标记为读取。

  Const olFolderInbox As Integer = 6 
 
 Sub MarkAsUnread（）
 Dim oOlAp As Object，oOlns As Object，oOlInb As Object 
 Dim oOlItm As Object，oOlAtch As Object 
 
' ~~>获取Outlook实例
设置oOlAp = GetObject（，Outlook.application）
设置oOlns = oOlAp.GetNamespace（MAPI）
设置oOlInb = oOlns.GetDefaultFolder（olFolderInbox）
 
'~~>检查是否有任何实际的未读电子邮件
如果oOlInb.Items.Restrict（[UnRead] = True）。Count = 0然后
 MsgBox否收件箱中的未读电子邮件
退出子
结束如果
 
'~~>标记第一个未读电子邮件为读
对于每个oOlItm在oOlInb.Items.Restrict（[UnRead] = True）
 oOlItm.UnRead = False 
 DoEvents 
 oOlItm.Save 
退出
下一个
结束子

PART - 5

转到您的下一个请求

打开excel中的excel附件

一旦你下载了上面所示的文件/附件，然后在下面的代码中使用该路径打开文件。

  Sub OpenExcelFile（）
 Dim wb As Workbook 
 
' ~~> FilePath是我们之前下载的文件
设置wb = Workbooks.Open（FilePath）
 End Sub

I'm trying to download and then open an Excel spreadsheet attachment in an Outlook email using VBA in Excel. How can I:

Download the one and only attachment from the first email (the newest email) in my Outlook inbox
Save the attachment in a file with a specified path (eg: "C:...")
Rename the attachment name with the: current date + previous file name
Save the email into a different folder with a path like "C:..."
Mark the email in Outlook as "read"
Open the excel attachment in Excel

I also want to be able to save the following as individual strings assigned to individual variables:

Sender email Address
Date received
Date Sent
Subject
The message of the email

although this may be better to ask in a separate question / look for it myself.

The code I do have currently is from other forums online, and probably isn't very helpful. However, here are some bits and pieces I have been working on:

Sub SaveAttachments()
    Dim olFolder As Outlook.MAPIFolder
    Dim att As Outlook.Attachment
    Dim strFilePath As String
    Dim fsSaveFolder As String

    fsSaveFolder = "C:\test\"

    strFilePath = "C:\temp\"

    Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

    For Each msg In olFolder.Items
        While msg.Attachments.Count > 0
            bflag = False
            If Right$(msg.Attachments(1).Filename, 3) = "msg" Then
                bflag = True
                msg.Attachments(1).SaveAsFile strFilePath & strTmpMsg
                Set msg2 = Application.CreateItemFromTemplate(strFilePath & strTmpMsg)
            End If
            sSavePathFS = fsSaveFolder & msg2.Attachments(1).Filename


    End If
End Sub

解决方案

I can give you the complete code in one go but that wouldn't help you learn from it ;) So let's Break up your requests and then we will tackle them 1 by 1. This is gonna be a very long post so be patient :)

There are total 5 parts which will cover all 7 (yes 7 and not 6) points so you don't have to create a new question for your 7th point.

PART - 1

Creating a Connection to Outlook
Checking if there is any unread email
Retrieving details like Sender email Address, Date received, Date Sent, Subject, The message of the email

See this code example. I am latebinding with Outlook from Excel then checking if there are any unread items and if there are I am retrieving the relevant details.

Const olFolderInbox As Integer = 6

Sub ExtractFirstUnreadEmailDetails()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object

    '~~> Outlook Variables for email
    Dim eSender As String, dtRecvd As String, dtSent As String
    Dim sSubj As String, sMsg As String

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Store the relevant info in the variables
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        eSender = oOlItm.SenderEmailAddress
        dtRecvd = oOlItm.ReceivedTime
        dtSent = oOlItm.CreationTime
        sSubj = oOlItm.Subject
        sMsg = oOlItm.Body
        Exit For
    Next

    Debug.Print eSender
    Debug.Print dtRecvd
    Debug.Print dtSent
    Debug.Print sSubj
    Debug.Print sMsg
End Sub

So that take care of your request which talks about storing details in the variables.

PART - 2

Now moving on to your next request

Download the one and only attachment from the first email (the newest email) in my Outlook inbox
Save the attachment in a file with a specified path (eg: "C:...")
Rename the attachment name with the: current date + previous file name

See this code example. I am again latebinding with Outlook from Excel then checking if there are any unread items and if there are I am further checking if it has an attachment and if it has then download it to the relevant folder.

Const olFolderInbox As Integer = 6
'~~> Path for the attachment
Const AttachmentPath As String = "C:\"

Sub DownloadAttachmentFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> New File Name for the attachment
    Dim NewFileName As String
    NewFileName = AttachmentPath & Format(Date, "DD-MM-YYYY") & "-"

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Extract the attachment from the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        '~~> Check if the email actually has an attachment
        If oOlItm.Attachments.Count <> 0 Then
            For Each oOlAtch In oOlItm.Attachments
                '~~> Download the attachment
                oOlAtch.SaveAsFile NewFileName & oOlAtch.Filename
                Exit For
            Next
        Else
            MsgBox "The First item doesn't have an attachment"
        End If
        Exit For
    Next
 End Sub

PART - 3

Moving on to your next request

Save the email into a different folder with a path like "C:..."

See this code example. This save the email to say C:\

Const olFolderInbox As Integer = 6
'~~> Path + Filename of the email for saving
Const sEmail As String = "C:\ExportedEmail.msg"

Sub SaveFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Save the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.SaveAs sEmail, 3
        Exit For
    Next
End Sub

PART - 4

Moving on to your next request

Mark the email in Outlook as "read"

See this code example. This will mark the email as read.

Const olFolderInbox As Integer = 6

Sub MarkAsUnread()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Mark 1st unread email as read
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.UnRead = False
        DoEvents
        oOlItm.Save
        Exit For
    Next
 End Sub

PART - 5

Moving on to your next request

Open the excel attachment in excel

once you have downloaded the file/attachment as shown above then use that path in the below code to open the file.

Sub OpenExcelFile()
    Dim wb As Workbook

    '~~> FilePath is the file that we earlier downloaded
    Set wb = Workbooks.Open(FilePath)
End Sub

这篇关于从Outlook下载附件并在Excel中打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

从Outlook下载附件并在Excel中打开 [英] Download attachment from Outlook and Open in Excel

问题描述

PART - 1

PART - 2

PART - 3

PART - 4

PART - 5

PART - 1

PART - 2

PART - 3

PART - 4

PART - 5

相关文章

Office最新文章

热门教程

热门工具

登录关闭

从Outlook下载附件并在Excel中打开 [英] Download attachment from Outlook and Open in Excel

问题描述

PART - 1

PART - 2

PART - 3

PART - 4

PART - 5

PART - 1

PART - 2

PART - 3

PART - 4

PART - 5

相关文章

Office最新文章

热门教程

热门工具

登录 关闭

登录关闭