Email a worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 (e-mail address removed)) 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
 
Hi

Use a button from the control toolbox on your sheet
and place your code in the click event of the button

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




hyderabadwala said:
Hi,
I have a requirement of having a "Email Report" button on a worksheet. This button basically emails the active worksheet to
Click to expand...
the reciepents. I was able to get this feature to work but with some issues. The code that i am using is
 
Hi Ron
Thanks for the advice..it worked. But i am still getting the "automatic link" message, how do i fix it

Thanks
 
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
 
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
 
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)




hyderabadwala said:
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
Click to expand...
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.
 
Back
Top