Hi, I am new to VBA (but this forum) but am wondering how to set an email to send once the Ping Test has finished. I am trying to get the program to work so that after every 24 hours, it emails the address with the full finished ping test and values have been changed. Any help would be much appreciated

Here is my code so far:

Public PingSuccess As Boolean

Public RunWhen As Double
Public Const cRunIntervalhour = 24 ' 24 hours
Public Const cRunWhat = "TestPingNew" ' the name of the procedure to run



Function sPing(sHost) As String

Dim oPing As Object, oRetStatus As Object



Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & sHost & "'")
PingSuccessCount = 0
For Each oRetStatus In oPing
If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
sPing = "NO REPLY: Status code is " & oRetStatus.StatusCode
PingSuccess = False

Else
sPing = "Pinging " & sHost & " with " & oRetStatus.BufferSize & " bytes of data: "
sPing = sPing & "RESPONSE: Time (ms) = " & vbTab & oRetStatus.ResponseTime
sPing = sPing & " TTL (s) = " & vbTab & vbTab & oRetStatus.ResponseTimeToLive
PingSuccess = True

End If
Next
End Function




Sub StartTimer()
RunWhen = Now + TimeSerial(cRunIntervalhour, 0, 0)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TestPingNew()

Dim PingSuccessCount As Integer
For Each c In Worksheets("Sheet1").Range("C5:C50").Cells

PingSuccessCount = 0
If c <> "" And c.Offset(0, 2) <> "x" Then
IPaddress = c.Text

Range("RESPONSE") = ""
c.Offset(0, 1) = ""
For n = 1 To 3

Range("RESPONSE") = Range("RESPONSE") & vbCrLf & sPing(IPaddress)
If PingSuccess = True Then PingSuccessCount = PingSuccessCount + 1
c.Offset(0, 1) = PingSuccessCount / n
KillSomeTime

'MsgBox PingSuccess

Next n

End If
Next c


For Each c In Worksheets("Sheet1").Range("H5:H50").Cells

PingSuccessCount = 0
If c <> "" And c.Offset(0, 2) <> "x" Then
IPaddress = c.Text

Range("RESPONSE") = ""
c.Offset(0, 1) = ""
For n = 1 To 3

Range("RESPONSE") = Range("RESPONSE") & vbCrLf & sPing(IPaddress)
If PingSuccess = True Then PingSuccessCount = PingSuccessCount + 1
c.Offset(0, 1) = PingSuccessCount / n
KillSomeTime

'MsgBox PingSuccess

Next n

End If
Next c

For Each c In Worksheets("Sheet1").Range("M5:M50").Cells

PingSuccessCount = 0
If c <> "" And c.Offset(0, 2) <> "x" Then
IPaddress = c.Text

Range("RESPONSE") = ""
c.Offset(0, 1) = ""
For n = 1 To 3

Range("RESPONSE") = Range("RESPONSE") & vbCrLf & sPing(IPaddress)
If PingSuccess = True Then PingSuccessCount = PingSuccessCount + 1
c.Offset(0, 1) = PingSuccessCount / n
KillSomeTime

'MsgBox PingSuccess

Next n

End If
Next c


For Each c In Worksheets("Sheet1").Range("R5:R50").Cells

PingSuccessCount = 0
If c <> "" And c.Offset(0, 2) <> "x" Then
IPaddress = c.Text

Range("RESPONSE") = ""
c.Offset(0, 1) = ""
For n = 1 To 3

Range("RESPONSE") = Range("RESPONSE") & vbCrLf & sPing(IPaddress)
If PingSuccess = True Then PingSuccessCount = PingSuccessCount + 1
c.Offset(0, 1) = PingSuccessCount / n
KillSomeTime

'MsgBox PingSuccess

Next n

End If
Next c


For Each c In Worksheets("Sheet1").Range("W5:W50").Cells

PingSuccessCount = 0
If c <> "" And c.Offset(0, 2) <> "x" Then
IPaddress = c.Text

Range("RESPONSE") = ""
c.Offset(0, 1) = ""
For n = 1 To 3

Range("RESPONSE") = Range("RESPONSE") & vbCrLf & sPing(IPaddress)
If PingSuccess = True Then PingSuccessCount = PingSuccessCount + 1
c.Offset(0, 1) = PingSuccessCount / n
KillSomeTime

'MsgBox PingSuccess

Next n

End If
Next c


Range("RESPONSE") = "SCAN COMPLETED"

StartTimer ' Reschedule the procedure

End Sub

Sub KillSomeTime()

Dim PauseTime, Start
PauseTime = 0.5 ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

End Sub



Sub SendEmail()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
Set rng = Sheets("Sheet1").Range("B4:X32").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

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")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "random@random.com"
.CC = ""
.BCC = ""
.Subject = "Notification - "
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

Function RangetoHTML(rng As Range)
' Working in Office 2000-2007
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)
.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