How to e-mail the entire excel file through VBA script

Question

I wish to automate emailing of my excel file which is currently being performed by me manually

I have the following flow structure: Task scheduler -> .bat file -> VBA script -> excel formulas

This means that my task scheduler will hit .bat file which would trigger VB file to execute the code and this will dump data from SQL DB into Excel file and then formulas inside of Excel file will prepare charts and graphs and all the calculations.

Here is my code of VB file:

    Macro1
Private Sub Macro1()

Set objExcel  = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\kursekar\Documents\Work\Apps\ReferralStrApp\StdztnRefRepTrial.xlsx")
objExcel.Visible = False
Set Conn = CreateObject("ADODB.Connection")
Set RS   = CreateObject("ADODB.Recordset") 
Dim SQL
Dim Sconnect
Sconnect = "Provider=SQLOLEDB.1;Password='25LaurelRoad';User ID='CPSMDIT\kursekar';Data Source='analyzer';Initial Catalog='analyzer_str';Integrated Security=SSPI; Persist Security Info=True;"
Conn.Open Sconnect

SQL = "WITH cte_REFERRALS_REPORTS(referralnum, refer_from, refer_from_name, refer_from_id, refer_to, refer_to_name, refer_to_id) AS (SELECT referralnum, refer_from, CASE WHEN refer_from_id = 'R' THEN RdicF.refname WHEN refer_from_id = 'P' THEN PdicF.provname END AS refer_from_name, refer_from_id, refer_to, "
SQL = SQL & "CASE WHEN refer_to_id = 'R' THEN RdicT.refname WHEN refer_to_id = 'P' THEN PdicT.provname END AS refer_to_name, refer_to_id FROM referral_t r Left Join refcode_t RdicF ON  r.refer_from = CASE WHEN r.refer_from_id='R' THEN RdicF.refcode ELSE NULL END Left Join refcode_t RdicT ON  r.refer_to = CASE WHEN r.refer_to_id = 'R' THEN RdicT.refcode ELSE NULL END "
SQL = SQL & "Left Join provcode_t PdicF ON r.refer_from  = CASE WHEN r.refer_from_id = 'P' THEN PdicF.provcode ELSE NULL END Left Join provcode_t PdicT ON r.refer_to = CASE WHEN r.refer_to_id = 'P' THEN PdicT.provcode ELSE NULL END ) SELECT chgslipno , a.acctno, patfname, patlname, appt_date, a.enccode, pr.provname "
SQL = SQL & ",a.provcode, rfc.refname, a.refcode, r1.refer_from as r1_ref_from, r1.refer_from_id as r1_ref_from_id, r1.refer_from_name as r1_ref_from_name, a.referral1 as r1_refnum, r2.refer_from as r2_ref_from, r2.refer_from_id as r2_ref_from_id, r2.refer_from_name as r2_ref_from_name,a.referral2, prgrc.provgrpdesc,s.specdesc, a.prov_dept, pos.posdesc,pr.cred "
SQL = SQL & "FROM apptmt_t a Left JOIN patdemo_t p ON a.acctno = p.acctno LEFT JOIN provcode_t pr ON pr.provcode = a.provcode LEFT JOIN refcode_t rfc ON a.refcode = rfc.refcode LEFT JOIN (SELECT*FROM cte_REFERRALS_REPORTS) r1 ON a.referral1 = r1.referralnum LEFT JOIN (SELECT*FROM cte_REFERRALS_REPORTS) r2 "
SQL = SQL & "on a.referral2 = r2.referralnum LEFT JOIN provgrpprov_t prgrpr on a.provcode = prgrpr.provcode LEFT JOIN provgrpcode_t prgrc on prgrpr.provgrpcode = prgrc.provgrpcode LEFT JOIN specialty_t s on pr.speccode = s.speccode LEFT JOIN poscode_t pos on a.poscode = pos.poscode "
SQL = SQL & "WHERE UPPER(a.enccode) in ('CON','APE','COB','CONZ','HAC','HFUI','MMN','NCG','NCHF','NCPF','NHFU','NMC','NOB','NP','NP15','NPE','NPI','NPOV','NPS','NPSV','NPV','OVN','IMC','NP30') AND UPPER(a.appt_status)='ARR' AND appt_date >= '2017-01-01' "
SQL = SQL & "ORDER BY a.acctno"

Set Sheet = objWorkbook.ActiveSheet
Sheet.Activate

RS.Open SQL, Conn
 Sheet.Range("A2").CopyFromRecordset RS

RS.Close
Conn.Close

objExcel.DisplayAlerts = False
'Release memory
'Set objFSO = Nothing
'Set objFolder = Nothing
'Set objFile = Nothing
objWorkbook.Save
objExcel.DisplayAlerts = True
objWorkbook.Close
objExcel.Workbooks.Close
objExcel.Quit

'Set objExcel = Nothing
MsgBox ("Saved")
End Sub

Answer 1

I know this is old question. I am doing this just for the purpose of future newbies of VB and SO who might encounter similar or same problems as per imaginary demands of their clients.

I have pasted my entire code and the Solution of the code with the help of highly revered VB developers at SO. The same question and its solution can be found below at this given link below:

https://codereview.stackexchange.com/questions/219371/write-to-excel-from-sql-db-using-vba-script/219378#219378

Answer 2

This depends heavily on how you want to send the email. Are you using outlook or are you trying to send it through gmail, yahoo or a private smtp server?

Regardless, I would put the vba code to send the file in the module of the excel file. The bat file calls Macro1 to update the workbook and then runs the macro code inside the module in the workbook. Make sure the code is in a module, not in any of the sheets or workbook. If you are using outlook to email, the code inside the module should look something like this with the Outlook Application module loaded in the library references:

Sub Email_Active_Workbook()
'Working in Excel 2000-2016
    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 = "email@address"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

This is adapted from: https://www.rondebruin.nl/win/s1/outlook/amail1.htm

This site is also worth referencing if sending through outlook: https://wellsr.com/vba/2018/excel/vba-send-email-from-excel/

And this: https://powerspreadsheets.com/send-email-excel-vba/

And then in your VB file Macro1 below the objWorkbook.save line add:

objExcel.Application.Run "StdztnRefRepTrial.xlsx!Email_Active_Workbook"

If your sending through an smtp server, it become slightly more complicated because you need to ensure the CDO library is loaded and it depends on the security settings of the mail server. This thread gives a working example of VBA sending an email on a PC, which can easily be adapted to your needs: Send email with workbook from VBA macro on both Windows and Mac

If SSL is required, it gets a little more complex. See this site for more details, specifically the link to Github for the SSL code: https://www.makeuseof.com/tag/send-emails-excel-vba/

EDIT

I completely agree with the prior comments, this is not the best way to accomplish reporting SQL data. This can be a quick fix, but using SSRS to send these reports should be your primary focus. I hope this isn't mission critical data, otherwise the emails will not get sent if you get an error when you leave this unattended.