chris pike
asked on
Excel VB - format error
I am getting an error on this line in my VBA
the error that highlights is the "FORMAT"
Any idea why? Thanks so much
Chris
For Each rngSupervisor In Range("tblSupervisor[Supervisor]")
sBody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & " - Please Recertify ASAP " & vbNewLine
the error that highlights is the "FORMAT"
Any idea why? Thanks so much
Chris
can you post the complete code?
there is no problem in the format
if you run that line as seperae procedure like this
sub test
Sbody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & " - Please Recertify ASAP " & vbNewLine
MsgBox Sbody
end sub
then you get no problem
there is no problem in the format
if you run that line as seperae procedure like this
sub test
Sbody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & " - Please Recertify ASAP " & vbNewLine
MsgBox Sbody
end sub
then you get no problem
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Complete Sub
Line 17 is the error
Line 17 is the error
Sub RunExpReport()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
Dim rng As Range
Dim rngSupervisor As Range
Dim DaysBefore As Long
Dim TodayLong As Long
Dim sBody As String
Dim OutApp As Object
Dim OutMail As Object
TodayLong = Now
DaysBefore = Range("DaysBeforeExp")
For Each rngSupervisor In Range("tblSupervisor[Supervisor]")
sBody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & " - Please Recertify ASAP " & vbNewLine
'Filter exp date table
With Worksheets("ExpDate").ListObjects("TableExpDate").Range
.AutoFilter Field:=3, Criteria1:=rngSupervisor
.AutoFilter Field:=4, Criteria1:="<=" & TodayLong + DaysBefore
End With
Set rng = Range("TableExpDate[#All]").SpecialCells(xlCellTypeVisible)
If rng.Cells.Count > Range("TableExpDate[#Headers]").Cells.Count Then
Set OutApp = CreateObject("Outlook.Application")
sBody = sBody & vbNewLine & RangetoHTML(rng)
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = rngSupervisor.Offset(, 1)
.CC = ""
.BCC = ""
.Subject = "Expiration Date Report as of " & Format(Now, "yyyy/mm/dd")
.HTMLBody = sBody
.display
End With
End If
Next rngSupervisor
Worksheets("ExpDate").ListObjects("TableExpDate").Range.AutoFilter
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
ASKER
Roy was helping on this one, HE is away from his desk, need to get this one off my desk ASAP.
Thanks guys
Chris
Thanks guys
Chris
you can replace Format with this VBA.Format$
but the best solutuon is to find what library is "MISSING" in the reference
Have you checked for MISSING references under Tools>References?
but the best solutuon is to find what library is "MISSING" in the reference
Have you checked for MISSING references under Tools>References?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys, I have no idea about library's.
Appreciate your help.
Appreciate your help.
What error do you get?
Can you upload the workbook in question?