0

An excel file (name : test.xls) contains n rows, n is not a fixed value.

There are 3 columns in the excel which contain data ( A, B, C). The value of column B is always a date (format : MM/DD/YYYY).

Need to compare currentDate (SYSDATE) with the value in B for each row in the excel, if there are any matches, then send value of A,B,C of that particular record via mail to address abc@xyz.com & cde@xyz.com with subject temp.

In case there are multiple matches, i.e. more than 1 row, say 2, has date as currentDate then send value of A,B,C for both the rows in same mail.

Is there a way to run this script everday automatically ? The computer in question where this script will be placed maybe on, shutdown or logged-off. In most cases it will be logged-off, rarely shutdown (hence adding in Startup folder wont be effective).

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Lastwish
  • 317
  • 10
  • 21

1 Answers1

2

Ideally the computer isn't shut down, else you'd have to use WoL to wake it up.

You should create a scheduled task which runs even when the user isn't logged on:

Scheduled task - Run whether user is logged on or not

Below is sample script for your reference, I'm not actually adding the values to the email body - you will need to modify to suit your needs:

$file = "C:\Users\Vincent\Desktop\test.xls"

$body = @"
Following data was found in report:

"@

#XL const
$xlValues = -4163 
$xlPart = 2

$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $Excel.Workbooks.open($file) 

#Sheets(1)
$Worksheet = $Workbook.Sheets.Item(1)
#Column B
$colBRange = $Worksheet.Columns.Item(2)

#match returns error if nothing found, easier to use Find function
#$Worksheet.Application.WorksheetFunction.Match((Get-Date),$colBRange)

$dateString = [string](get-date -DisplayHint Date -Format "dd/MM/yyyy")

#.Find(What:="05/05/2015", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
$result = $colBRange.Find($dateString, $worksheet.Cells.Item(1,2), $xlValues)
#save address of first found cell to detect when find function wraps around
$first = $result

$continue = $result -ne $null
while ($continue){
    #work with result, like collect them
    write "Found matching date in row $($result.Row) of $($Worksheet.Name)"

    #prepare email body:
    $body = $body + ""
    $result = $colBRange.FindNext($result) #find next after current result
    $continue = ($result.Address() -ne $first.Address())
}

$PSEmailServer = "smtp.mydomain.com"

Send-MailMessage -From "no-reply@mydomain.com" `
    -To "me@mydomain.com" `
    -Subject "Report" `
    -Body $body
Vincent De Smet
  • 4,859
  • 2
  • 34
  • 41
  • it would be interesting to use name parameters, this SO question has a neat approach for this: http://stackoverflow.com/questions/5544844/how-to-call-a-complex-com-method-from-powershell – Vincent De Smet May 05 '15 at 10:52
  • Firstly, my apologies for the delay of reponse. Thank you for your answer. Can you please explain about #XL const and PSEmailServer, what is their purpose ? – Lastwish May 08 '15 at 08:23
  • xlValues: The Find function takes a parameter called "LookIn" - see the comment line above (I got it by recording a macro and doing ctrl+f in excel + look at VBA). I want to look at the value of the cell, not the formula. (I got my sample dates through =TODAY()) function and format to display DD/MM/YYYY) . I want to lookIn values, so I need to use the xlValues const from VBA. I typed ?xlValues in the immediate window and it gave me that number. Excel has this const built in, but it isn't available in PS unless you define it and give it the correct value. that's what I did. – Vincent De Smet May 08 '15 at 08:28
  • PSEmailServer: powershell has a Send-MailMessage commandlet, I think you can actually pass in the smtp server, but you can also set the $PSEmailserver variable to define the default email server. – Vincent De Smet May 08 '15 at 08:29
  • Oh, gotcha. But if the mail is sent via outlook, then whats the value of PSEmailServer ? do we need to define outlook object and all ? – Lastwish May 08 '15 at 10:26
  • I'm not sure why you try to do everything through com automation while using the available powershell commandlets will be a lot faster. Do you want to automate outlook to just take over whatever mail config it has? you can drop PSEmailServer if you're automating outlook. but I'd drop the data to csv and avoid Excel/Outlook and do a pure PS-only solution – Vincent De Smet May 08 '15 at 10:30