使用VBA遍历阵列，将Outlook电子邮件从一个文件夹移动到另一个文件夹? [英] Looping through Arrays with VBA, to Move outlook emails from one folder to another?

问题描述

我想将发票电子邮件从主文件夹移动到另一个文件夹.

I want to move emails of invoices from a main folder to a different folder.

在第一个模块中，我从Outlook中的Outlook中提取了带有VBA的电子邮件的主题，它们位于第3列中.然后，在第8列中，我手动写出了我希望电子邮件移动到的文件夹.文件夹是一个子文件夹)

I extracted the subject of the emails with VBA from outlook in the first module, they are in column 3. Then I manually write out the folder I would like the emails to move to, in column 8. (The names of the folder is a subfolder)

第3列是我提取的电子邮件的主题，我对Outlook使用 restrict 方法来返回带有特定标题的电子邮件

Column 3 is the subject of the email which I extracted, I used the restrict method for outlook to return the email with the specific tittle

第8列也是我也希望移动电子邮件的文件夹.

Column 8 is the folder I would like the email to move too.

示例如下代码必须将电子邮件放在主题为"A"的主文件夹中，文件夹为"1"

Example is like below The code has to place email in the main folder with subject'A' to Folder '1'

Column 3      columnn 8

A                 1
B                 2
C                 2
D                 1
E                 1

我使用数组的原因是因为每次提取数据时，列表都会更改，因此它是动态的.因此，我使用LBound和UBound来包括发票的整个列表.

The reason I use arrays is because, every time I make an extract, the list changes, hence it is dynamic. Therefore, I used LBound and UBound to include the whole list of invoices.

我已经在第一个模块中将所有变量声明为"public".只在这里把相关的留给了代码

I have declared all variables here in the first module as 'public'. Only left the relevant ones here to the code

Sub MovingEmails_Invoices()

  'Declare your Variables
    Dim i As Object
    Dim myitems As Object
    Dim subfolder As Outlook.Folder 


    'Set Outlook Inbox Reference
    Set OP = New Outlook.Application
    Set NS = OP.GetNamespace("MAPI")

    'To loop through subfolder and its folders
    Set rootfol = NS.Folders("[e-mail]")
    Set Folder = rootfol.Folders("Austria")

'The list for invoice numbers and folders is dynamic
'Each subject being searched is different

Dim Listmails() As Variant
Dim Rowcount As Long
Dim Mailsubject As Variant
Dim FolderName As Variant
Dim MS As String
Dim myrestrictitem As Outlook.items

 'Establish the array based on the mailbox extract
  Sheets("files").Activate
  Listmails = Range("A2").CurrentRegion


'Ititerate through the array which is dynamic (One-dimensional)
For Rowcount = LBound(Listmails) To UBound(Listmails)

'3rd row for email subject 'used DASL Filter
Mailsubject = Application.WorksheetFunction.Index(Listmails, Rowcount, 3)
MS = "urn:schemas:mailheader:subject LIKE \'%" & Mailsubject & "%\'"

    'Find the email based on the array for email subject
    Set myitems = Folder.items
    Set myrestrictitem = myitems.Restrict(MS)

        For each i in myrestrictitem
        If i.class = olmail then

         '8th row for folder name
         FolderName = Application.WorksheetFunction.Index(Listmails, Rowcount,8) 
         Set subfolder = rootfol.Folders(FolderName) ' i have an error here

         'If email found then mark it as read
         i.UnRead = False

         'Move it to the subfolder based on the array for folder name
         i.Move subfolder

Next Rowcount

End Sub

现在，我使用从Microsoft Office Center获得的示例来构造限制部分，这是此页面上的最后一个示例:

Now, I used the example I got from Microsoft Office Center to construct the restrict part, the last example on this page: https://docs.microsoft.com/en-us/office/vba/api/outlook.items.restrict

当我尝试以同样的方式进行操作时，它对我的​​代码不起作用.

when I try to do the same way, it doesn't work for my code.

错误消息来自；

Set myrestrictitem = myitems.Restrict(MS)

和?

Set subfolder = rootfol.Folders(FolderName)

错误消息是条件不正确.也可能是因为我没有正确执行循环.

The error message is the condition is not correct. Also it could be because I am doing the loop incorrectly.

是否有另一种方式可以做到这一点，也许就不用数组了吗?我需要中频条件吗?

Could there be another way of doing this, without arrays maybe? do i need IF condition?

推荐答案

您的条件必须包含 @SQL = 前缀.用双引号括起DASL属性名也是一个好主意:

You condition must include the @SQL= prefix. It is also a good idea to double quote the DASL property names:

@SQL="urn:schemas:mailheader:subject" LIKE '%test%'

在更改集合(通过调用Move)时，也不应使用" for each ".使用下循环:

You also should not use "for each" when you are changing the collection (by calling Move). Use a down loop:

for i = myrestrictitem.Count to 1 step -1
  set item =  myrestrictitem.Item(i)
  ..
  item.Move subfolder

这篇关于使用VBA遍历阵列，将Outlook电子邮件从一个文件夹移动到另一个文件夹?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！