Autorun VBA code daily (1 Viewer)

[jdlewin1]

Hi All,

I have a VBA code which creates some excel sheets, saves them and then emails them to the required email address. This works perfectly well, however it requires the user to click on a command button.

What I want it to do is to automatically run the code and send the email every day @21:00.

Any ideas on how to do this? I have tried using Microsoft Task Scheduler which I can get to open the database and run the code using the /x "Macro Name" however it fails on the email part of the code and throws up an error message which states: "ActiveX component cant create object" so wont complete and send the mail.

My code to generate the excel sheets and email are below for info, it errors on the "Set oEmail = oApp.CreateItem(olMailItem)" line when using the task Scheduler but works fine when its run from a command button on the main form:

Option Compare Database

Public Function EOSDailySheetIssueAuto()

Dim objXL As Excel.Application
Dim objXL2 As Excel.Application
Dim objWkb As Excel.Workbook
Dim objWkb2 As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objSht2 As Excel.Worksheet
Dim db As Database
Dim db2 As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim myPath As String
Dim myPath2 As String
Dim strExcelName As String
Dim strExcelName2 As String
Dim strpath As String
Dim strpath2 As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim ExcelFileName As String
Dim ExcelFileName2 As String
Dim MymsgEmail As String
Dim EmailContact As String

Const conMAX_ROWS = 100
Const conSHT_NAME = "Daily Registration Receipt"
Const conSHT_NAME2 = "Daily Reported Receipt"
'Const conWKB_NAME = "K:\mafi\Oil Diagnostics\0. LIMS\0.1 - Registration\Registration Receipt Temp - EOS Daily.xlsx"
'Const conWKB_NAME2 = "K:\mafi\Oil Diagnostics\0. LIMS\0.1 - Registration\Reported Receipt Temp - EOS Daily.xlsx"

Const conWKB_NAME = "G:\0. LIMS\0.1 - Registration\Registration Receipt Temp - EOS Daily.xlsx"
Const conWKB_NAME2 = "G:\0. LIMS\0.1 - Registration\Reported Receipt Temp - EOS Daily.xlsx"

