0

At an impasse, (a complete loss as to where to go next)

I am building a powershell script to automate a number of manual tasks one of which is to run a query within access and then email that object to a number of recipients.

The closest I have got to was running 

$Acc = New-Object -com Access.Application

$Acc.OpenCurrentDataBase("L:\Operations Database\OPS\OPS-V2.0.accdb")

##Test email object

$Acc.DoCmd.RunMacro("EmailLiveFNZ")

This ran the macro that I had produced in Access but I was then required to manually click on an Outlook warning box so it has not resolved the issues.

I have then started playing around with various versions of the following script but can't get it to work.

$Acc = New-Object -com Access.Application

$Acc.OpenCurrentDataBase("L:\Operations Database\OPS\OPS-V2.0.accdb")

##Test email object

$Acc.DoCmd.SendObject acSendQuery, "LiveFNZFundXfer", acFormatXLS, _
"email@address.com", , , _
"Live Transfers", , False

I am assuming that I can send the query out using powershell automatically I just can't find the assistance I need online...

Alternatively if anyone knows how to save an access object to a specific folder location on a PC I could have the script email the file from there, I can't work it out...

Thanks for your help!

Regards

R

I also tried;

$Acc = New-Object -com Access.Application

$Acc.OpenCurrentDataBase("L:\Operations Database\OPS\OPS-V2.0.accdb")

##Test export object

$Acc.DoCmd.OpenQuery("LiveFNZFundXfer") | Export-CSV "C:\LiveFNZFundXfer.csv"

This compiles without errors but does not export anything

Mr Deans
  • 269
  • 1
  • 4
  • 21
  • What happens when you run these commands on there own? `$Acc.DoCmd.OpenQuery("LiveFNZFundXfer")` or `$Acc.DoCmd.RunMacro("EmailLiveFNZ")`. Do they produce output? – Matt Oct 11 '14 at 14:57
  • All commands individually work fine, it's something with the syntax but cannot for the life of me work it out. – Mr Deans Oct 12 '14 at 08:54
  • NB: you seem to be using underscores for multi-line commands; that's VBScript thing. In PowerShell you need to use the backtick/grave accent (`): http://stackoverflow.com/questions/3235850/how-to-enter-a-multi-line-command – JohnLBevan Oct 12 '14 at 17:00

0 Answers0