Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Email from within Excel (Excel 2002 SP3)

    Hello everyone,
    I am trying to get an Excel macro to send an email automatically of a worksheet that gets data dumped into it and then saved as a tab delimited text file.
    So far I have managed to get the system to save the text file and attach it automatically to an outgoing email, but I then have to press the send button myself!
    I have tried using the following code to send the email, but due to the fact that the email system is the active app the send keys command does not work until I return to Excel, i.e. I press send myself!

    Sub SendMail()
    'attaches spreadsheet to email message ready for sending
    '
    Dim Subject As String

    Subject = "Stock Withdrawal for " & Sheets(1).Range("A1")

    Application.Dialogs(xlDialogSendMail).Show "email.name@domain.com.au", Subject
    'now send the mail by getting the system to press the Ctrl key along with the Enter key
    SendKeys "^{enter}"

    End Sub

    Any help would be greatly appreciated!
    Thanks in advance.
    Brian.

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

    Re: Email from within Excel (Excel 2002 SP3)

    If you want to use SendKeys, you should put the SendKeys instruction above the Applications.Dialogs(...) line, for SendKeys places the keystrokes into the keyboard buffer, to be handled by the next instruction(s).

    If you're using Outlook, you can also use Automation to control Outlook from Excel.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Re: Email from within Excel (Excel 2002 SP3)

    Thanks Hans for your tip.
    Tried putting the sendkeys command before the mail code, but it still made no difference.
    However... I have managed to get everything working by using something called CDO that I tracked down via google.
    Once again, thanks.
    Brian.

  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: Email from within Excel (Excel 2002 SP3)

    I'm guessing that because you used Application.Dialogs(xlDialogSendMail).Show in your code, you obtained it by recording a macro. If you delve into Excel VBA you'll find that it has a Sendmail method which automates the process without using SendKeys, and it's simpler than instancing Outlook or using CDO. Search the Excel Forum for a number of discussions on using SendMail, including how to send just a sheet or range, and see also Ron Debruin's Excel SendMail page.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from within Excel (Excel 2002 SP3)

    How does one retain the fomatting of the worksheet being e-mailed? I have text that is colored, bold and underlined.

    In using code from Ron Debruin's Excel SendMail; "Sending The Selection In The Body Of The Mail" it appears that the sheet is converted to HTML and then sent using CDO. Unfortunately formatting is not retained.

    Your assistance is appreciated,
    John

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

    Re: Email from within Excel (Excel 2002 SP3)

    Why don't you use SendMail or automate Outlook?

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from within Excel (Excel 2002 SP3)

    Hans,

    I will be running this off a server which does not have Outlook installed on it. I have tested it using CDO and it works just fine. My challenge is the formatting.

    Regards,
    John

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from within Excel (Excel 2002 SP3)

    Hans,

    I was able to retain formatting. Well I went another route by using a combination of CDO code from the previously mentioned link and HTML code.

    After the "With iMsg" I included a reference to ".HTMLBody=" and added the necessary HTML code for my message.

    Regards,
    John

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

    Re: Email from within Excel (Excel 2002 SP3)

    Glad you found a solution!

Posting Permissions

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