Hi,
I use an email macro for most of my reports that references a table which contains all my contact details and email content references.
I use a google'd Function to do this, so unfortunately only half understand it.. Therefore hoping someone can help me with a question!
The biggest limitation I come across with this, is that the Range I give in my Range column, only works if it's on the same sheet as my table, I can't seem to get it to look at another sheet.
Anyone know how I can input the range into the cell so that the macro will pick it up from another tab? Eg, if for the first person in my table, I wanted to copy a table on Sheet "Weekly Summary" Range "A1:C5". This range will change on every line so can't put a fixed reference directly into my macro
I use this table as for most reports i run, it goes to 50-60 individual people to show just their specific data
I use an email macro for most of my reports that references a table which contains all my contact details and email content references.
I use a google'd Function to do this, so unfortunately only half understand it.. Therefore hoping someone can help me with a question!
The biggest limitation I come across with this, is that the Range I give in my Range column, only works if it's on the same sheet as my table, I can't seem to get it to look at another sheet.
Anyone know how I can input the range into the cell so that the macro will pick it up from another tab? Eg, if for the first person in my table, I wanted to copy a table on Sheet "Weekly Summary" Range "A1:C5". This range will change on every line so can't put a fixed reference directly into my macro
I use this table as for most reports i run, it goes to 50-60 individual people to show just their specific data
Name | To | Cc | Subject | Name | File Path | Range |
VBA Code:
t = ws.Range("h" & i).Value
Set rng = ActiveSheet.Range(t).SpecialCells(xlCellTypeVisible)
With OutMail
.To = ws.Range("b" & i).Value
.CC = ws.Range("c" & i).Value
.Subject = ws.Range("d" & i).Value
.HTMLBody = ws.Range("f" & i).Value & RangetoHTML(rng)
.Display
End With
Code:
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Range("A1").PasteSpecial Paste:=8
.Range("A1").PasteSpecial xlPasteValues, , False, False
.Range("A1").PasteSpecial xlPasteFormats, , False, False
.Range("A1").Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function