email vba to grab info from cell to put on subject

[ayrton216]

Hello, I need help! I am trying to get the subject from a cell for example: ("B5") = Daniel
("B6") = Mark


Email


Subject: "Can " Mark " Please be advised that appointment is due"


Depending on expiring date to grab the correct cell and put on subject


This is the code I have so far:






Private Sub Worksheet_Calculate()
'Updateby Extendoffice
Dim Xrg As Range
Set Xrg = Range("M5:M100")
If Range("M5") = "Reminder Sent" Then
Call Mail_small_Text_Outlook
End If


End Sub


Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim Subj As Object
Dim xMailBody As String
Set Subj = Range("B5:B10")
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hello," & vbNewLine & vbNewLine & vbNewLine & _
"There is a Certificate close to expiry date, please click on the link provided for more information." & vbNewLine & vbNewLine & vbNewLine & _
"Best Ragards"
On Error Resume Next
With xOutMail
.To = "hancoa@parliament.uk"
.CC = ""
.BCC = ""
.Subject = "Certificate: " & Range("B5").Value & " is close to expiration date"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

Please can someone help me

 

[kvngrn]

hi,
Can you give us more details?
1/ Do you want to go through all the column B with the name and check then the column M ?
2/ Can it be several time the same name in column B ? if yes do you want to send several emails or only 1 ?