Sending an updated Excel sheet Every business day using VBA Excel / Outlook

Every day at 3pm I have to send an Excel workbook to a colleague. The macro in the workbook just copies all the cells in one sheet and pastes the Special into another sheet and saves the workbook. I wrote a macro to do this and also send it to an email address, however I am struggling to send it automatically. I have already instructed Scheduling Tasks, but I don’t know how to establish a connection between opening Excel, executing Marco, saving the workbook and sending it to a specific person. Code below - thanks for your help.

Sub Fixing()

    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-9
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
      xlNone, SkipBlanks:=False, Transpose:=False
    Range("I7").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = ""
        .CC = ""
        .BCC = "my email address"
        .Subject = "Daily Email"
        .Body = ""
        .Attachments.Add ("F:\Excel Models\Daily Email.xlsm")
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

      

+3


source to share


1 answer


Create VBScript file

eg. start excel action.vbs

with the following code inside:

    dim EXL
    set EXL = CreateObject("Excel.Application")
    'not required
    EXL.Visible = true

    'your file and macro    
    EXL.Workbooks.Open "full path to your excel file including extension here" 
    EXL.Run "Fixing"

    'close everything
    EXL.Quit
    Set EXL = Nothing

      



Save the file and set Windows Task Scheduler

to run this file vbs

at 3pm.

+1


source







All Articles