Creating Notification Email with VBA and inserting range as table in email body to recipient listed in column

TXRChav · Jun 17, 2016

I have a spreadsheet with recipients name in column A, recipients email in column B and mulitple others columns with the information to be emailed to these recipients. Each recipient has multiple rows, and the number of rows per recipient varies each time. The number of recipients also varies.
What I would like to do is create only one email for each recipient and include the other data columns relevant to that recipient as a table at the end of the body of the email. All emails would have the same text in the body of the email which would be stored in the code and not in the spreadsheet.
Any help would be appreciated. This is my first time dealing with outlook through Excel VBA.
Thanks

Rijnsent · Jun 21, 2016

Hi TXRChav,
that sounds like a good challenge for a first time Excel->Outlook VBA project. Could you please post your code here (in [ CODE ] brackets) as that would help giving you feedback? As a starter, the site of Ron de Bruin has some good starting points for VBA mailing from Excel: Mail from Excel and make/mail PDF files (Windows)
Cheers,
Koen

TXRChav · Jun 21, 2016

Hi Rijnsent,

Thanks I used some of Ron de Bruin's code along with another suggestion to "Add a reference to outlook library in VBA (in the toolbar ->Tools->References-Microsoft Outlook)" from Sgdva at StackOverflow to come up with the following solution:

This sub sets up my data and is not very relevant for the answer, but might be of use to someone.

