Email a worksheet
 

Hi
I have a requirement of having a "Email Report" button on a worksheet. This button basically emails the active worksheet to the reciepents. I was able to get this feature to work but with some issues. The code that i am using i

Sub WorksheetOnlyEmail(
Dim Recip As Varian
Dim res
Dim inpresp As Strin
Dim impmsg, filename As Strin
resp = MsgBox("Have you entered the Team Name?", vbYesNo, "Team Information"
If resp = 6 The
'MsgBox "you have selected yes
inpmsg = "Please Enter the complete email addresses" & (Chr(13)) & " (eg ) and seperate multiple email id's by a "";""
inpresp = InputBox(inpmsg, "Email Information", "Email ID here"
filename = Worksheets("Report Out Pitch").Range("C1") & " YTD COQ Report" & Format(Date, "MMDDYY") & "-" & Format(Time(), "HHMMSS"
MsgBox filenam
If Len(inpresp) < 0 The
'MsgBox inpres
If CheckIfEmailIDCorrect(inpresp) The
Recip = Array(inpresp)
Worksheets("Report Out Pitch").Cop
Application.DisplayAlerts = Fals
ActiveWorkbook.SaveAs filename:=filename & ".xls
ActiveWorkbook.SendMail Recipients:=Recip, Subject:=filename & "YTD COQ Report
ActiveWorkbook.Clos
Application.DisplayAlerts = Tru
End I
End I

ElseIf resp = 7 The
'MsgBox "You have selected No
End I
End Su

The issues are
The worksheet also has a button to print out a range. This button does not work does not work in the emailed version of the report
when i open the email sent, there is a message displayed saying that the worksheet has links to another worksheet and if i wanted to update it
How can i overcome these two issues?

Thanks in advanc

Email a worksheet
 

Hi Ron
Thanks for the advice..it worked. But i am still getting the "automatic link" message, how do i fix it

Thanks

Email a worksheet
 

You have links to other workbooks or sheets

You can paste special as values
After the copy line add this code

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"hyderabadwala" wrote in message
...
Hi Ron,
Thanks for the advice..it worked. But i am still getting the "automatic link" message, how do i fix it?

Thanks

Email a worksheet
 

Ron,
It worked...thanks a lot..You have made my life easy.. I appreciate it

Hyderabadwala

Email a worksheet
 

Hi

If you copy as values then there are no links anymore in the worksheet?
Maybe a range name?

Send me a copy (private)of the sheet where you use the PasteSpecial code
I take a look at it then


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"hyderabadwala" wrote in message
...
Ron,
I thought the "ask to update automatic links" was taken care..but unfortunately I am still getting the error message. Any idea
why?

Thanks

Hyderabadwala

Email a worksheet
 

Copy this sheets also in the new workbook if you want them to work

Like this
Sheets(Array("Sheet1", "Sheet3")).Copy

Use

Use this then to do a pastespecial

Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"hyderabadwala" wrote in message
...
Hi,
The following is the code that i am using to copy and paste the worksheet. I even wanted to bring to your notice that the
worksheet not only has data, it also has some charts based on a different worksheet in the same workbook and also has a couple
of linked tables from a different worksheet in the same workbook.

Recip = Array(inpresp) '
Worksheets("Report Out Pitch").Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
'Cells(1).Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=filename & ".xls"
ActiveWorkbook.SendMail Recipients:=Recip, Subject:=filename & "YTD Report"
ActiveWorkbook.Close
Application.DisplayAlerts = True

Let me know if you need more information.

Thanks

Hyderabadwala