Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Send a Range in excel to a page via email (9.0.7616 sp-3)

    I am trying to send a range in excel to pagers via Outlook ( the range is sent in the body of the email) automatically. I am sending the pages manually now but would like to automate the process. The pages are sent each hour. The current code that I am using pastes the time of the last page over part of the data that I am trying to send. Any ideas about how to accomplish this?

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Send a Range in excel to a page via email (9.0.7616 sp-3)

    Could you run through the actions you take (step by step) when you do it manually. It could help some of the forum geniuses to assist in coding it 4 you if they know the method you are using currently! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Regards,
    Rudi

  3. #3
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send a Range in excel to a page via email (9.0.7616 sp-3)

    I have attached the code I am using. The code to send the email manually works fine but the one to repeat the process doesn't. The first part of the code sends the email to the page when ativated. The remaining code works to send the email automatically but overwrites part of the data in the range that I am sending.

  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: Send a Range in excel to a page via email (9.0.7616 sp-3)

    <P ID="edit" class=small>(Edited by JohnBF on 02-Sep-04 08:22. Oops, had beginning and end backwards.)</P>Since the Body of an Outlook e-mail is a single object, unlike a Word document, you can't add information in the middle of the message text without a lot of string coding. So without actually testing the code, at a glance it appears that new data added to the message Body is overwriting the entire existing message Body, so I'd recommend trying just this code change:

    <font color=blue>Sub Mail_Selection_Outlook_Body()</font color=blue>
    <etc>
    <font color=blue>With OutMail</font color=blue>
    <etc>
    change this:
    <font color=blue> .HTMLBody = RangetoHTML</font color=blue>
    to this for the new data to be at the beginning of the message
    <font color=blue> .HTMLBody = RangetoHTML & .HTMLBody</font color=blue>
    or this for the new data to be at the end of the message
    <font color=blue> .HTMLBody = .HTMLBody & RangetoHTML</font color=blue>

    If this doesn't help, post back to this thread.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send a Range in excel to a page via email (9.0.7616 sp-3)

    I understand the first part of the code overwrites the text each time. This is good as the range is a web based query that updates automatically. However, the second part of the code (the part that sets up the timing of the repeated execution) pastes a timestamp in the middle of the range each time it runs, overwriting the formulas in the cells. The timestamp is necessary to advance the macro to the next time to run. I would just like to have the timestamp put somewhere else in the worksheet and not sent with the data I'm trying to send. When the second part of the code selects A1 as the cell for the timestamp it selects the first cell in the range I'm trying to send (not A1).

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Send a Range in excel to a page via email (9.0.7616 sp-3)

    Instead of placing the timestamp in a location based on the activecell:

    ActiveCell.Offset(icount - 1, 0).Value = Format(Now(), "hh:mm:ss")

    Why not explicitly put it where you want it somewhere (eg in A150, change as desired):
    range("A150").Value = Format(Now(), "hh:mm:ss")

    Steve

  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: Send a Range in excel to a page via email (9.0.7616 sp-3)

    If Steve hasn't solved all the problem, he has solved part of the problem. The other part that may be causing difficulties is in:

    Function RangetoHTML()
    <etc>
    With ActiveWorkbook.PublishObjects.Add( _
    <etc>
    source:=Selection.Address, _
    <etc>

    I can't figure where Selection.Address is, though I suspect it is meant to be from

    Sub Mail_Selection_Outlook_Body()
    <etc>
    Range("l6:m30").Select
    Set source = Selection.SpecialCells(xlCellTypeVisible)

    and you should follow Steve's advice to make it explicit in Function RangetoHTML(), such as

    source:=Range("l6:m30").SpecialCells(xlCellTypeVis ible).Address, _

    OR, since in Mail_Selection_Outlook_Body() you set as a range name called source but never use it again, you could pass it to the RangetoHTML() Function such as

    Function RangetoHTML(rng as Range)
    <etc>
    source:=rng.Address, _

    Hope this makes sense.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send a Range in excel to a page via email (9.0.7616 sp-3)

    Thank you. This works well and solves my problem.

Posting Permissions

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