循环遍历Excel宏中的不同过滤器选项 [英] loop through different filter options in Excel Macro

问题描述

我只是想知道如何在excel宏中循环使用不同的选项，并执行相同的操作。

我的操作是将ID从excel导出到outlook通讯组列表。

我使用以下代码：

  Public Sub DistributionList（） 
 Dim objOutlook As New Outlook.Application 
 Dim objNameSpace As Outlook.Namespace 
 Dim objDistList As Outlook.DistListItem 
 Dim objMail As Outlook.MailItem 
 Dim objRecipients As Outlook。收件人
 
 
设置objNameSpace = objOutlook.GetNamespace（MAPI）
设置objDistList = objOutlook.CreateItem（olDistributionListItem）
设置objMail = objOutlook.CreateItem（olMailItem） 
设置objRecipients = objMail.Recipients 
 ActiveSheet.Range（$ A $ 1：$ C $ 10）。AutoFilter字段：= 3，Criteria1：= _ 
团队1
 objDistList.DLName =Team 1
 
对于i = 2到单元格（ActiveSheet.Rows.Count，1）.End（xlUp）.Row 
 objRecipients.Add（Range（ B& i）.Value）
 Next i 
 
 objDistList.AddMembers objRecipients 
 objDistList.Display 
 objRecipients.ResolveAll 
 
设置objOutlook = Nothing 
设置objNameSpace = Nothing 
设置obj DistList = Nothing 
设置objMail = Nothing 
设置objRecipients = Nothing 
 
 End Sub 
  

在上述代码中，这两行过滤了一个团队并导出到一个通讯组列表，

  ActiveSheet .Range（$ A $ 1：$ C $ 10）。AutoFilter Field：= 3，Criteria1：= _ 
Team 1
 objDistList.DLName =Team 1
  

我有三个团队，我想要三个通讯组列表。任何人都可以帮助我编辑这段代码，以便循环遍历文件并创建三个通讯组列表？

我是VBA的新手，任何帮助都不会感激。 / p>

谢谢

解决方案

  Sub DistributionList（）
 Dim objOutlook As New Outlook.Application 
 Dim objNameSpace As Outlook.Namespace 
 Dim objDistList As Outlook.DistListItem 
 Dim objMail As Outlook.MailItem 
 Dim objRecipients As Outlook.Recipients 
 Dim i As Long，j as Long，teamNames（）As String 
 
'''团队名称存储在数组'''''''''' 
 redim teamNames（1到3）
 teamNames（）= Split（Red，Green，Blue，，）
'''''''''''' '''''''''''''''''''''''''''
设置objNameSpace = objOutlook.GetNamespace（MAPI）
 
对于j = LBound（teamNames）到UBound（teamNames）
设置objDistList = objOutlook.CreateItem（olDistributionListItem）
设置objMail = objOutlook.CreateItem（olMailItem）
设置objRecipients = objMail.Recipients 
 
 ActiveSheet.Range（$ A $ 1：$ C $ 10）AutoFilter Field：= 3，Criteria1：= _ 
 teamNames（j）
 objDistList.DLName = teamNames（j）
 
对于i = 2 To Cells ActiveSheet.Rows.Count，1）.End（xlUp）.Row 
 objRecipients.Add（Range（B& i）.Value）
 Next i 
 
 objDistList.AddMembers objRecipients 
 objDistList.Display 
 objRecipients.ResolveAll 
设置objDistList = Nothing 
设置objMail = Nothing 
设置objRecipients =没有
下一个j 
 
设置objOutlook =没有
设置objNameSpace =没有
 
 
结束Sub 
  

您可以尝试以上我认为它应该工作，但没有尝试。您应该有一种方法从电子表格或用户输入的范围中选择通讯组列表名称，而不是从1-3 IMHO计数。这是由你决定的。

谢谢

I am just wondering how to loop through different options in excel macro and perform the same operation.

My operation is to export IDs from excel to outlook distribution list.

I used the following code :

Public Sub DistributionList()
Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objDistList As Outlook.DistListItem
Dim objMail As Outlook.MailItem
Dim objRecipients As Outlook.Recipients


Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objDistList = objOutlook.CreateItem(olDistributionListItem)
Set objMail = objOutlook.CreateItem(olMailItem)
Set objRecipients = objMail.Recipients
ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=3, Criteria1:= _
        "Team 1"
objDistList.DLName = "Team 1"

For i = 2 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
objRecipients.Add (Range("B" & i).Value)
Next i

objDistList.AddMembers objRecipients
objDistList.Display
objRecipients.ResolveAll

Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objDistList = Nothing
Set objMail = Nothing
Set objRecipients = Nothing

End Sub

In the above code these two lines filter one team and export to one distribution list,

ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=3, Criteria1:= _
        "Team 1"
objDistList.DLName = "Team 1"

I have three teams and I would want three distribution lists. Can anybody help me in editing this code so that it would loop through the file and create three distribution lists?

I am new to VBA and any help would be appreciated.

Thanks

解决方案

Public Sub DistributionList()
Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objDistList As Outlook.DistListItem
Dim objMail As Outlook.MailItem
Dim objRecipients As Outlook.Recipients
Dim i As Long, j as Long, teamNames() As String

'''The Team Names are Stored in array '''''''''
redim teamNames(1 to 3)
teamNames() = Split("Red,Green,Blue", ",")
'''''''''''''''''''''''''''''''''''''''''''''''
Set objNameSpace = objOutlook.GetNamespace("MAPI")

For j = LBound(teamNames) To UBound(teamNames) 
    Set objDistList = objOutlook.CreateItem(olDistributionListItem)
    Set objMail = objOutlook.CreateItem(olMailItem)
    Set objRecipients = objMail.Recipients

    ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=3, Criteria1:= _
    teamNames(j)
    objDistList.DLName = teamNames(j)

    For i = 2 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
        objRecipients.Add (Range("B" & i).Value)
    Next i

    objDistList.AddMembers objRecipients
    objDistList.Display
    objRecipients.ResolveAll
    Set objDistList = Nothing
    Set objMail = Nothing
    Set objRecipients = Nothing
next j

Set objOutlook = Nothing
Set objNameSpace = Nothing


End Sub

You can try out the above I think it should work but didn't try it out. You should have a way to select the Distribution list name from the range in the spreadsheet or through a userinput rather than just counting from 1-3 IMHO. It is up to you though.

Thanks

这篇关于循环遍历Excel宏中的不同过滤器选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！