Running Combined Modules from Macro

[znxm0i]

Hello all,

A fellow forum member recently compiled some VBA code for me to run from two separate modules and while the code does work I am not sure how to run code via an Access macro. If I try to call the code in the first module via "RUNCODE" it keeps telling me that I have the wrong number of arguments. In the 2nd module, I know I have to change "SUB" to "FUNCTION" in order for it to work. Can anyone please tell me how I would go about combining the two modules and getting them to run via a macro. Below is the code I currently have:

First module named RptExtras:

Function GetBoiler(ByVal sFile As String) As String
'**** Created by someone else
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Second module named RptAutomation:

Sub sendForApproval1()
'*************************************************
'VBA Code created by Trevor G May 2015
'Updated from previous version
'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)


SigString = "C:\Users\con04785\AppData\Roaming\Microsoft\S igna tures\Nina.txt"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
With olMail
.To = "nina@email.com"
.Subject = Format(Date, "dd-mmm-yyyy") & " Daily Report"
.Body = "Attached is the latest report update." & vbCr & vbCr & _
"Thank you." & vbCr & _
"" & vbCr & _
"Regards" & Signature

.Attachments.Add "C:\Users\con04785\Desktop\Daily Protocol Status.pdf"
.Display 'Change to Send once you are happy this is working for you.
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub

[June7]

The macro should call just the sendForApproval procedure. The sendForApproval procedure calls the GetBoiler procedure.

NOTE: deleted duplicate thread

[znxm0i]

June,

I am a novice Access user, thank you so very much for helping me with this. It works like a charm. Now next step is to figure out how to get it to run automatically via MS-Task Scheduler, any ideas?

[June7]

I use a VBScript that opens Access database, runs procedure, closes db.

Dim accessApp
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("path to db\db.accdb")
accessApp.Run "ConstructionExtract"
accessApp.Quit
set accessApp = nothing
End If

Task runs the VBScript file as scheduled.

[znxm0i]

Thank you June7 for providing this script. I was finally able to get MS-Task Scheduler to launch Access and open my database by using the following instructions found on this forum:

then in Task Scheduler:
In the Action section
Select the Action: Start a program
Under Settings
Program/script: "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
Add arguments: "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\Memberships.acc db"
Start in: C:\Program Files (x86)\Microsoft Office

However, if I attempt to add the switch "/x DailyProtocolReport" within the quotes immediately following the database name ending in .accdb Access does not work, all it does is just hangs. Is there a way for me to launch get my Access macro "DailyProtocolReport" to run once the Access database opens via Task Scheduler?

I tried earlier running VB script but I would have to provide written justification as to why I need this script to run on a company server. I am a Administrative Assistant who is simply trying to automate her reporting process so that a daily report is sent without my manual intervention. With your help and the help of others, I've come this far in getting the Access macro to work and Task Scheduler to run, I just need to know how to get everything to fire at once.

Any other suggestions

I use a VBScript that opens Access database, runs procedure, closes db.

Dim accessApp
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("path to db\db.accdb")
accessApp.Run "ConstructionExtract"
accessApp.Quit
set accessApp = nothing
End If

Task runs the VBScript file as scheduled.

[June7]

AFAIK, must be VBA procedure, not macro. I don't use macros.

I've never used a switch.

[znxm0i]

I finally got it to work. Had to place the switch outside the quotes after calling the database.