send email from excel to multiple recipients

**hariexcel1987** · 11-20-2012, 11:49 AM

Dear Holger ,

Wow, i am able to send an e-mail to respective Recipients whom i am marked on column "L",

Here my requirement is that this Hold tracker will be update with only Hold documents, so we no need to give any condition to satisfy and end of the day I should be able to send an E-mail notification to all the recipients stating the reason why their respective bills on Hold and request them to respond within 10 days of Time or else it will get reject from the system , the mail should be something like "

" Dear Sir/Madam,
Kindly provide the following clarification/ input required that we came across while 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 the payment.

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

We are awaiting your reply to proceed further.

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

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

Regards,
Team "

and also help me to provide the code for give more space between the sentences

Can you please look into this and help me out ...

You are helping me a lot , Thanks for your efforts and you have been really superb.

I will be waiting for your positive response

Regards,
Hari

**HaHoBe** · 11-20-2012, 04:42 PM

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