Results 1 to 19 of 19

Thread: [RESOLVED] Send table on email

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Resolved [RESOLVED] Send table on email

    Hello.

    Need to attach this table to an outlook email.

    Subject also needs to read.

    "Please find enclosed yesterdays" and showing date in dd-mmm-yy format.


    Name:  Capture 3.jpg
Views: 415
Size:  54.3 KB

    Thanks
    Attached Images Attached Images  

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    if word is your email editor you can paste the table into word
    otherwise you need to build the table as an html string
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Hello,

    Don’t want it on word. Needs to be pasted onto an email and to a set group of people

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Hello,

    Don’t want it on word. Needs to be pasted onto an email and to a set group of people

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    afaik you can not paste into an outlook inspector using code, it may be possible using a bunch of API calls, but it is not something i have done, or is available within the outlook object model

    how is your skill with html code?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Not great... :-)

    I did some work on this last night and came up with the below

    Its coming up with an error message which I cant seem to fix.


    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    Compile error:

    Sub or Function not defined
    ----------

    RangetoHTML - This is the line it breaks on.


    VB6 Code:
    1. Dim rng As Range
    2.     Dim OutApp As Object
    3.     Dim OutMail As Object
    4.     Set rng = Nothing
    5.     On Error Resume Next
    6.    
    7.    
    8.     Set rng = Selection.Range(xlCellTypeVisible)
    9.     t
    10.  
    11.     Set rng = Sheets("Summary").Range("B4:L34").SpecialCells(xlCellTypeVisible)
    12.     On Error GoTo 0
    13.     If rng Is Nothing Then
    14.         MsgBox "The selection is not a range or the sheet is protected" & _
    15.         vbNewLine & "please correct and try again.", vbOKOnly
    16.         Exit Sub
    17.     End If
    18.     With Application
    19.         .EnableEvents = False
    20.         .ScreenUpdating = False
    21.     End With
    22.     Set OutApp = CreateObject("Outlook.Application")
    23.     OutApp.Session.Logon
    24.     Set OutMail = OutApp.CreateItem(0)
    25.     On Error Resume Next
    26.     strHtml = "<html>" & "<body>" & "Hi All," & "<br>" & "</br>" & "</body>" & "</html>"
    27.          
    28.     With OutMail
    29.         .To = ""
    30.         .CC = ""
    31.         .Subject = "Test Mail"""
    32.         .HTMLBody = strHtml & RangetoHTML(rng)
    33.         .Display
    34.     End With
    35.     On Error GoTo 0
    36.     With Application
    37.         .EnableEvents = True
    38.         .ScreenUpdating = True
    39.     End With
    40.     Set OutMail = Nothing
    41.     Set OutApp = Nothing
    42.        ActiveWorkbook.CheckCompatibility = False
    43.   ActiveWorkbook.Save
    44. End Sub
    Last edited by FunkyDexter; May 24th, 2019 at 02:39 AM.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    .HTMLBody = strHtml & RangetoHTML(rng)
    this would add the table after end of the html body

    do you have some function that converts a range of cells to html?

    ELSE
    you can not assign a range to the string, you would need to get the value from each cell in the range and insert as a able with <TR anf <TD for rows and columns, plus any formatting required for text, alignment or cell colour

    Its coming up with an error message which I cant seem to fix.
    that is terrible, i wonder what the error could be, and where it would occur
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Sorry never done this type of coding before.

    Why do I need to convert a range? I only want to copy it the range specified and paste into an email.


    .HTMLBody = strHtml & RangetoHTML(rng)
    this would add the table after end of the html body

    do you have some function that converts a range of cells to html?

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    I only want to copy it the range specified and paste into an email.
    as stated before, i do not believe you can post into an email body by code,
    you could copy the range to the clipboard, create an email then display it for the user to manually paste the clipboard content to the email, but i have never done it that way

    do you have some function that converts a range of cells to html?
    while it would not be too hard to do, i am sure there could
    be some already available, a quick search brought up many hits, try rondebruin.nl, but his site is down at the momont
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Send table on email

    Code tags added
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    gibra
    Guest

    Re: Send table on email

    Quote Originally Posted by kris01 View Post
    Sorry never done this type of coding before.
    See if this help you
    VBA - Copy From Excel into Outlook
    https://www.mrexcel.com/forum/excel-...o-outlook.html

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    ok, i tested this it worked for me, but i make no promises
    Code:
    Set ol = GetObject(, "outlook.application")
    Set msg = ol.createitem(0)
    msg.Subject = "Please find enclosed yesterdays " & Format(Date - 1, "dd-mmm-yy ")
    msg.to = "me"
    msg.htmlbody = "hi all <br><br>"
    msg.display
    Range("a1:d20").Copy    ' change range to suit
    
    Application.Wait DateAdd("s", 2, Now)
    SendKeys "{tab}", True
    SendKeys "{DOWN}", True
    SendKeys "~", True
    SendKeys "%{e}p", True
    msg.send
    Application.CutCopyMode = False
    if sendkeys gives a permission denied error message, try application.sendkeys, or wscript.shell sendkeys method
    Last edited by westconn1; May 24th, 2019 at 06:24 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Thanks for doing this. Ive learnt so much from this thread.


    What line do I add If i want to select a range on a particular worksheet - Worksheet "Summary"?

  14. #14
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Send table on email

    Code:
    Range("a1:d20").Copy    ' change range to suit
    You would qualify this, something like:

    Code:
    wsSummary.Range("a1:d20").Copy    ' change range to suit
    You'd need to set wsSummary first.

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    What line do I add If
    change Range("a1:d20").Copy ' change range to suit

    to
    Code:
    sheets("Summary").range("b4:l34").copy
    or whatever range of cells

    also i changed the date in the code to be yesterdays, rather than todays
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    Hello.

    Nearly there.


    It brings up the email with the various components.... Subject etc etc

    But not the table.

    Name:  Capture 5.jpg
