Hi all
Please help me, I have been searching all morning on varous sites to produce a macro which will run once a cell value contains certian text.
My spreadsheet basically tracks letters I send out and identifies if we have not had a response by 28 days and in column "Y" it returns the value "Overdue" if that is the case.
What I want is for my code below (which works) to run when the value in Column Y becomes "Overdue".
My spreadsheet consists of headings from B16 to Y16 and data begins from B17:Y17 and down to B595:Y595. I need Excel to identify any lines which contain the word "Overdue" in column Y, then send the email (run the code below) or if Y is blank, move onto the next line. Presumably someone clever can add some code ontop of my macro below to get this working?
I have formula's after column Y which determine who the reminder should be sent to, and supplies the email address to use in column AF.
Many thanks in anticipation,
Tom
Please help me, I have been searching all morning on varous sites to produce a macro which will run once a cell value contains certian text.
My spreadsheet basically tracks letters I send out and identifies if we have not had a response by 28 days and in column "Y" it returns the value "Overdue" if that is the case.
What I want is for my code below (which works) to run when the value in Column Y becomes "Overdue".
My spreadsheet consists of headings from B16 to Y16 and data begins from B17:Y17 and down to B595:Y595. I need Excel to identify any lines which contain the word "Overdue" in column Y, then send the email (run the code below) or if Y is blank, move onto the next line. Presumably someone clever can add some code ontop of my macro below to get this working?
I have formula's after column Y which determine who the reminder should be sent to, and supplies the email address to use in column AF.
Many thanks in anticipation,
Tom
Code:
Sub SendNotesMail()
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'The current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim Session As Object 'The notes session
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = (Range("AF17"))
MailDoc.Subject = "DP letter for: " & Range("B17") & " " & Range("D17") & " is now overdue"
MailDoc.Body = "Hi " & Range("AE17") & vbNewLine & vbNewLine & "The letter sent for " & Range("B17") & " " & Range("D17") & " on " & Range("K17") & " is now overdue. Please contact client." & vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine & "My name" & vbNewLine & "My job title" & vbNewLine & "My team name" & vbNewLine & "Address line 1" & vbNewLine & "Address line 2" & vbNewLine & "address line 3" & vbNewLine & "address line 4" & vbNewLine & "post code"
MailDoc.SAVEMESSAGEONSEND = SaveIt
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End If
End Sub
