How to pass a variable in the procedure of application on time?

741 views Asked by At

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.

2

There are 2 answers

2
Sriks On

When calling a sub you can pass arguments.

Sub email_send(x as variant) 
'your code here
End sub
x = 3
email_send x

Or you can declare x as a public variable :
https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables

11
Domenic On

First you'll need to change the signature for email_send so that it includes a parameter that accepts a Long...

Sub email_send(ByVal x As Long)

Then you can pass your argument as follows...

Application.OnTime time1, "'email_send " & x & "'"