CODEWITHSUNDEEP
vbaexceloutlook
user4581436
user4581436

Reputation: 57

VBA Error: Expected End Sub. Outlook Combing Public Sub and Sub

In the VBA (Macro) below I attempted to combine two macros. When the code runs (triggered by receiving an email) I get a expected End Sub error at line 9.

The first macro(saveAttachtoAccess) saves a file that is attached to an email and is triggered by a rule in Outlook. The second Macro (run_Excel_Macro) would open an excel file, refresh two sheets, save, and then exit.

As stated at line 9 (Exit Sub) I get a Compile error: Expected End Sub. My problem is when End Sub is in line 9 it only runs 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

Upvotes: 0

Views: 310

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

You cannot combine procedures in any OOP language - that's not how they work.

You can Call another sub from a sub like so:

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

Note that the Call keyword is actually depreciated in VBA and isn't required, I have used it here for illustrative purposes.

Upvotes: 0

Kyle Mac
Kyle Mac

Reputation: 146

I think you just need to call the run_Excel_macro and then End your Public Sub instead of exiting.

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

Upvotes: 0

Related Questions