Candyman8019
Well-known Member
- Joined
- Dec 2, 2020
- Messages
- 985
- Office Version
- 365
- Platform
- Windows
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:
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:
VBA Code:
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