Hello, I have been working on a workbook where i am checking on a column to see if the items expiry date is less than 30 days and if yes, then i am sending notification emails. But, when there is no items to be renewed in 30 days the program is throwing error telling " Runtime Error -2147467259 (80004005) - We need to know who to send this to. Make sure you enter atleast one name". The code what i have written is as below:
Sub SendEmail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(8))
If MailDest = "" And Cells(iCounter, 8).Offset(0, -1) = "Renew" Then
MailDest = Cells(iCounter, 8).Value
ElseIf MailDest <> "" And Cells(iCounter, 8).Offset(0, -1) = "Renew" Then
MailDest = MailDest & ";" & Cells(iCounter, 8)
End If
Next iCounter
.BCC = MailDest
.Subject = "FYI"
.Body = "Remainder mail to Renew. Please ignore if already Renewed"
.Send
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Kindly let me know what is the mistake i have made so that the program runs fine even though there is no item to be renewed in my sheet.
K
Sub SendEmail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(8))
If MailDest = "" And Cells(iCounter, 8).Offset(0, -1) = "Renew" Then
MailDest = Cells(iCounter, 8).Value
ElseIf MailDest <> "" And Cells(iCounter, 8).Offset(0, -1) = "Renew" Then
MailDest = MailDest & ";" & Cells(iCounter, 8)
End If
Next iCounter
.BCC = MailDest
.Subject = "FYI"
.Body = "Remainder mail to Renew. Please ignore if already Renewed"
.Send
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Kindly let me know what is the mistake i have made so that the program runs fine even though there is no item to be renewed in my sheet.
K