Dumping data to .pdf, taking screenshots, mirroring a sheet, or other report type?

[S Davis]

Hello all,

I have a tool that essentially takes a snapshot of the current status
of the company. This is run every morning to encompass the entire
previous day's activities.

The next step in progression with this tool is to send a summary out
to all managers / VP's, etc. I'd love to come up with something in VBA
to make this easier. Currently this already logs all data into access
for historical tracking over time, but I would prefer to come up with
a way to either take a screenshot, create a pdf of the sheet, or
create a new sheet lacking formulas but containing the values and
formatting.

This is a long shot, but can it be done?

 

[Jim Thomlinson]

Take a look at this...

http://www.excelguru.ca/node/21

 

[S Davis]

Take a look at this...

http://www.excelguru.ca/node/21
--
HTH...

Jim Thomlinson








- Show quoted text -

Thanks for the reply.. that works great and I am going to look into
using it. However, given that the generation of the PDF will be spread
across more users than myself, I'm hesitant to use an outside program
(ie. not Adobe). That requires widespread installation of the program,
which then becomes an IS issue... real hassle.

I noticed however that you can go to File -> Send to -> Mail
Recipient, and then mail off an entire worksheet or multiple
worksheets. This seems to be good enough for me, but I would like to
just incorporate it into some code that would automatically happen
every time I upload the history to Access via a button.

Possible?

Can I perhaps modify this code to mail off a selection, range, or
entire worksheet?:
*********
Dim OutApp As Object
Dim OutMail As Object

Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
'Set OutMail = OutApp.CreateItem(olMailItem)
Set OutMail = OutApp.CreateItem(0)
strbody = "*email content deleted*" & vbNewLine & vbNewLine & _
"*email content deleted*" & vbNewLine & vbNewLine & _
"*email content deleted*" & vbNewLine & vbNewLine & _
"*email content deleted*"

With OutMail
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.BCC = ""
.Subject = "*email subject*"
.Body = strbody
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
ThisWorkbook.Close SaveChanges:=True

End Sub
*********

 

[Tom Ogilvy]

create a new sheet lacking formulas but containing the values and

formatting

Sub MakeRecord()
activesheet.copy
activesheet.usedrange.formula = activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.Close SaveChanges:=False
End Sub

 

[Tom Ogilvy]

Private Sub Workbook.Open()
activesheet.usedrange.formula = activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)"
ActiveWorkbook.Close SaveChanges:=False
End Sub

in the Thisworkbook module.

 

[S Davis]

Sub MakeRecord()
activesheet.copy
activesheet.usedrange.formula = activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.Close SaveChanges:=False
End Sub

Thanks Tom. I'm using this. I will do a search after this post, but
for completeness sake it would be great if I could get a response on
how to replace all "activesheet" with a worksheet name. Worksheet is
titled "Daily Email".

 

[Tom Ogilvy]

Private Sub Workbook.Open()
' you only need to change the first one
ActiveWorkbook.Worksheets("Daily Email").copy
' now the activesheet/activeworkbook is the copy
ActiveSheet.usedrange.formula = Activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)"
ActiveWorkbook.Close SaveChanges:=False
End Sub

 

[Tom Ogilvy]

Looks like the copy line got omitted. It is still needed - however, see my
response to your next question.

 

[S Davis]

Looks like the copy line got omitted. It is still needed - however, see my
response to your next question.

--
Regards,
Tom Ogilvy







- Show quoted text -

This is great. Absolutely great. Im just mucking around trying to get
it to mail more than one recipient but that shouldnt be too hard. As
it stands, it works perfectly, just as I wanted. Thank you!

 

[Tom Ogilvy]

Recipients:=Array("(e-mail address removed)","(e-mail address removed)","(e-mail address removed)")

as an example.

 

[S Davis]

Recipients:=Array("(e-mail address removed)","(e-mail address removed)","(e-mail address removed)")

as an example.
--
Regards,
Tom Ogilvy







- Show quoted text -

Thanks You seem to know most everything I ever have a question
for... any recommended reading or resources?

And while we're at it, any way to CC some people on this email? I have
two distinct management classes to deal with here...

Thanks again.

 

[Tom Ogilvy]

I don't believe there is any provision for CC. You would need to use the
Outlook approach you posted for that.

http://www.rondebruin.nl/sendmail.htm