The code so far works well, I thank you! I have a few questions:
http://imgur.com/a/fkc2W Edit: upon further testing I cant seem to reproduce the error reliably. idk, seems pretty good, should I just build an If (errorlevel != "0") then try again function?
Edit2 Seems like error is resolved, May have been how I was writing the email, but since setting it up as Email.body := header . body it has calmed down.
How can I open a saved workbook and loop through column C until cell("c:" & rownumber) contains %city% and then return value of cell("E:" & rownumber) ( lookup email address by city name)
Where can I read up on COM, and start to understand Hierarchy, objects, methods, properties etc, the more simple the better me:="n00b"
Is there a function that exists that I could modify to interact with a subfolder of my inbox named 24 hr reports , that contains these reports. My intention would be to once a day, process each email in that folder ( just these reports, sent there by outlook rule) so that each time it saw no warnings, it would delete the email and move to the next one, or if it was an email with warnings, create the chart, send the email as this script does, then delete, and move on to the next one. if not, how would you approach this?
Thank you so much for your time and knowledge!
Code: Select all
f1::Reload
Pause::Pause
f2::ListVars
Pause
!`::
#SingleInstance
SetKeyDelay, -1
TMP:=""
RMRKS:=""
Date1:=""
Date2:=""
City:=""
Chart:=""
Warnings:=""
EMAIL:=clipboard
city:=Getcity(EMAIL) ; Get city name
Warnings := ParseWarnings(Email)
if Warnings.MinIndex() ; If there was stuff to put into array
CreateChart(Warnings, chart, city)
else
msgbox , No Warnings
;###################################################################################################################################################################################################################;
;########################################################################################## Functions ####################################################################################################;
;###################################################################################################################################################################################################################;
ParseWarnings(Email)
{
Warnings := []
EMAIL := StrReplace(EMAIL, """") ; Email text var. Remove all quotes.
Loop, Parse, EMAIL, `n, `r ; Parse email by line
{
tmp := StrSplit(A_LoopField, ";") ; For each line break it into chunks by ";"
rmrks := tmp.6 ; Warn code is in 6th index
If InStr(rmrks, "Warning") ; If this is a warning line
{
date1:=StrSplit(tmp.1, "/") ; Date/time is in DD/MM/YYYY , split it up by "/"
Warnings.Push( {"When": date1.2 "/" date1.1 "/" date1.3, "What": rmrks} ) ;Warnings[1].when //// Warnings[1].what
}
}
return Warnings
}
CreateChart(Warnings, chart, city)
{
static xlColumnClustered := 51
, xlColumnField := 2
, xlCount := -4112
, xlDatabase := 1
, xlHidden := 0
, xlPivotTableVersion12 := 3
, xlRowField := 1
XL := ComObjCreate("Excel.Application") ; Create an excel object
Wbk := XL.Workbooks.Add ; Add a workbook to the object
Xl.Visible := True ; Make it visible
XL.DisplayAlerts := False
Sheet := xl.Sheets(1) ; Save a reference to this sheet
; Set Column headings and width
Sheet.Range("A1").Value := "Time" ; Create Time header
Sheet.Range("A:A").columnwidth := "20"
Sheet.Range("B1").Value := "Warning" ; Create Warning Header
Sheet.Range("B:B").columnwidth := "56.86"
; Create a safe array and copy data into it. Then put the safe array into a range.
nRows := Warnings.MaxIndex() ; The number of rows
SafeArray := ComObjArray(12, nRows, 2) ; Create a safearray of the correct size. (Type = 12, Rows = nRows, Columns = 2)
for i, Warning in Warnings
{
SafeArray[i - 1, 0] := Warning.When ; SafeArray[RowNumber, ColumnNumber] := Value
SafeArray[i - 1, 1] := Warning.What ; SafeArray index starts at 0 (not 1)
}
Cell := Sheet.Range("A2") ; The top left cell of the range
Sheet.Range(Cell, Cell.Offset(nRows - 1, 1)).Value := SafeArray ; Put the SafeArray into the Range
rng := Sheet.UsedRange.address
trgt := Sheet.range("c1")
pvt := xl.ActiveWorkbook.PivotCaches
.Create(xlDatabase, rng, xlPivotTableVersion12)
.CreatePivotTable(trgt, "PivotTable1",, xlPivotTableVersion12)
pfWarning := pvt.PivotFields("warning")
pfWarning.Orientation := xlColumnField
pfWarning.Position := 1
pvt.AddDataField(pfWarning, "Count of Warning", xlCount)
; **Is it necessary to set 'pfTime.Orientation' multiple times?
pfTime := pvt.PivotFields("time") ; VBA = With ActiveChart.PivotLayout.PivotTable.PivotFields("Time")
pfTime.Orientation := xlHidden ; VBA = ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
pfTime.Orientation := xlRowField ; VBA = .Orientation = xlRowField
pfTime.Position := 1 ; VBA = .Position = 1
pfTime.AutoGroup ; Must be Office version >= 2016
pvt.PivotFields("Minutes").Orientation := xlHidden ; ???
pfTime.Orientation := xlHidden ; ???
Sheet.Shapes.AddChart
wbk.ShowPivotChartActiveFields := false
Sheet.ChartObjects(1).Activate
Chart := wbk.ActiveChart
Chart.ChartTitle.Delete
Chart.ChartType := xlColumnClustered
Chart.PivotLayout.PivotTable.PivotFields("Warning").Orientation := xlColumnField
Chart.PivotLayout.PivotTable.PivotFields("Warning").Position := 1
CreateEmail(Chart, city,Warnings)
XL.quit
return Chart
}
CreateEmail(Chart, city,warnings)
{
; Reference: http://stackoverflow.com/questions/25603864/copy-excel-chart-to-outlook-mail-message
; Alternative method: http://www.mrexcel.com/forum/excel-questions/562877-paste-chart-into-email-body.html
static olMailItem := 0
olApp := ComObjCreate("Outlook.Application")
Email := olApp.CreateItem(olMailItem)
Email.Display
Email.To := "test64413@gmail.com"
Email.Subject := "*** Todays Warnings for Your Gain Site in " city " ***"
body:=warndata(warnings)
header:="`n" "`n" "Data:" "`n"
Email.body := header . body
Chart.ChartArea.Copy
wEditor := olApp.ActiveInspector.WordEditor
wEditor.Application.Selection.Paste
}
GetCity(EMAIL)
{
Split := StrSplit(EMAIL, "`n", "`r")
City := Split[Split.Length()-5]
IfNotInString, City, ,
City := Split[Split.Length()-6]
City:=strsplit(city,",")
City:=City.1
Return City
}
warndata(warnings)
{
for i, Warning in Warnings
body .= "`n" Warning.When "`t" Warning.What "`n" ; This is not right
return body
}