Invalid procedure call or argument (Run-time error '5')

[Mindy2017]

Can somemone review this code and see why I am getting an invalid procedure call argument message (the text in red is what is being highlighted in VB). Thank you in advance!

Sub datesexcelvba()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim icounter As Integer
Dim maildest As String


Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long


Dim rownum As Long
Dim mystr As String


rownum = 2
Do Until Cells(rownum, 2).Value = ""
If Cells(rownum, 2).Value <= Date + 14 And Cells(rownum, 4).Value = "Send Reminder" Then                 ''change made here
mystr = mystr + Cells(rownum, 1).Value & ", "
End If
rownum = rownum + 1
Loop


mystr = Left(mystr, Len(mystr) - 2)


Dim x As Long
lastrow = Sheets("OK-Green").Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To lastrow


mydate1 = Cells(x, 2).Value
mydate2 = mydate1


Cells(x, 6).Value = mydate2


datetoday1 = Date
datetoday2 = datetoday1


Cells(x, 7).Value = datetoday2


If mydate2 - datetoday2 = 10 Then
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.createitem(OlMailItem)


Cells(x, 4) = "Send Reminder"
Cells(x, 3).Font.ColorIndex = 2
Cells(x, 3).Font.Size = 16
Cells(x, 3).Font.Bold = True
Cells(x, 3).Value = mydate2 - datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing


Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.createitem(OlMailItem)


MailDest1 = "mindy2017art@gmail.com"
MailDest2 = "
mindy2017art@gmail.com​
"


With OutLookMailItem
  .to = MailDest1
  .bcc = MailDest2
  .Subject = Range("a1").Value & " " & mystr
  .Body = Range("B1").Value
  .display
 End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

 

[wideboydixon]

Firstly, you should use & to concatenate strings. Secondly. if no rows meet your criteria then mystr will be zero characters in length so Left("", -2) will return error 5. Try like this instead:

rownum = 2
Do Until Cells(rownum, 2).Value = ""
    If Cells(rownum, 2).Value <= Date + 14 And Cells(rownum, 4).Value = "Send Reminder" Then ''change made here
        mystr = mystr & ", " & Cells(rownum, 1).Value
    End If
    rownum = rownum + 1
Loop
mystr = Mid(mystr, 3)

WBD

 

[Mindy2017]

wideboydixon!

Thank you...can you also read the code to see if have the macro generating two emails? The first email that is generated doesn't provide me with the locations. However, the second time I run the macro the list is shown in the email.

I would like it to run correctly the first time.

Thank you in advance.