VBA Email to Recipient based on cell value in workbook

[excelisfunusually]

Hello!

I am trying to use a macro to send email to a recipient based on the value in a cell (B21 on Sheet 4). This value is the result of a vlookup formula that changes based on another cell's input. My vba code is below. Any help is greatly appreciated!


Sub Send_email_pm()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim strto As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Please check SharePoint for the latest update to this document"

strto = Worksheets("Sheet4").Range("B21").Value

On Error Resume Next
With OutMail
.To = strto
.Subject = Dir(ThisWorkbook.FullName)
.Body = strbody
.Send

End With

End Sub


Thanks!

 

[excelisfunusually]

I found the solution for this by looking at YouTube. https://www.youtube.com/watch?v=uW8gjf3lB88. I used For & Next.

For i = 21 to 21
With OutMail
.To = Sheet4.Cells(i,2) [2 because the cell is B21 or column 2]. I tested it on myself and received the email.