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

Mindy2017

New Member
Joined
Aug 7, 2018
Messages
7
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!
Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:

Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top