Question regarding ranges

[ChrisM92]

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

Name

To

Cc

Subject

Name

Email

File Path

Range

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

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

 

[Norie]

Do you have a column for the sheet?

 

[RoryA]

Try using Application.Range(t)

 

[ChrisM92]

Do you have a column for the sheet?

Name is Column A, across to Range which is Column H