Table showing #### when copied

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
21
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I've got a macro to generate emails that includes copying data from my worksheet into the email body. Never had any issues with it until the report I'm currently working on, and now for some reason one of the columns keeps getting pasted as #### instead of the value??

1620921018965.png

1620921000388.png


On my spreadsheet it all shows fine, no issues with the formatting, so it's happening when being copied, and only with the one column..
I tried adding "Cells.EntireColumn.AutoFit" for when it copies into a temp file, but even that didn't fix it

The macro references a table which contains all my contact details - this is the "ws" referenced throughout

Any ideas why this column is getting squashed?

1620921178706.png


VBA Code:
SheetName = ws.Range("a" & i).Value
SheetNameColumns = Sheets(SheetName).Cells(1, Columns.Count).End(xlToLeft).Column
SheetNameRows = Sheets(SheetName).Range("a" & .Rows.Count).End(xlUp).Row
ColumnLetter = Split(Cells(1, SheetNameColumns).Address, "$")(1)


ws.Range("h" & i) = "A1" & ":" & ColumnLetter & SheetNameRows


t = ws.Range("h" & i).Value
            
Set rng = Nothing

On Error Resume Next

Set rng = Sheets(SheetName).Range(t).SpecialCells(xlCellTypeVisible)

On Error GoTo 0
       
            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 & RangetoHTML2(rng)
                .Save
            End With


        Next i
    End With

VBA Code:
Function RangetoHTML2(rng As Range)
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)
RangetoHTML2 = ts.readall
ts.Close
RangetoHTML2 = Replace(RangetoHTML2, "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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,878
Office Version
  1. 2010
Platform
  1. Windows
What do you see on the address bar if you click any one of those #### cells? The address bar will show you the value stored in that cell regardless of the format.

If you manually widen the column, say, double the width, what will you see?
 

Forum statistics

Threads
1,147,822
Messages
5,743,400
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top