VBA Error wrong number of arguments or invalid property assignment

[Access Beginner]

Hello,


I've found this bit of code during my searches on the web and this does work for me in other spreadsheets however, when I try and run this in another workbook the following error is coming up
"wrong number of arguments or invalid property assignment"

The following is highlighted and the word "Format" is highlighted in yellow

TempFile = Environ$("temp") & "/" & Format(Today, "dd-mm-yy h-mm-ss") & ".htm"

Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Today, "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)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).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

Sub MailReports()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim rngAttach As Range
Dim hlink As String
    
    'Set range for file attachment, cell should contain the path and file name
    With ActiveSheet
    Set rngAttach = .Range("b5")
    End With
Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Sheets("Email_Reports").Range("b7:c70").SpecialCells(xlCellTypeVisible)
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)



With OutMail
    .SentOnBehalfOfName = ThisWorkbook.Sheets("Email_Reports").Range("d6").Value
    .To = ThisWorkbook.Sheets("Email_Reports").Range("d2").Value
    .CC = ThisWorkbook.Sheets("Email_Reports").Range("D3").Value
    .BCC = ThisWorkbook.Sheets("Email_Reports").Range("D4").Value
    .Subject = ThisWorkbook.Sheets("Email_Reports").Range("D5").Value
    .Display
    .HTMLBody = RangetoHTML(rng) 

    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    strFileName = Dir(rngAttach.Value)
               .Attachments.Add Replace(rngAttach.Value, "*.*", "")
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Rick Rothstein]

Today is an Excel function name, it is not a VBA function (it evaluates to Null if you don't assign a value to it). I think you would want to use Now in place of it...

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

 

[Access Beginner]

Hi Rick,

Thanks for your reply. I changed it to.... TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

And still get the same error

 

[Rick Rothstein]

Hi Rick,

Thanks for your reply. I changed it to.... TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

And still get the same error

Hmm, strange... I do not get an error with that code line. Given that, I am not sure what to tell you.

 

[RoryA]

I suspect that in that problem workbook you have used Format as the name of something, probably a procedure. If you amend the code to use VBA.Format instead of just Format, does it work?

 

[Access Beginner]

Thanks Rory, that was precisely the problem. Thanks for the help.