Email Pivot table from Access 1

[joshryanhr]

Does anyone know how to email a pivot table from access, I have a form with a pivot table but when I try to email it the email body is just a regular table. I tryed it in reports where you can put a subform that has the pivot tabel but when i try to eamail it, it says that it cant save subforms.

 

[kjv1611]

You may need to have Access spit the pivot table out to Excel or Word, and then send from there. The only other thought I can think of is possibly a snapshot report. But I'm not sure whether or not you can use a pivot table in a snapshot report.

--

"If to err is human, then I must be some kind of human!" -Me

 

[joshryanhr]

I have thought about that, but what would be the code to have access spit it out to excel and then email it with set email addresss all from one button in access?

 

[kjv1611]

To do it all from Access, you'll need at least the MS Excel and probably MS Outlook objects eneabled under Tools-References, and go from there...

Try doing some searching on the topic your asking. There are TONS of resources out there giving the whole step-by-step instructions for this, so it'll take less time on our part here, and you'll probably learn something in the mean time.

I'd search first for "Exporting Access to Excel VBA" or somethign to that extent. Then "VBA email Excel Workbook" or something like that.

First, get the Excel portion done...

And actually, you might not need the Excel and Outlook objects, just depends upon how/what you want to do with it.

For instance, you may can use DoCmd.TransferSpreadsheet to get the data out to Excel format, and then use an already built-in command for sending an email with attachment within Access.

Give a look around the forums here, and/or Google, and post back with more specific questions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[joshryanhr]

I have been to lots have excel and access sites for help on this. I can get it to transfer the data to a spread sheet but i can seem to find the code to export the actual pivot to a temp excel sheet and then have it email the pivot.

 

[joshryanhr]

I got this much so far from doing hours of searching, but now i get a error saying to set object varible.


Private Sub Command13_Click()
DoCmd.OpenForm "qds actions taken", acFormPivotTable, , , acFormPropertySettings, acWindowNormal
DoCmd.RunCommand acCmdPivotTableExportToExcel

ActiveWorkbook.SendMail "email addr", Subject


End Sub

 

[kjv1611]

It sounds like you didn't set an object reference for Excel.

So, perhaps this (assuming you've already ticked the correct referenced, as I mentioned earlier):

Private Sub Command13_Click()
  [highlight]Dim xlApp As Excel.Application[/highlight]
  
  DoCmd.OpenForm "qds actions taken", acFormPivotTable, , , acFormPropertySettings, acWindowNormal
  DoCmd.RunCommand acCmdPivotTableExportToExcel

  [highlight]xlApp.[/highlight]ActiveWorkbook.SendMail "email addr", Subject

  [green]'Make sure you've done what you needed done with this instance of Excel before closing it, but when finished, do definitely close it.  ;0)[/green]

  [highlight]xlApp.Quit
  Set xlApp = Nothing[/highlight]
 
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[joshryanhr]

i get run time error 91
"object variable or with block variable not set

 

[kjv1611]

Did you try checking your references? Make sure you've got all the references you need enabled for your project. Anything you've got that requires an object needs a reference, pretty much. Some are there by default, others, such as Excel and Outlook, have to be added on a case by case basis.

--

"If to err is human, then I must be some kind of human!" -Me

 

[joshryanhr]

Ok i set all microsoft office refference to get this to work, but it sends the whole workbook, all i want it to do is send the pivot table as the body.

 

[SkipVought]

In excel try doing a File > Send to... of your choosing.

Then turn on your macro recrder and record doing it.

Incorporate the code into your procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joshryanhr]

i tryed that but the macro shows up blank

 

[kjv1611]

but it sends the whole workbook, all i want it to do is send the pivot table as the body.

That's what it's supposed to do. But it sounds like you're actually wanting it to be in the body of your email, rather than as an attachment.

I suppose that is still possible, but I'd have to ask is it worth going through the necessary hoops?

--

"If to err is human, then I must be some kind of human!" -Me

 

[joshryanhr]

In the case that im not here some one else will have to run the report and send the eamail. Rather then have to train someone on how to do this it would be better to just have them click a button and everything be automated for them.

 

[kjv1611]

Just to be totally clear - you want the pivot table created in Access, and then basically pasted into your email body?

If so, then maybe there's something that can be done using the clipboard.

But if you just need it sent as an attachment, you should be pretty much ready to go - just put all the code in your Access database, behind a button.

--

"If to err is human, then I must be some kind of human!" -Me