Power Query -> Outlook

[stanl]

The script below and attached text files combine 2 previous threads into a single operation. Appreciate feedback or anyone taking time to test. I will be using the procedure with internal db's or SFTP downloads where the M code will be more complicated.

Code (WINBATCH) Select Expand



;Winbatch 2020b - Testing Excel Power Query => Outlook 
;Requires Excel 2016 or Higher or Power Query added to Excel 2013 or earlier
;Steps:
;       check for support text files
;       delete output workbook if it already exists
;       create output workbook based on web query transformed with M Code
;       copy created range to clipboard
;       save workbook/close Excel
;       create outlook email preview with workbook attached
;              and clipboard contents inserted into body
;
;Stan Littlefield  April 17, 2021
IntControl(73,1,0,0,0)
gosub udfs


cXLS = Dirscript():"sunrise.xlsx"  ;workbook to be created
cMcode = Dirscript():"sunrise.txt" ;M code template for Web Access
html =  Dirscript():"test.txt"     ;email body text with HTML tags
If FileExist(cXLS) Then FileDelete(cXLS)
If ! FileExist(cMcode) Then Terminate(@TRUE,"Cannot Continue",cMcode:" not found")
If ! FileExist(html) Then Terminate(@TRUE,"Cannot Continue",html:" not found")
clip=@FALSE
cMonth = "June" ;change as needed to another month to test
qry = "Sunrise" ;
cn = "Query - ":qry
newcode = FileGet(cMcode)
newcode = StrReplace(newcode,"|MTH|",cMonth)


BoxOpen("Please Wait","Creating Excel Power Query Web Connection")
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = qry
BoxOpen("Please Wait","Creating Query/Saving Workbook/Then Close Excel")
oXL.ActiveWorkBook.Queries.Add(::Name=qry,Formula=newcode,Description="Sunrise Query")
;at this point the connection is created but data not sent to worksheet
;create a ListObject with OLEDB connection string
cSource = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=%qry%;Extended Properties=''"
qt = oWS.ListObjects.Add(::SourceType=0,Source=cSource,Destination=oWS.Range("$A$1")).QueryTable
qt.CommandText = "Select * FROM [%qry%]"
qt.Refresh()
;may take some time but should save
oXL.ActiveWorkbook.SaveAs(cXLS)
oWS.UsedRange.Select()
oXL.Selection.CopyPicture(1,2)
clip=@TRUE
oXL.ActiveWorkbook.Close()
oXL.Quit()
BoxShut()
oWS=0
oXL=0
If fileexist(cXLS) Then createmail()
Exit


;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",geterror())
;======================================================================================================


:CANCEL
Display(2,"Thank you","Goodbye...")
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine createmail()
BoxOpen("Please Wait","Preparing Outloook Email")
cTo = "somebody@somewhere.com"   ;use your own
cSub = "Automating sending Outlook Message with attachment "
data = cXLS
cBody = fileget(html) ;simple text with HTML wrappers
outlook = CreateObject("Outlook.Application")
email = outlook.CreateItem(0)
email.GetInspector.Activate()
BoxShut()
sSig = email.HTMLBody
email.To = cTo
email.Subject = cSub
email.HTMLBody =  cBody:sSig
If clip Then email.GetInspector.WordEditor.Range(0,0).Paste()
email.Attachments.add(data)
;only show email
;email.Send()
ObjectClose(outlook)
outlook = 0
pause("Email Preview Created","Modify, send, or close as needed")
#EndSubRoutine


Return




[oradba4u]

Not sure if this helps, but I compiled your code (WinBatch 2021b) on a Win 10 pro machine (all the latest updates) and voila! My Excel 2019 spreadsheet loaded the data, BUT I couldn't send anything because I do not use Outlook (it is dis-abled)

[stanl]

Not sure if this helps, but I compiled your code (WinBatch 2021b) on a Win 10 pro machine (all the latest updates) and voila! My Excel 2019 spreadsheet loaded the data, BUT I couldn't send anything because I do not use Outlook (it is dis-abled)

The email portion could be written for Postie or Sendmail, not sure of the LOE.