Can anyone explain how I can transfer the for loop counter x as an argument of sub email_send in application.ontime procedure.
Find attached the code I have written to send email reminder on the basis of different types of drawing.
Dim x As Long
Sub drawings()
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
If Cells(x, "F") = "Type-1" And Cells(x, "H") = "" Then
Cells(x, "H").Value = 1
time1 = Now() + TimeValue("00:02:00")
Application.OnTime time1, "'email_send" & x & "'"
ElseIf Cells(x, "F") = "Type-2" And Cells(x, "H") = "" Then
Cells(x, "H").Value = 1
time2 = Now() + TimeValue("00:04:00")
Application.OnTime time2, "'email_send" & x & "'"
ElseIf Cells(x, "F") = "Type-3" And Cells(x, "H") = "" Then
Cells(x, "H").Value = 1
time3 = Now() + TimeValue("00:08:00")
Application.OnTime time3, "'email_send" & x & "'"
ElseIf Cells(x, "F") = "Type-4" And Cells(x, "H") = "" Then
Cells(x, "H").Value = 1
time4 = Now() + TimeValue("00:10:00")
'time4 = time4 + 5
Application.OnTime time4, "'email_send" & x & "'"
MsgBox time4
End If
MsgBox Cells(x, "A")
Next x
End Sub
Sub email_send(ByVal x As Long)
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
'MsgBox "hello"
.To = Cells(2, 9).Value
.Subject = "Case ID " & Cells(x, "A") & " (" & Cells(x, "B") & ") Deadline Approaching"
.Body = "Please complete your assigned drawing asap."
.Display
.Send
End With
End Sub
I'm new to VBA, so I request you guys to find mistakes in my code and suggest edits.
Edit: Thank you for guiding me through the steps, but the problem is I'm getting an unexpected error when I follow them. I wanted to attach the photo of the error but I can't because I don't have enough points.
When calling a sub you can pass arguments.
Or you can declare x as a public variable :
https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables