Hi, Hari,
please have a look at the following code. Both Chr(13) and vbCrLf may be used to force a new line in the string. According to the pattern laid out here you should be able to start making changes to the output on your own if you keep an original of the code as a safety and use either ' or REM to convert the codelines to comment which wouldnīt be executed:
Sub Notify()
Dim WS As Worksheet, rngArea As Range, rngCell As Range
Dim OutApp As Object, OutMail As Object
Dim Msg As String
Set OutApp = CreateObject("Outlook.Application")
Set WS = ThisWorkbook.Sheets("Sheet1")
Set rngArea = WS.Range(WS.Cells(2, 1), WS.Cells(WS.Cells(Rows.Count, "A").End(xlUp).Row, 1))
For Each rngCell In rngArea
If rngCell.Offset(0, 1).Value = "Hold" Then
Msg = "Dear Sir/Madam" & Chr(13) & Chr(13)
Msg = Msg & "Kindly provide the following clarification/ input required that we came across while processing your claim." & vbCrLf
Msg = Msg & "We would need your inputs to proceed further on processing of this claim." & vbCrLf & vbCrLf & _
"Vendor Claim Details: Document No./ Ref No. " & WS.Cells(rngCell.Row, "A") '<--- this is just a guess from my side
Msg = Msg & " [ having Invoice No. " & WS.Cells(rngCell.Row, "D").Value & " ] "
Msg = Msg & "of Vendor code " & WS.Cells(rngCell.Row, "E").Value & " of Vendor Name: " & WS.Cells(rngCell.Row, "F").Value & vbCrLf & vbCrLf
Msg = Msg & "Reason for Holding " & WS.Cells(rngCell.Row, "P").Value & ", Kindly attach the finance head approval for releasing the payment."
Msg = Msg & Chr(13) & Chr(13) & "*************************************************************************** **********"
Msg = Msg & Chr(13) & "If the Hold document not resolved within 15 days from hold date the claim will be REJECTED."
Msg = Msg & Chr(13) & "*************************************************************************** **********"
Msg = Msg & Chr(13) & Chr(13) & "For further clarification please feel free to conduct us"
Msg = Msg & Chr(13) & Chr(13) & "Regards, " & vbCrLf & "Team"
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = WS.Cells(rngCell.Row, "L").Value
.CC = ""
.BCC = ""
.Subject = " Your Bills on HOLD"
.Body = Msg
.Send
End With
Set OutMail = Nothing
End If
Next rngCell
End Sub
Ciao,
Holger
Bookmarks