Consulting

Results 1 to 16 of 16

Thread: Solved: Email Workbook without macros

  1. #1

    Solved: Email Workbook without macros

    With the following code, does anyone have a solution to emailing the workbook without the embedded macro? There must be code to "SaveAs" without it.

    I'm not a fan of presenting the recipients with an "enable or disable macros" on receipt. TIA

    [vba]
    Dim strDate As String
    ActiveSheet.Copy
    strDate = Format(Date, "mm-dd-yy")
    ActiveWorkbook.SaveAs strDate & ".xls"
    ActiveWorkbook.SendMail "<recipient>", _
    "Daily Sales Report for " & strDate
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    [/vba]

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, UNET!
    Welcome to VBAX!

    When you put code, you can type [VBA] at the beginning of it, and again at the end, only this time with /VBA, and it'll format your code really cool, like it is now that I've edited it.

    As for the technical question, I'll let someone else answer that.
    ~Anne Troy

  3. #3
    ... making it more legible! Thanks.

  4. #4
    VBAX Regular
    Joined
    May 2004
    Location
    London
    Posts
    8
    Location
    Since you're only copying one sheet into the new workbook you're attaching, I assume the code you're trying to rid of is in that sheet's class module (event procedures and such)?

    I can think of two basic approaches.
    Either create a new empty workbook with just one sheet, and then copy over the worksheet's contents and formatting only, instead of copying the whole sheet.
    Or copy the sheet as you're doing now, and then delete the contents of its code module - examples here.

  5. #5
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I'm with herilane; just replace [vba]ActiveSheet.Copy[/vba] with [vba]ActiveSheet.Cells.Copy
    Workbooks.Add
    ActiveSheet.Paste[/vba]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  6. #6
    VBAX Regular
    Joined
    May 2004
    Location
    London
    Posts
    8
    Location
    Yes, the first one is definitely simpler, but I mentioned the second alternative in case there are charts or other objects in the worksheet, or if you want to make sure the the page setup or print area settings etc also get copied properly.

  7. #7
    VBAX Contributor Ivan F Moala's Avatar
    Joined
    May 2004
    Location
    Auckland New Zealand
    Posts
    185
    Location
    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    ActiveSheet.Cells.Copy
    Workbooks.Add
    ActiveSheet.Paste

    That worked great with the macro (which is all I ever asked about ). How 'bout the link info though?

    Is there a line of code that will remove any imbedded links on the paste also?

    Thanks for your assistance.

  9. #9
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    A link is something that is not embedded.
    Something that is embedded is not linked.

    But, presumably, you mean that some of your cells are linked to other worksheets or workbooks?

    Then, I think:

    [VBA]ActiveSheet.PasteSpecial Paste:=xlValues[/vba]
    ~Anne Troy

  10. #10
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Pasting values might have some other effects as well. Can you give us a bit more detail? Do you have hyperlinks, or formulae dependent on other workbooks, or what?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  11. #11
    VBA:

    I get a user defined error on "ActiveSheet.PasteSpecial Paste:=xlValues"

    As far as the links; It's a fairly simple spreadsheet that does a weekly tally based on daily date, so worksheet 7 eventually (week-end) contains data from worksheets 1-6.

  12. #12
    I got it.

    "Selection.PasteSpecial Paste:=xlValues", instead of "ActiveSheet.PasteSpecial Paste:=xlValues"

    Thanks for all your help.

  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    UNET: I'm going to mark this solved. Let me know if I'm wrong!
    ~Anne Troy

  14. #14
    VBA:

    Not wrong but this is even better (for my purpose anyway) - " Selection.PasteSpecial Links = False"

    [vba]
    Private Sub CommandButton1_Click()

    Dim strDate As String
    ActiveSheet.Cells.Copy
    Workbooks.Add
    Selection.PasteSpecial Links = False

    strDate = Format(Date, "mm-dd-yy")
    ActiveWorkbook.SaveAs strDate & ".xls"
    ActiveWorkbook.SendMail "<recipient>", _
    "Daily Sales Report for " & strDate
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False

    End Sub
    [/vba]
    So, a very (very, very) basic macro to send a worksheet sans all embedded macros or links.

    Thanks again to a great board!

  15. #15
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Glad you like it!
    We're not even a month old!
    Don't forget to play Pacman, hee hee.
    ~Anne Troy

  16. #16
    VBAX Newbie
    Joined
    Nov 2005
    Location
    Hampstead, NH
    Posts
    1
    Location
    Could someone tell me how to format multiple recipients using sendmail from script? - have tried many methods without success. thanks.

    Richard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •