Auto Send Email Data from Excel using Outlook

[mrsec]

Hi I was wondering if the VBA code to auto run without opening the workbook/worksheet for the code to work?i know that the event is when open.But is there a way to make it run without opeing?

Private Sub Workbook_Open()
    
    Dim i As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
   
    
    
    For i = 2 To Range("C65536").End(xlUp).Row
        If Cells(i, 6) <> "Y" Then
            If Cells(i, 3) - 7 < Date Then
                Set OutMail = OutApp.CreateItem(0)
                strto = Cells(i, 4).Value 'email address
                strsub = "Project " & Cells(i, 2).Value & " is due on Due date " & Cells(i, 3).Value 'email subject
                strbody = "Dear " & Cells(i, 1).Value & vbNewLine & "please update your project status" 'email body
                
                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    .Send
                    '.display
                                    
                End With
                On Error Resume Next
                Cells(i, 5) = "Mail Sent " & Now()
                Cells(i, 6) = "Y"
                
            End If
        End If
    Next
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[KOKOSEK]

Take this code from Workbook_Open and put it in standard module, for ex. Module1, as Sub SendEmail().
Then open Notepad and create file RunWithourXL.vbs

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'\Path\Into\Your\File\XLFileWithThisMacro.xlsm'!Module1.SendEmail"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
WScript.Quit

save it and you can run it without opening excel.

 

[mrsec]

Take this code from Workbook_Open and put it in standard module, for ex. Module1, as Sub SendEmail().
Then open Notepad and create file RunWithourXL.vbs

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'\Path\Into\Your\File\XLFileWithThisMacro.xlsm'!Module1.SendEmail"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
WScript.Quit

save it and you can run it without opening excel.

Just to clarify,ill moved my code to module?and create a notepad with your code and name it RunWithourXL.vbs?

 

[KOKOSEK]

Yes. Excel file with code in module and pure txt file (with vbs extension, created in notepad)
If you done it correctly double click on this vbs file runs vba without opening this xlsm file.