PDA

View Full Version : send email notification from excel to multiple recipients



bcomhari
11-17-2012, 12:10 PM
Dear Team,

Here i am but still i am getting error messge
please see the below code :



Sub Notify()
Dim WS As Worksheet, Rng As Range, c As Range, D As Range, F As Range, p As
Range
Dim OutApp As Object, OutMail As Object
Dim Msg As String, Addr As String, i As Long
Dim NotificationNecessary As Boolean

Set OutApp = CreateObject("Outlook.Application")
Set WS = ThisWorkbook.Sheets("Sheet1")

For Each c In Rng
For Each D In Rng
For Each F In Rng
For Each p In Rng

NotificationNecessary = False
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. "
Msg = Msg & "We would need your inputs to proceed further on processing
of this claim" & "Vendor Claim Details:" & "Document No./ Ref No &
C.Offset(, 2) & "
Msg = Msg & "having Invoice No" & D.Offset(2, 4)
Msg = Msg & "of Vendor Name " & F.Offset(2, 6)
Msg = Msg & "Reason for Holding " & F.Offset(2, 16)

For i = 4 To 13
If WS.Cells(Z.Row, i) = "X" Then
Msg = Msg & " -" & WS.Cells(1, i) & Chr(13)
NotificationNecessary = True
End If
Next

If NotificationNecessary Then
Msg = Msg & Chr(13) & "If the Hold document not resolved within 15
days from hold date the claim will be REJECTED." & Chr(13)
Msg = Msg & Chr(13) & "Thank you,"
Msg = Msg & Chr(13) & "Team"

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next

With OutMail
.To = Q.Offset(, 1)
.CC = ""
.BCC = ""
.Subject = " Your Bills on HOLD"
.Body = Msg
.Send
End With

Set OutMail = Nothing

End If

Next

End Sub
I would like to have a message in the below format :




Dear Sir/Madam,
Kindly provide the following clarification/ input required that we came acrosswhile processing your claim.
We would need your inputs to proceed further on processing of this claim.

Vendor Claim Details: Document No./ Ref No. XXXX [ having Invoice No -NO/LEGALCH/884 ] of Vendor code "XXX " of Vendor Name : "Vendor name

Reason for Holding : Kindly attach the finance head approval for releasing thepayment.

When you are sending any additional document, please attach a print out of thismail and then drop it in
Wividus Box.

We are awaiting your reply to proceed further.

*************************************************************************** **********
If the Hold document not resolved within 15 days from hold date the claim willbe REJECTED

*************************************************************************** **********
For further clarification please feel free to conduct us.

Regards,
Team

Simon Lloyd
11-19-2012, 12:13 AM
What error are you getting?