Email and display a worksheet

Posted by Dean on January 31, 2002 10:32 PM

Is it possible to email a single worksheet out of a workbook and then allow the recipient to view that worksheet with their copy of Excell? No supporting VBA code is needed, just the worksheet and possibly attached headers & footers.

How about the same thing but view with something other than Excel?

How about formatting the thing up and emailing a visual representation of the worksheet (essentially what would have been printed)?

Posted by James on February 01, 2002 3:34 AM

I did something like this.

I started with an InputBox, where the user can give a filename (this is to essentially make a working copy of what you want to send). In the source workbook, I copied the required worksheet and saved as the filename given. Then just copied the entire sheet and pasted special as values only. (Then in fact I deleted some unnecessary columns) Then just email this file with xlDialogSendMail.

I have one problem about this. The copy needs to be stored someplace. So I hard wired it to C:TEMP. If you figure out how to get around this, please let me know.

Anyway here's my macro: (some variables and comments are in Spanish because of where I work)

Sub EMAILREPORT()

'Macro to send Report as only one page Excel workbork with no formulas

MinePlanFecha = InputBox("Ingresa la fecha de Programa de Mina" & Chr(13))
If MinePlanFecha = "" Then Exit Sub 'if cancel, stop procedure
Sheets("Report").Copy

'Save as separate workbook and paste only values
ActiveWorkbook.SaveAs ("C:\TEMP\" & MinePlanFecha)
Sheets("report").Select
Columns("D:W").Select
ActiveSheet.Unprotect
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("W:AQ").Select
Selection.Delete Shift:=xlToLeft
Range("A16").Select

'Send Report as email and close report
Application.Dialogs(xlDialogSendMail).Show arg1:="", arg2:="Programa de Mina : " & MinePlanFecha
ActiveWorkbook.Close savechanges:=False
End Sub

Hope this helps.

Posted by Dean on February 01, 2002 2:55 PM

I'll give this a try - thanks for the very informative response.