Pivot table in body of email - ron de bruin

[oplintx]

I AM TRYING TO PASTE A NAMED PIVOT TABLE (ENTIRE TABLE) AS A HTML TABLE IN A EMAIL. I CAN ONLY GET A RANGE TO PASTE IN THE EMAIL. DOES ANYONE KNOW HOW TO SELECT A NAMES PIVOT TABLE USING RON DE BRUIN CODE FOR EMAILING.

I AM ALSO WANT A RANGE OF CELLS TO BE INCLUDED BELOW THE PIVOT TABLE. ANY IDEAS.

Sub Mail_Selection_Range_Outlook_Body()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
    [U][/U][B]Set rng = Sheets("Anderson").Range("A3:I125").Value[/B]
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "scott.thompson@EMAIL.com"
        .CC = ""
        .BCC = ""
        .Subject = Sheets("Anderson").Range("AA5").Value
        .HTMLBody = RangetoHTML(rng)
        '& StrBody
        '.StrBody = Sheets("Anderson").Range("AA6:AA112").SpecialCells(xlCellTypeVisible)
        .Send   'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[deletedalien]

Will the pivot need to be functional after sending it? in other words, should the recipient be able to filter fields and such?

 

[oplintx]

It needs to be in a html format like when you select the table and paste it in the body of the email. It can't be a picture and wont be filterable....

any help would be great!

 

[deletedalien]

In that case i CAN help you

Just add another woorksheet and copy your whole pivot table and paste it on the new sheet BUT you will paste it as a picture hyperlink (its a picture that automatically updates as your data does)

i have done this in the past, coincidentally enough with Ron's method and worked like a charm Just make sure that your pivot is formatted to PERFECTION and that you get rid of grid-lines as any minor detail will show up on your recipients inbox.

 

[deletedalien]

Actually i just found an ooooooold file that does exactly this (not a pivot but same method) i can share it with you if you'd like

 

[oplintx]

Yes, please do share but I want to avoid coping the pivot table to a new sheet. I want to extract it from the existing sheet, because I am eliminating everything that can be automated. I have about 20 tabs, each with pivots that I will modify the code to email customers pricing. I have everything I need but coping the pivot table by name, and getting it into the email to send...

 

[deletedalien]

hmm.... in that case i dont know if that might help....

eitherway here is my file:

https://onedrive.live.com/redir?resid=f92e790861feab0c!255&authkey=!AO4wgLOiOOpey2Y&ithint=file,xls



i just realized that there are unsupported features in the web version.....

Maybe if you saved it to your one drive and then download it from your one drive?

 

[deletedalien]

try this:

https://www.dropbox.com/s/rngdquskmvi9e8d/NEW Hourly Productivity Scores 4.0.xls?dl=0