Views: 315
Size:  28.6 KB

    Here's my code, just in case I've missed anything.....

    Sub snwb()

    Set ol = GetObject(, "outlook.application")
    Set msg = ol.createitem(0)
    msg.Subject = "Please find enclosed yesterdays " & Format(Date, "dd-mmm-yy ")
    msg.to = "me"
    msg.htmlbody = "hi all <br><br>"
    msg.display
    Sheets("Summary").Range("b4:l34").Copy

    Application.Wait DateAdd("s", 2, Now)
    SendKeys "{tab}", True
    SendKeys "{DOWN}", True
    SendKeys "~", True
    SendKeys "%{e}p", True
    msg.display
    Application.CutCopyMode = False

    End Sub

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    i made some small change

    Code:
    Set ol = GetObject(, "outlook.application")
    Set msg = ol.createitem(0)
    msg.Subject = "Please find enclosed yesterdays " & Format(Date, "dd-mmm-yy ")
    msg.to = "me"
    msg.htmlbody = "hi all <br><br>"
    msg.display
    Range("a1:d20").Copy    ' change range to suit
    
    Application.Wait DateAdd("s", 2, Now)
    SendKeys "{tab}", True
    SendKeys "{DOWN}"
    SendKeys "~"
    SendKeys "%(ep)", True
    msg.send
    Application.CutCopyMode = False
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Send table on email

    are missing the sheet's "summary" reference from the above?

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Send table on email

    i don't have a sheet summary so i just test with the activesheet change to suit, the small changes to the code were in the sendkeys
    if you think that using the summary sheet might be a problem, test with some other range first, but i doubt it would make any difference

    i also did not include the edit i made to the date, as i only changed that in the previous post, not my sample code


    if it still not working, i will test again tonight on a later computer /office version, just in case there is any difference
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width