Learn MS Excel – Video 372- VBA-Paste Excel range in OUTLOOK body



In this video we are pasting the excel table as range in outlook body which can be copied or edited directly in the outlook body. Watch many more such amazing videos on this channel

22 thoughts on “Learn MS Excel – Video 372- VBA-Paste Excel range in OUTLOOK body

  1. Hi Ajay,
    I want to send a email to list of recipients. The email message body should consist of selective rows from excel worksheet. I have below data. I want to send email to listed email person with only specific rows on that person's name. For example, Send an email to shailesh@abcxyz.com with only first 4 rows, and so on for other users. I have thousands of such rows. Probably I might need a filter based selection. Can you please suggest?

    Type Number Name Version Owning Org. Primary Owner Email
    Forms SP00034-14 Supplier Form 5 Operations shailesh@abcxyz.com
    Document SP00034-15 Hand piece Z.1 Robotics shailesh@abcxyz.com
    Document 1 SP00034-16 Management Procedure 3 IT shailesh@abcxyz.com
    Form 1 SP00034-17 Computer Systems Form 2 IT shailesh@abcxyz.com
    Document WIP 0224 Hand piece X.1 Robotics david@abcxyz.com
    Document 1 9OSP8140 Management Procedure 6 Quality Michelle@abcxyz.com
    Form 9OSP8140F1 Computer Systems Form 2 7 Supply katherine@abcxyz.com

  2. Hi Ajay, This is absolutely fantastic, Thanks for knowledge sharing videos. I have a question that i have couple of Email id's like In cell A1,cell B1, Cell C1 , i want iall of them in "To" list in Vba . could you please suggest any idea.

  3. Hello,
    Thanks for the useful info. Can you help me with a problem. I am trying to use this coding and send multiple emails with different ranges. So that means the same format and same sheet but with different ranges.

    I have used a second macro which sends the different macro ranges. So that means one macro to send 4 other macros (with different ranges in each.) The only difference between the below and the other three is rangeastableA and "AL3:AO7"

    E.g.

    Sub Macro1()
    Application.Run "rangeastableA"
    Application.Run "rangeastableB"
    Application.Run "rangeastableC"
    Application.Run "rangeastableD"
    End Sub

    Each of the 4 macros are the same except they extract different info from different ranges inside the worksheet.

    The problem I am having is, Application.Run "rangeastableA" extracts the correct data, for the first email. But the other 3 macros as indicated above extracts blank data. What I need is for the information to be directed back into the first sheet. I presume the second, third and fourth macro is extracting data from new_wb. Can you please indicate what needs to be changed?

    Please Note : The only difference between the below and the other three is rangeastableA and "AL3:AO7"

    Sub rangeastableA()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim wb As ThisWorkbook, p As String, ws As Worksheet, rng As Range, new_wb As Workbook, rng2 As Range
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("sheet1")

    Set rng = Range("AL3:AO7")
    p = "C:Users" & Environ("username") & "Desktopmytest.html"

    Workbooks.Add
    Set new_wb = ActiveWorkbook

    'MsgBox Sheet1.UsedRange.Address

    ThisWorkbook.Activate
    rng.Copy
    new_wb.Activate

    ActiveCell.PasteSpecial xlPasteValues
    ActiveCell.PasteSpecial xlPasteColumnWidths
    ActiveCell.PasteSpecial xlPasteFormats

    'MsgBox new_wb.Sheets(1).UsedRange.Address

    new_wb.PublishObjects.Add(xlSourceRange, p, new_wb.Sheets(1).Name, new_wb.Sheets(1).UsedRange.Address, xlHtmlStatic).Publish (True)

    Dim readme As Variant
    '''''''''now we are going to put the html table data in a variable
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim final_file As Scripting.TextStream

    Set final_file = fso.OpenTextFile(p, ForReading)

    readme = final_file.ReadAll

    Dim o As Outlook.Application
    Set o = New Outlook.Application

    Dim omail As Outlook.MailItem
    Set omail = o.CreateItem(olMailItem)

    With omail

    .To = "ABC@gmail.com"
    .Subject = "Testing"
    .HTMLBody = " " & "<br>" & " " & "<br>" & "<table align = left >" & readme & "</table>"
    .Display
    .Send
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    final_file.Close
    Kill p

    End With

    End Sub

  4. Great video Ajay!  Thanks for all of your very helpful videos.  I like  how you summarize what we are doing in the beginning and end of the videos.  Great job!

  5. Hi Ajay, I have written below macro but I am getting run time error 1004 at "new_wb.PublishObjects.Add(xlSourceRange, p, new_wb.Sheets(1).Name, new_wb.Sheets(1).UsedRange.Address, xlHtmlStatic).Publish (True)" while running code.
    Please help.

    Sub rangetable()
    Dim wb As ThisWorkbook, p As String, ws As Worksheet, rng As Range, new_wb As Workbook, rng2 As Range
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Macro")

    Set rng = Range("a1:g13")
    p = "C:Uses" & Environ("Username") & "Desktop|mytest.html"

    Workbooks.Add
    Set new_wb = ActiveWorkbook
    ThisWorkbook.Activate
    rng.Copy
    new_wb.Activate

    'Range("A1").Select
    ActiveSheet.Paste
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    'ActiveCell.PasteSpecial Paste:=xlPasteFormats
    'ActiveCell.PasteSpecial.xlPasteColumnWidths

    MsgBox new_wb.Sheets(1).UsedRange.Address

    new_wb.PublishObjects.Add(xlSourceRange, p, new_wb.Sheets(1).Name, new_wb.Sheets(1).UsedRange.Address, xlHtmlStatic).Publish (True)

    Dim readme As Variant

    'Dim fso As Scripting.FileSystemObject
    'Set fso = New Scripting.FileSystemObject
    'Dim final_file As Scripting.teststream

    'Set final_file = fso.opentextfile(p, forReading)
    'readme = final_file.ReadAll
    'Dim o As outlook.Application
    'Set o = New outlook.Application
    'Dim omail As outlook.mailitem
    'Set omail = o.createitem(omailitem)
    'With omail

    '.To = "Vaibhav.keshari@tcs.com"
    '.Subject = "hi"
    '.HtmlBody = "Greetings" & "<br>" & "Please see below snapshot" & "<br>" & "<tabel align=left>" & readme & "</table>"
    '.display

    'Application.ScreenUpdating = True
    Kill p

    End Sub

  6. Hello Ajay,

    I am huge fan of your automation skills.

    Honestly saying those helped me alot in my task related to office.

    I need your help.

    I want to paste the pivot table with its user customized source color, source value format and source column width from excel worksheet to outlook email body as shown below .

    "Hi All,

    Please find the below Summary of Quality for the MTD Sept'17.".

    This is the exact place where i want to paste my pivot in editable format with its original customized formatting same as like excel worksheet.

    And please tell me how to move cursor which email body from left to right and vice versa and up to down and vice versa.

    I have searched on google but didn't get any relevant information.

    Hope you will help me.

    Regards,
    Aniket

  7. Hi Ajay,

    Looks pretty good but when we copied entire table to outlook the last row was not bordered. It wont allow us to border also. Could you please explain how we can border entire outlook table in one shot.

  8. Thank your effort to share your knowledge, Could you please guide us how to copy a table from outlook body and need to paste in excel with same format ?

  9. Hi Ajay

    this i really helpful video but I have lots of questions regarding this

    1.on daily basis i need to send data to client and angents if we use this code either i need to change the name of excel file evrytime or the data will get pasted on the same file in both cases it will be difficult

    2 as i said i need to share data to agents regarding there calls breaks n all other important stuffs for every 2 hours so every agent should received there own data only so is it possible tht i can create code using the above code

    please suggest

  10. Thank you so much Ajay!

    I was exactly looking for this code which can paste excel range in Outlook email. I am working on one file and if i need any help i will get back to you 🙂

  11. sir u right but this is rather a manual approach when I divide these numbers by any number but I want that the bar chart remains as long as the default size whether the number 500 or greater then 1000 it I don't want to merge the cells also please make a tricky I tried a lot I tried this one also that u mentioned

  12. sir I want to take advantage of repeat function in excel like repeat the "l" symbol near backspace and repeat it times depending upon a cell in range values 20.30.100.500 but there is a problem when I use this all works but cell having number 500 gets bigger enough to wider a particular column I want that the bar created by the repeat function remains in the column it does not let the column force to cross the default width which is 8.43 means to say bar chart must be fixed in a column by something tricky functions………so that value that is large rept function can adjust it in the column

Leave a Reply

Your email address will not be published. Required fields are marked *