Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Sending sheet via e-mail

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Iím using Excel 2000 and would like to sent one sheet only. I found this code (for Excel 2002) when searching but I get an error message on the 3rd line. This is exactly what I need. Can you help this novice? Thanks.

    sheets("sheet1").copy
    activeworkbook.sendmail
    recipients:="email_address@hotmail.com", Subject:="Testing"

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bob,

    This code will work fine in Excel 2000 but you need to include the Recipients:= bit on the same line as the ActiveWorkbook.SendMail i.e.

    Sheets("sheet1").Copy
    ActiveWorkbook.SendMail Recipients:="email_address@hotmail.com", Subject:="Testing"

    Recipients and Subject are arguments of the SendMail method.

    HTH,
    Dan

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I copied the code and got it to work. I do know that the 3rd line should be a continuation of the 2nd line (i.e. there should be no 3rd line). Not sure if this is how you intended to post it, or if is just a function of this message board system.

    K

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there friend

    maybe you should try this code

    Sub anothermailAP()

    Dim filenam As String
    Dim ol As Object
    Dim mailitem As Object
    Dim Subj As String
    Dim Month As String
    filenam = Application.ActiveWorkbook.FullName
    Set ol = CreateObject("Outlook.Application")
    Set mailitem = ol.CreateItem(olMailItem)

    ' Gets the name of the e-mail recipient.
    Recip = ""

    ' Creates the subject line.
    Month = Cells(5, 2)
    Subj = Month & ""

    ' Creates the e-mail message text.
    messg = "Attached are the worksheet you requested for " & Month & "." & vbCrLf & vbCrLf & _
    "Please review them and let me know about any changes that are made."

    ' Sets all items in the e-mail.
    With mailitem
    .To = Recip
    .Subject = Subj
    .Body = messg
    .Attachments.Add filenam
    .Display
    End With

    End Sub

    Try This,,it send a whole worksheet away to recipient.


  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can I send it to Outlook Express instead of Outlook? What do I change in " createobject("Outlook.Application") " ?

    I was looking for the Outlook Express Object Model in MSDN but no luck. Anybody know where I can find it?

    Thanks



  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-14 00:15, Goofydisney wrote:
    Can I send it to Outlook Express instead of Outlook? What do I change in " createobject("Outlook.Application") " ?

    I was looking for the Outlook Express Object Model in MSDN but no luck. Anybody know where I can find it?
    The OE object Model is not well supported
    In fact I don't beleive the Coms is exposed
    for manipulation.

    Is OE your default emailer ??
    If not then try resetting your default
    mail prg in Internet setup to OE.
    Thanks





    _________________
    Kind Regards,
    Ivan F Moala
    Have a Nice day

    [ This Message was edited by: Ivan F Moala on 2002-05-14 01:36 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can I send it to Outlook Express instead of Outlook? What do I change in " createobject("Outlook.Application") " ?

    I was looking for the Outlook Express Object Model in MSDN but no luck. Anybody know where I can find it?

    Thanks
    Hi,

    Unfortunately Outlook Express is not an automation server and as such doesn't expose an object model. You won't be able to use code for OE in the same way you can for Outlook. There are samples around for automatically sending mail from OE but I don't think they're as reliable or as neat as code you can create with Outlook - e.g. I think they all rely on the Sendkeys method to actually send the message.

    Regards,
    Dan

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-14 01:05, dk wrote:
    Can I send it to Outlook Express instead of Outlook? What do I change in " createobject("Outlook.Application") " ?

    I was looking for the Outlook Express Object Model in MSDN but no luck. Anybody know where I can find it?

    Thanks
    Hi,

    Unfortunately Outlook Express is not an automation server and as such doesn't expose an object model. You won't be able to use code for OE in the same way you can for Outlook. There are samples around for automatically sending mail from OE but I don't think they're as reliable or as neat as code you can create with Outlook - e.g. I think they all rely on the Sendkeys method to actually send the message.

    Regards,
    Dan
    I think your right Dan...most if not All rely
    on some sought of Send Keys...
    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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