Using Outlook to Trigger Excel Macro

[Candyman8019]

I am using Outlook to schedule sending out reminder notices. A recurring Outlook meeting reminder triggers an Excel workbook to open and run the macro then saves and closes it.

This works great as long as the workbook is closed. I’m looking for some assistance and recommendation to go in one of multiple directions:

Option 1:

Have the outlook macro Ensure the specified workbook is closed before continuing. Ie if it is open, then close it with save before continuing on to the rest of the script.

Option 2:

If the workbook is already open, then just run the macro, save and close the workbook.

I have been unsuccessful in getting either of those options to work.

My outlook macro is currently as follows:

Private Sub SendEmailNotices()

Dim wb As Workbook

Dim strFilePath As String

Dim strFile As String



' Set the path to the workbook

strFilePath = "C:\Users\...\"



' Set the file name

strFile = "TheMacroWorkbook.xlsm"



‘Open workbook

Set wb = Workbooks.Open(strFilePath & strFile)



' Run macro script and close with save

wb.Application.Run (strFile!SendNotices")

wb.Application.DisplayAlerts = False

wb.Close savechanges:=True

End Sub

 

[DacEasy]

Hi CM, I would do Option 2, by adding On Error Resume Next above the Set wb line. This way if the wb is already open, it continues along without the error.

‘Open Workbook
On Error Resume Next
Set wb = Workbooks.Open(strFilePath & strFile)

 

[Candyman8019]

I did try that; however it doesn't work. I believe if I was only working in excel that would be fine, but this is Outlook calling the macro in Excel. The error I get is: 'TheMacroWorkbook.xlsm' is open in another application. Please close it and try again.

 

[GWteB]

This is as far as you can go:

    On Error Resume Next
    Set WB = Workbooks.Open(strFilePath & strFile)
    On Error GoTo 0
    
    If Not WB Is Nothing Then

        ' Run macro script and close with save

        WB.Application.Run "strFile!SendNotices"

        WB.Application.DisplayAlerts = False

        WB.Close savechanges:=True
    
    End If

What you're wanting is only possible on certain conditions, because the different processes on a system logically do not have access to each other's opened files. This would make a system particularly vulnerable and unreliable. Suppose that user 1 wants to close the workbook opened by user 2, where would that go if that would be possible?

 

[DacEasy]

Outlook VBA Check if File is Open/Not Open

So I have a bit of code to check whether or not a file is open or not and act accordingly: Sub Check() Dim Ret Ret = IsWorkBookOpen("C:\Users\sberger\Desktop\GPAU\Target.xlsx") If Ret = False Then Dim xlApp As Object Dim sourceWB As Object Set...

www.mrexcel.com

This thread should help you out, especially where it checks to see if the workbook is open.

For Each wbOpen In Workbooks
If wbOpen.FullName = File_Name Then Exit For
Next
If wbOpen Is Nothing Then Set wbOpen = xlApp.Workbooks.Open(File_Name)

 

[GWteB]

@DacEasy, that only would work within one and the same instance of Excel, not from Outlook VBA.
To be certain a workbook isn't opened by another process, the code below can be used.

Public Sub UsageExample()
    Dim Result  As Boolean
    Result = IsWorkbookOpen("C:\Users\Folder\Book.xlsm")
End Sub

Public Function IsWorkbookOpen(ByVal argFullFileName As String) As Boolean
    Dim FileID As Long, ErrNum As Long
    FileID = VBA.FreeFile
    On Error Resume Next
    Open argFullFileName For Input Lock Read As #FileID
    ErrNum = Err.Number
    Close FileID
    IsWorkbookOpen = CBool(ErrNum)
End Function

 

[Candyman8019]

So what I'm hearing is that if the excel workbook is already open, I can detect that with the code above, but there's nothing I can do about it, ie close it, since the process for that instance is not owned by Outlook.

It makes sense. Thanks for the input everyone.

 

[Candyman8019]

This is pretty good actually. It allows me to know if the file is open and handle it gracefully with a message to the user rather than just erring out. Thanks.

 

[GWteB]

You are welcome. Note that the code just does that, nothing else!
The code doesn't check if the file exists. So if this function returns a FALSE, then you're certain that the file exists and that it's not open in another process.
If this function returns TRUE it might be the file doesn't exist at all.