Code:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Sub[/COLOR][COLOR=#303336] Related_BA[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] filename [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] returnVAlue [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] BAwb [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] BAws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] BArng [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] LastRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveWorkbook
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Super User Report"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

filename [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]GetOpenFilename[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]filefilter[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]"Excel Files (*xls), *xls"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Title[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]"Please select BA refernce file"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] filename [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]

ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A:B"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Insert

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] BAwb [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Workbooks[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Open[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]filename[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] BAws [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] BAwb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Sheet1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] BArng [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] BAws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ListObjects[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"DepartmentBA"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]DataBodyRange

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"BA"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]HorizontalAlignment [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlCenter
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Font[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Bold [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"BA Email"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]HorizontalAlignment [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlCenter
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Font[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Bold [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

LastRow [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"C1"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]CurrentRegion[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count

[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] LastRow
    ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]VLookup[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] BArng[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] i

[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] LastRow
    ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]VLookup[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] BArng[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] i

BAwb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]

ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A:B"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]AutoFit

ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B2"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]CurrentRegion[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sort key1[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B2"[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] order1[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlAscending[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
    key2[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"C2"[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] order2[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlAscending[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Header[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlYes

[/COLOR][COLOR=#101094]Call[/COLOR][COLOR=#303336] SendEmail

ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A:B"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Delete


[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub[/COLOR][FONT=Verdana]

[/FONT]

</code>This formats the data for the email and calls the email function. I still might need code to handle #N/A from the vlookup.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">

Code:

[/FONT][COLOR=#101094]Sub[/COLOR][COLOR=#303336] SendEmail[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] cBA [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Collection
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] cell [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] vNum [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] lRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveWorkbook
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Super User Report"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
lRow [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]"A"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A2:A"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] lRow[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] cBA [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#101094]New[/COLOR][COLOR=#303336] Collection

[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] cell [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells
        cBA[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Add cell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336],[/COLOR][COLOR=#101094]CStr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]cell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] cell
[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
cBA[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Remove [/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"None"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Super User Report"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]AutoFilterMode [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] vNum [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] cBA
    rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]AutoFilter Field[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Criteria1[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]vNum
    [/COLOR][COLOR=#101094]Call[/COLOR][COLOR=#303336] Email[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]vNum[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]AutoFilter Field[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] vNum


[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub[/COLOR]</code>

This sube actually creates and sends the email.

Code:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]
Sub[/COLOR][COLOR=#303336] Email[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]BA [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] lRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] StrBody [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] OutApp [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] OutMail [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Mnth [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Yr [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Variant[/COLOR][COLOR=#303336]

StrBody [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"This is line 1"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"
"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
          [/COLOR][COLOR=#7D2727]"This is line 2"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"
"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] _
          [/COLOR][COLOR=#7D2727]"This is line 3"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"


"[/COLOR][COLOR=#303336]


Mnth [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Month[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#7D2727]"mmmm"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
Yr [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveWorkbook
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Super User Report"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
lRow [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]"A"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'Only the visible cells in the selection[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"C1:L"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] lRow[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]SpecialCells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlCellTypeVisible[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'You can also use a fixed range if you want[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] rng [/COLOR][COLOR=#101094]Is[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Application
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]EnableEvents [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlDiagonalDown[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlDiagonalUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeLeft[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeTop[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeBottom[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeRight[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlInsideVertical[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlInsideHorizontal[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlContinuous
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ColorIndex [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]TintAndShade [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Weight [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlThin
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] OutApp [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Outlook.Application"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] OutMail [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] OutApp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]CreateItem[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

Mnth [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Month[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#7D2727]"mmmm"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
Yr [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]Date[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] OutMail
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] BA
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]CC [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]BCC [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Subject [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"Monthly Super User Report "[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Mnth [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Yr
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]HTMLBody [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] StrBody [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] RangetoHTML[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]rng[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Display   [/COLOR][COLOR=#858C93]'or use .Send[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]

rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlDiagonalDown[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlDiagonalUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeLeft[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeTop[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeBottom[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlEdgeRight[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlInsideVertical[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Borders[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlInsideHorizontal[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]LineStyle [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] xlNone

[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Application
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]EnableEvents [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] OutMail [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] OutApp [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub[/COLOR]</code>

This function is referenced in the sub above.

Code:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Function[/COLOR][COLOR=#303336] RangetoHTML[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]rng [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] fso [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ts [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] TempFile [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] TempWB [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook

TempFile [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Environ[/COLOR][COLOR=#303336]$([/COLOR][COLOR=#7D2727]"temp"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Format[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]"dd-mm-yy h-mm-ss"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]".htm"[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'Copy the range and create a new workbook to past the data in[/COLOR][COLOR=#303336]
rng[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] TempWB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Workbooks[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Add[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] TempWB[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]PasteSpecial Paste[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]PasteSpecial xlPasteValues[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]PasteSpecial xlPasteFormats[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
    Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]CutCopyMode [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]Resume[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DrawingObjects[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Visible [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DrawingObjects[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Delete
    [/COLOR][COLOR=#101094]On[/COLOR][COLOR=#101094]Error[/COLOR][COLOR=#101094]GoTo[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'Publish the sheet to a htm file[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] TempWB[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]PublishObjects[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Add[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336] _
     SourceType[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlSourceRange[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
     filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]TempFile[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
     Sheet[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]TempWB[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Name[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
     Source[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]TempWB[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]UsedRange[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Address[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
     HtmlType[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlHtmlStatic[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Publish [/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'Read all data from the htm file into RangetoHTML[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] fso [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Scripting.FileSystemObject"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ts [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] fso[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]GetFile[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]TempFile[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]OpenAsTextStream[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
RangetoHTML [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ts[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]readall
ts[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close
RangetoHTML [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Replace[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]RangetoHTML[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]"align=center x:publishsource="[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _
                      [/COLOR][COLOR=#7D2727]"align=left x:publishsource="[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'Close TempWB[/COLOR][COLOR=#303336]
TempWB[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close savechanges[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'Delete the htm file we used in this function[/COLOR][COLOR=#303336]
Kill TempFile

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ts [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] fso [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] TempWB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]Nothing[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Function

[/COLOR]</code>

I hope this is useful to someone.

Creating Notification Email with VBA and inserting range as table in email body to recipient listed in column

TXRChav

New Member

Rijnsent

Well-known Member

TXRChav

New Member

Similar threads

Share this page

Creating Notification Email with VBA and inserting range as table in email body to recipient listed in column

TXRChav

New Member

Rijnsent

Well-known Member

TXRChav

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock