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
source to share
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.
source to share