首页 文章

VBA错误：预期的结束子 . Outlook Combing Public Sub和Sub

提问于
浏览 次
0

在下面的VBA（宏）中，我尝试组合两个宏 . 当代码运行时（通过接收电子邮件触发）我在第9行得到预期的End Sub错误 .

第一个宏（saveAttachtoAccess）保存附加到电子邮件的文件，并由Outlook中的规则触发 . 第二个宏（run_Excel_Macro）将打开一个excel文件，刷新两张纸，保存，然后退出 .

如第9行（Exit Sub）所述，我得到一个编译错误：Expected End Sub . 我的问题是当End Sub在第9行时它只运行saveAttachtoAccess .

Public Sub saveAttachtoAccess(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "C:\Users\Josh\Documents\Source_Files"
     For Each objAtt In itm.Attachments
     objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
     Set objAtt = Nothing
     Next
     Exit Sub
Sub run_Excel_Macro()
    Dim App As Excel.Application
    Dim wkbk As Excel.Workbook

    Set App = New Excel.Application
    App.Visible = True
    Set wkbk = App.Workbooks.Open("C:\Users\Documents\C:\Users\Josh\Documents\Source_Files"JoshExcel.xlsm")
    App.OnTime DateAdd("s", 5, Now()), wkbk.Name &"!RefreshCombineSaveExit"

    Set App = Nothing
    Set wkbk = Nothing
End Sub
excel vba excel-vba outlook

2 回答

  • 0

    我想你只需要调用run_Excel_macro然后结束你的Public Sub而不是退出 .

    Public Sub saveAttachtoAccess(itm As Outlook.MailItem)

Dim objAtt As Outlook.Attachment
Dim saveFolder As String

saveFolder = "C:\Users\Josh\Documents\Source_Files"
   For Each objAtt In itm.Attachments
     objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
     Set objAtt = Nothing
   Next

   Call run_Excel_Macro 

End Sub

Sub run_Excel_Macro()
Dim App As Excel.Application
Dim wkbk As Excel.Workbook

Set App = New Excel.Application
App.Visible = True
Set wkbk = App.Workbooks.Open("C:\Users\Documents\C:\Users\Josh\Documents\Source_Files"JoshExcel.xlsm")
App.OnTime DateAdd("s", 5, Now()), wkbk.Name & "!RefreshCombineSaveExit"

Set App = Nothing
Set wkbk = Nothing
End Sub
    回复于
  • 0

    您无法在任何OOP语言中组合过程 - 这不是它们的工作方式 .

    你可以 Call 来自子的另一个子像这样：

    Public Sub saveAttachtoAccess(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "C:\Users\Josh\Documents\Source_Files"
     For Each objAtt In itm.Attachments
     objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
     Set objAtt = Nothing
     Next
Call run_Excel_Macro '// <-- Call other sub before ending this one.
End Sub

Sub run_Excel_Macro()
    Dim App As Excel.Application
    Dim wkbk As Excel.Workbook

    Set App = New Excel.Application
    App.Visible = True
    Set wkbk = App.Workbooks.Open("C:\Users\Documents\C:\Users\Josh\Documents\Source_Files"JoshExcel.xlsm")
    App.OnTime DateAdd("s", 5, Now()), wkbk.Name &"!RefreshCombineSaveExit"

    Set App = Nothing
    Set wkbk = Nothing
End Sub

    请注意， Call 关键字实际上是在VBA中折旧的，并不是必需的，我在此处将其用于说明目的 .

    回复于

相关问题