Const conRANGE = "Registration_Excel_Range_Daily"
Const conRANGE2 = "Reported_Excel_Range_Daily"
Const conRANGE3 = "Daily_Reg_Rec_Date"
Const conRANGE4 = "Daily_Rep_Rec_Date"


      'Creates Excel Registration sheet
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("Registration_Daily_List_Holding", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    objSht.Range(conRANGE).CopyFromRecordset rs
    objSht.Range(conRANGE3).Value = Date
    
     'Saves Excel Daily Registration sheet and sets strpath for email attachment name
  'myPath = "K:\mafi\Oil Diagnostics\0. LIMS\0.1 - Registration\Registration Receipts\"
  
  myPath = "G:\0. LIMS\0.1 - Registration\Registration Receipts\"
  
  strExcelName = "L0001 - EOS - Daily Registration Receipt - " & Format(Now(), "dd.mm.yyyy hh.nn") & ".xlsx"
  strpath = myPath & strExcelName
    
  objWkb.SaveAs fileName:=strpath
        
        
       'Creates Excel Reported sheet
  Set db2 = CurrentDb
  Set objXL2 = New Excel.Application
  Set rs2 = db.OpenRecordset("Reported_Daily_List_Holding", dbOpenSnapshot)
  With objXL2
    .Visible = True
    Set objWkb2 = .Workbooks.Open(conWKB_NAME2)
    On Error Resume Next
    Set objSht2 = objWkb2.Worksheets(conSHT_NAME2)
    If Not Err.Number = 0 Then
      Set objSht2 = objWkb2.Worksheets.Add
      objSht2.Name = conSHT_NAME2
    End If
    Err.Clear
    On Error GoTo 0
    objSht2.Range(conRANGE2).CopyFromRecordset rs2
    objSht2.Range(conRANGE4).Value = Date
    
    
     'Saves Excel Daily Reported sheet and sets strpath2 for email attachment name
  'myPath2 = "K:\mafi\Oil Diagnostics\0. LIMS\0.1 - Registration\Registration Receipts\"
  
  myPath2 = "G:\0. LIMS\0.1 - Registration\Registration Receipts\"
  
  strExcelName2 = "L0001 - EOS - Daily Reported Receipt - " & Format(Now(), "dd.mm.yyyy hh.nn") & ".xlsx"
  strpath2 = myPath2 & strExcelName2
    
  objWkb2.SaveAs fileName:=strpath2
     
                   
        'Sets name for excel attachments for email
    ExcelFileName = strpath
    ExcelFileName2 = strpath2
    
          'Sets contact email address from client profile
    EmailContact = "jonathan.lewin@eatechnology.com"
  
  
        'Sets main body of email
    MymsgEmail = "Hello," & vbCrLf & vbCrLf
    MymsgEmail = MymsgEmail & "Please find attached the list of samples submitted to the laboratory for analysis today (Registration Receipt). These samples have been received and registered in our LIMS system." & vbCrLf & vbCrLf
    MymsgEmail = MymsgEmail & "Please review the attached and respond with any corrections." & vbCrLf & vbCrLf
    MymsgEmail = MymsgEmail & "All samples approved and issued are detailed in the (Reported Receipt) attached to this email." & vbCrLf & vbCrLf
    MymsgEmail = MymsgEmail & "Kind Regards," & vbCrLf & vbCrLf
    MymsgEmail = MymsgEmail & "EA Technology" & vbCrLf
    MymsgEmail = MymsgEmail & "Oil Diagnostics Laboratory - LIMS" & vbCrLf
    MymsgEmail = MymsgEmail & "Email: OilData@eatechnology.com" & vbCrLf
    MymsgEmail = MymsgEmail & "Tel: 0151 347 2359"
  
      'Auto Email the PDF & Excel reports
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .Recipients.Add EmailContact
        '.Recipients.Add "OilData@eatechnology.com"
        .Subject = "Oil Diagnostics - L0001 - Daily Registration & Reported Receipts"
        .Body = MymsgEmail
        .Attachments.Add ExcelFileName
        .Attachments.Add ExcelFileName2
        .Send
    End With
          
      'Close excel workbook
   objWkb.Close
   objWkb2.Close
  
  End With
  Set objSht = Nothing
  Set objSht2 = Nothing
  Set objWkb = Nothing
  Set objWkb2 = Nothing
  objXL.Quit
  objXL2.Quit
  Set objXL = Nothing
  Set objXL2 = Nothing
  Set rs = Nothing
  Set rs2 = Nothing
  Set db = Nothing
  Set db2 = Nothing
  End With
End Function

So any thoughts on why its erroring on the ActiveX or is there a way to run it through some code automatically on a daily basis at a set time within Access?

Cheers,
jdlewin1

 

[June7]

I accomplish this with a VBScript file set to execute daily with Windows Task Scheduler. The script opens the db. However, my procedure is not manipulating Excel objects. It exports data to another Access db, zips it, and emails zip file. Never encountered the error you describe.

Set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase "C:\folder path\db.accdb"
accessApp.UserControl = true
accessApp.Run "Test"
'accessApp.DoCmd.RunMacro "TestMacro"
accessApp.Quit

A timer within Access would require the db to already be open and timers can be memory hogs.

 

[jdlewin1]

thanks for the response June.

It all works fine, opens Access, manipulates excel but errors on the email creation. Which I don't get as it all works fine if I run it manually from a command button...

 

[June7]

Sorry, I cannot replicate that issue so cannot offer solution.

 

[theDBguy]

thanks for the response June.

It all works fine, opens Access, manipulates excel but errors on the email creation. Which I don't get as it all works fine if I run it manually from a command button...

Hi, In some cases, when trying to automate Outlook using the Task Scheduler, the user may have to stay logged in, so Outlook can use the correct account.

 

[Micron]

In both cases, is it being run from the same pc? Is there something in the button code that's missing in the code that is called by the TS implementation?

After a quick review, there are a lot of things about your code that I don't see the point of or don't agree with. For examples, you don't use Option Explicit, have declared at least one variable that you don't seem to use, put a lot of unrelated code inside of With blocks...
Hard to tell if you really need two of everything (would depend on if you really need two applications and all the child objects open at the same time).

 

[Gasman]

So for my benefit?

You have Dim oApp As New Outlook.Application, but you have not created a pointer to the object.?

https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application

 

[Micron]

Not sure which post you might be referring to. If mine, and if I understand the point, then yes that would be one of them. I'd say it's another possible reason why it might work when logged in as the user may have Outlook open. Have to admit that that is speculation on my part. The other I would call attention to would be

  Set [B][COLOR=red]db2[/COLOR][/B] = CurrentDb
  Set objXL2 = New Excel.Application
  Set rs2 = [B][COLOR=Red]db[/COLOR][/B].OpenRecordset("Reported_Daily_List_Holding", dbOpenSnapshot)

 

[jdlewin1]

It’s the same pc and the same piece of code.

I appreciate some aspects of the code is not needed but I’m not concerned with that at present. I have pulled a copy off the servers onto a drive so had to reroute some code whilst I figure this automation part out. The code works perfectly well and does the exact job I need it too.

The command button simply runs the macro sequence, If i double click the macro from the nav pane it also executes the code fine.

It only fails with the ActiveX error on the email creation section of the code.

 

[June7]

db2 is declared and set but is not used. db is declared and set and is used in both recordset calls. I doubt this is cause for failure.

I don't use macros and my procedure is a Sub not a Function. Again, doubt this would cause failure either.

 

[The_Doc_Man]

Regarding use of Task Scheduler, you must understand that TS automatically runs all tasks as SYSTEM. To make it work, SYSTEM has to be a valid user of your database AND you also have to work a bit for Outlook to accept SYSTEM as a valid e-mail user.

If that becomes an issue, you might need to do a "Run As" in the task being scheduled, and your IT folks might not like that because "Run As" typically is one of the most potent options you can have. IT shops typically restrict its use.

Given the options, I would research why SYSTEM isn't being allowed to send mail. However, the answer might be simple. Does user SYSTEM have a .PST file on that box?

 

[Micron]

IIRC, using CDO to send mail would avoid the system issue. That's how I sent mail by vba when a pc opened the db via TS, but I can't recall if the pc had an email account. I don't think it needed one in that case because CDO allows you to specify the account and server that should be used.

 

[The_Doc_Man]

Micron, you are absolutely correct that CDO can avoid the SYSTEM issue. The thing is, the PC doesn't NEED an account, exactly for the reasons you specified. CDO is "closer" to SMTP than Outlook (in the "internals" sense.) Outlook has more layers on top of it even if your e-mail account is a POP3/SMTP setup. It is quite possible that the extra layers get in the way because of such things as the Outlook SENT folder. SENT is not an SMTP feature - it is an Outlook feature.

 

[Gasman]

You can run a task under a certain userid if required.?

Regarding use of Task Scheduler, you must understand that TS automatically runs all tasks as SYSTEM. To make it work, SYSTEM has to be a valid user of your database AND you also have to work a bit for Outlook to accept SYSTEM as a valid e-mail user.

If that becomes an issue, you might need to do a "Run As" in the task being scheduled, and your IT folks might not like that because "Run As" typically is one of the most potent options you can have. IT shops typically restrict its use.

Given the options, I would research why SYSTEM isn't being allowed to send mail. However, the answer might be simple. Does user SYSTEM have a .PST file on that box?

 

[The_Doc_Man]

Task scheduler allows the option to use a RunAs for a given task. It is the IT staff who craps a brick if you do so without first getting permission, perhaps by putting up your firstborn as collateral. It is totally not impossible for Task Scheduler to do it, but what usually happens is that there are some settings in the registry to block use of RunAs.

Normally, it is no big deal that you run some file manipulation script as SYSTEM, which is the default for anything run by Task Scheduler anyway, since TM is ALSO run as SYSTEM. And you would think that SYSTEM would automatically have permissions to use RunAs. But with the registry hack in place, that doesn't work. The typical registry hack is that account SYSTEM has all sorts of FILE-related permissions but not all of the privilege-level abilities. Full control of the system is usually entrusted to the Administrator account or a renamed version thereof. When that happens, you find that SYSTEM has only what it needs to be the account under which a system reboot occurs.