Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy, then email workbook without macro attached

  Asked By: Chisisi    Date: Sep 01    Category: MS Office    Views: 910
  

I have a workbook with several worksheets and a macro. The macro,
among other things, emails itself to people. Because I don't want to
email a spreadsheet with a macro attached, it creats a copy of
itself, emails that copy, and finally deletes that copy. The
problem is when the receipient opens the emailed spreadsheet, they
get a linking error because some of the spreadsheets contain graphs,
and those graphs are still linking back to the original spreadsheet
as the data source. Is there a way around this, or should I stop
wasting my time?


Code to email workbook:
Private Sub emailSpreadsheet()

ActiveWorkbook.Worksheets.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Name & " Email version"
ActiveWorkbook.SendMail "xxxxxxx@...", _
"Testing"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Fatih Farooq     Answered On: Sep 01

I think the problem may be that you are saving the active workbook  with a
different name - the equivalent of Save As. You could try copying the file
before opening and making the changes. I think this way the file will maintain
"internal" links.

 
Didn't find what you were looking for? Find more on Copy, then email workbook without macro attached Or get search suggestion and latest updates.




Tagged: