How to run Outlook VBA code with VBScript?

Question

I am trying to run working Outlook VBA code with VBScript.

The goal is to send a file from a location on drive to specified email address. This has to be recurring e.g. 7 am every day. Task Scheduler can do that I guess.

How do I create a vbs file to run the Outlook VBA code and link the vbs file to Task Scheduler?

Current macro:-

Dim fldName As String
Sub SendFilesbyEmail()
Dim sFName As String

    i = 0
    fldName = "\\blackstone.com\files\home\apac\gurgaon\Aggarwam\Settings\desktop\EXCEL FILES\Notes\"
    sFName = Dir(fldName)
    Do While Len(sFName) > 0
      Call SendasAttachment(sFName)
      sFName = Dir
      i = i + 1
      Debug.Print fName
    Loop
    MsgBox i & " files were sent"

End Sub

Function SendasAttachment(fName As String)

Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Dim olAtt As Outlook.Attachments

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0) ' email
Set olAtt = olMsg.Attachments

' attach file
olAtt.Add (fldName & fName)

' send message
With olMsg
  .Subject = "Here's that file you wanted"
  .To = "[email protected]"
  .HTMLBody = "Hi " & olMsg.To & ", <br /><br /> I have attached " & fName & " as you requested."
  .Send

End With
End Function

This isn't VBScript, it's VBA. VBScript doesn't support the As keyword, everything in VBScript is a Variant. — Mathieu Guindon
Yes I understand that. You need to remove the As clauses everywhere, this is VBA code, VBScript doesn't run VBA code, it's two distinct languages. VBA runs VBScript, VBScript doesn't understand VBA. — Mathieu Guindon
I am trying to create a VBScript to open up excel and running this macro in excel. I think creating a whole script as VBS would be be an issue in future.. — Mayank

Jerry M Jerry M · Accepted Answer · 2018-07-24T20:28:41

Set fso = CreateObject("Scripting.FileSystemObject")


Set myxlApplication = CreateObject("Excel.Application")
myxlApplication.Visible = False
Set myWorkBook = myxlApplication.Workbooks.Open( curDir & "C:\ABC\Pipeline Report.xlsm" )
myWorkBook.Application.Run "Module1.Refresh" 'Change to the Module and Macro that contains your macro
myxlApplication.Quit

How to run Outlook VBA code with VBScript?

1 Answers