Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    E-mail a workbook (Excel 2000)

    Please help me with the code to send a workbook via our default Novell GroupWise 6 e-mail.
    I tried to record the macro using the File > Sent to Option, but all it gave me was Application.Dialogs(xlDialogSendMail).Show
    Can anyone help??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-mail a workbook (Excel 2000)

    You can send any open workbook by using the SendMail method of the Workbook object. SendMail uses the default mail system on your PC. Examples:

    ActiveWorkbook.SendMail Recipients:="John Doe", Subject:="Quarterly Sales"

    Workbooks("Billable Hours.xls").SendMail Recipients:=Array("john@company.com", "mary@company.com")

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail a workbook (Excel 2000)

    Thanks again for the prompt reply and solution.

    Regards

  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: E-mail a workbook (Excel 2000)

    Here's a site with much information on Sendmail.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Location
    Sacramento, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail a workbook (Excel 2000)

    I used the code below to generate a 'Send mail' function on my computer. It works great. Then, I shared my file with other people (I have created a process for 21 locations throughout the state). My limited test run was to 3 locations...all of which use Groupwise and the process is failing at their centers (I have not been able to find a center with Outlook yet). I did, maybe, mess things up when I placed the line within a 'With' statement. But, I tested it inside and out of the 'with' statement and the Groupwise people get the same results - it fails. Any suggestions?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-mail a workbook (Excel 2000)

    Did you intend to post some code? I don't see any.

  7. #7
    New Lounger
    Join Date
    Nov 2004
    Location
    Sacramento, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail a workbook (Excel 2000)

    Sorry. This is the vesion with the command within the 'With' statement.
    For K = 1 To Count
    Application.DisplayAlerts = False
    Check = ActiveCell.Value
    If Range("G3") = Check Then
    ActiveCell.Offset(0, 1).Select
    Recipient = ActiveCell.Value
    MonthlyInvoice = "Monthly Invoice"
    Windows(Pop_file).Activate
    With Application
    .EnableEvents = False
    .DisplayAlerts = False
    ActiveWorkbook.SendMail Recipients:=Recipient, Subject:=MonthlyInvoice
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    Application.DisplayAlerts = False
    Workbooks(Pop_file).Close
    Range("A1").Select
    GoTo Endprocess
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Next K

    I have a separate file I call 'Email List' that this process is checking for a vendor number and that vendor's email address.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-mail a workbook (Excel 2000)

    You activate a window whose name is contained in the variable Pop_file, then close it. Do you open a lot of windows beforehand? Without information like that, it's impossible to tell what your code does.

    BTW Count is a bad name for a variable, since it is also a property of many things in VBA.

  9. #9
    New Lounger
    Join Date
    Nov 2004
    Location
    Sacramento, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E-mail a workbook (Excel 2000)

    I will change the 'count' use.

    The process is set up (in a rather sloppy manner) to allow a user to select up to 30 files and then places them on a sheet (I call 'Filelist') and opens ALL the files. Then, using a series of 'if/then' statements, selects 'filename1', processes it, including emailing it, then closes that file and Loops to change 'Pop_file' to 'Filename2' and retry the process. For the groupwise users, I suggested that they start with no more than 4 files. Again, it works for me with outlook. I can include the whole code, if you like.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-mail a workbook (Excel 2000)

    I don't have GroupWise, so I wouldn't be able to test it. Perhaps someone with GroupWise will have a suggestion.

Posting Permissions

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