如何使用Excel VBA打开在特定时间范围内发送到特定Outlook文件夹的Outlook excel附件? [英] How to open an Outlook excel attachment using Excel VBA, sent in a particular time range to a specific Outlook folder?

问题描述

这是我的第一个问题.我想主修计算机科学，但与此同时，请使用VBA Excel寻求帮助.

This is my first ever question. I want to major in computer science but in the meantime, I kindly ask you for some assistance using VBA Excel.

我每天(工作日)收到具有相同文件名的Outlook电子邮件；我想对VBA Excel进行编程，以打开前一天晚上收到的Outlook附件.

I receive Outlook emails with the same filename daily (weekdays); I want to program VBA Excel to open the Outlook attachments that I receive during the night before.

所以，实际上有两件事:

So, really two things:

1. 对VBA Excel进行编程以从特定文件夹打开附件
2. 删除同一封电子邮件，以便第二天进行相同的处理(所有文件都使用相同的文件名发送，因此，我不会感到困惑或复制并粘贴错误的当天数据).

我已经编写了以下代码，但需要从Outlook中打开excel工作簿，而不是C驱动器:

I have written the below code but need to open excel workbook from Outlook, not C drive :

Sub Test()

    Dim WB As Workbook      'designate variables
    Dim sSaveFileName As String


    sSaveFileName = Format(Now() - 1, "MM-DD-YYYY")  'save as received date, 
    yesterday

    ' not needed >>>>> 'Set WB = 
    Workbooks.Open("C:\Users\nader\OneDrive\Documents\openthisexcel.xlsx")


    Windows("openthisexcel.xlsx").Activate  'activate opened excel attachment
    Sheets("Sheet1").Select
    Range("A1:A50").Select
    Selection.Copy                          'select range and copy

    Windows("macroExcel.xlsm").Activate    'activate xlsm template (another 
    workbook)
    Sheets("Sheet1").Select
    Range("A1:A50").Select
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False                          'select range 
    and paste from one excel to another

    WB.SaveAs Filename:= _
        "C:\Users\nader\OneDrive\Documents\openthisexcel.xlsx" & 
    sSaveFileName & ".xlsx"        'save received excel using filename and 
    the date it was received in Outlook


    WB.Close  'close the excel attachment


End Sub

请提供一些帮助，以从文件夹中打开Excel附件以及如何使用VBA在Outlook中删除电子邮件(包括Excel附件).

Please provide some assistance to open Excel attachment from folder and how to delete the email (including Excel attachment) in Outlook using VBA.

我阅读了一些Stack Overflow帖子，但听不懂(这里只是一个初学者).

I read some Stack Overflow posts , but couldn't understand them (just a beginner here).

推荐答案

查看

Look at Download attachment from Outlook and Open in Excel. This does what you want from Excel.

但是，您还有另一个选择，那就是编写一个Outlook宏.请参见按计划运行Outlook宏.这样，您将能够每天自动在Outlook中运行宏(只要打开Outlook)即可提取数据.宏的内容与第一个链接中的内容基本相同.

However, you have another option, that is, to write an Outlook macro instead. See Running Outlook Macros on a Schedule. With this, you would be able to run your macro automatically daily in Outlook (as long as Outlook is opened) to extract your data. The content of the macro would be essentially the same as the one in the first link.

这篇关于如何使用Excel VBA打开在特定时间范围内发送到特定Outlook文件夹的Outlook excel附件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！