Results 1 to 10 of 10

Thread: XL97

  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    XL97

    In 97, (how) can I program a button so a distributed spreadsheet I'm creating can be automagically e-mailed back to me (and only me) upon user completion? I know formulas but I don't know VBA. Users will be on an Outlook/Exchange platform. [img]/w3timages/icons/sick.gif[/img]
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL97 mail-to capability

    (This was meant to be the title of my post.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 mail-to capability

    1) In the worksheet where you want the button, right click on your toolbars and select Forms. Click on the Button, and place it where you want it

    2) The Assign Macro dialog box will appear. Click on New.

    3) In the subroutine Button1_Click that will be created for you place the following code:
    sub Button1_Click()

    Thisworkbook.SendMail "JohnBF",Thisworkbook.name & " completed by " & application.username

    End sub

    That worked here using Outlook Express. What happens under Exchange is anybody's guess (I'll find out on Monday, cos that's what we're migrating to)

    Jon

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL97 mail-to capability

    First test worked in Outlook, and I thank you greatly!
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL97 mail-to capability

    John, you clearly know what you are doing, and I clearly don't. May I impose on you for some additional help? Because I need to force a new name for the Workbook, here's what I have:

    <pre>Sub EMailSendButton_Click()
    Dim FN As String
    If Workbook_SheetChange = True Then
    Do
    FN = Application.GetSaveAsFilename
    Loop Until FN <> False
    ActiveWorkbook.SaveCopyAs Filename:=FN
    ThisWorkbook.SendMail "JohnDoe", ThisWorkbook.Name & " completed by " & Application.UserName
    Else
    MsgBox "No changes have been made to the Worksheet!"
    End If
    End Sub</pre>


    However running the code always returns the MsgBox, and I guess I'm misusing the WorkBook_SheetChange event. I don't need the macro to handle the event, the macro just needs to know if the sheet has changed at the time the button is clicked.

    Any help will be appreciated.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 mail-to capability

    I never claim to know what I'm doing. I've found that's usually about the time things start going wrong.

    Anyway, my humble suggestion for your problem would be
    Sub EMailSendButton_Click()
    Dim FN As String
    If ThisWorkbook.Saved = False Then
    FN = Application.GetSaveAsFilename
    If FN <> False then
    ThisWorkbook.SaveCopyAs Filename:=FN
    ThisWorkbook.SendMail "JohnDoe", ThisWorkbook.Name & " completed by " & Application.UserName
    else
    MsgBox "The workbook has not been sent"
    end if
    Else
    MsgBox "No changes have been made to the Worksheet!"
    End If

    End Sub

    The Saved property shows whether the workbook has been changed since it was last saved. Unfortunately, it can sometimes be set by just recalculating, which is a bit of a pain. I don't know of an alternative.

    I also took the (gross) liberty of rewriting the macro a bit. One of my pet hates is procedures that insist I do something, even if I may not want to. Looping round and round until the user enters a filename would really nark me, if I was the user. Ignore that change at your leisure if you wish.

    HTH

    Jon

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL97 mail-to capability

    Thank you! Mille gracie! Muchas gracias! Merci beaucoup! This was a great deal of help and has been plagiarized ruthlessly. You are light years ahead of me ... well, at least light months.

    To quote Blanche duBois: "I have always depended on the kindness of strangers"
    or to misquote Ringo: "I get by with a lotta help from my friends"
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 mail-to capability

    After effusive thanks like that, what else can I say other than you're welcome. If I'm ever in Salt Lake City or you're ever in Adelaide, you can buy me a beer.

    Jon

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 mail-to capability

    I've been trying to use .SendMail from Excel and am having some trouble. It works perfectly on Office2000/Win2000 machines but when a Win95/Office97 system runs it, it generates this error:
    Run-time error '1004':
    Method 'SendMail' of object '_Workbook' failed.

    I was sort of assuming that SendMail was an Office2000 feature that didn't work under Excel97 but this thread indicates otherwise. Any ideas what's up here?

    Thank you!
    Sue

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL97 mail-to capability

    I did see your thread, but if I was proficient enough to debug your code, I wouldn't have needed to post this question a while back. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    FWIW, here's a sniplet of my code, which is within a buttonclick, and works fine:

    If vbYes <> MsgBox("Do you want to e-mail the file now?", _
    vbYesNoCancel + vbQuestion, TITLE) Then
    Call UserCancelMsg(False, TITLE)
    Exit Sub
    Else: On Error Resume Next
    ThisWorkbook.SendMail "<person@validaddress>", _
    strFN & " completed by " & strAEName
    If Err = 0 Then
    MsgBox "File emailed to <person> as:" & vbLf & "
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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