send email from excel to multiple recipients

Hi, Hari,

as you changed the look of the sheet I kindly ask you to provide me with the updated code you use to send mail from that sheet. And please confirm that this is the final look of the sheet as there needs a column to be added (date of mail sent or day pending when mail was sent) as well as a formula being used for Column P days pending.

so I would like to request you to provide me code for to add if condition

The way I would go for a solution was laid out and there were several scenarios. As you want to adjust 2 macros on your own the If-Statements I feel that are needed are a check for the pending (Iīd apply an Autofilter to Column P) and for the date the last mail was sent.

Ciao,
Holger

Hi, Hari,

right now Iīm facing the fact that the macro that worked on the old file doesnīt create a mail on my system after I adapted it for the changes. Could you please check on your system if itīs working?

Sub Notify_121204()
Dim WS As Worksheet, rngArea As Range, rngCell As Range
Dim OutApp As Object, OutMail As Object
Dim Msg As String

Set WS = ThisWorkbook.Sheets("Hold-Data")
With WS
  If .AutoFilterMode Then
    .Range("A1:R1").AutoFilter
    .Range("A1:R1").AutoFilter Field:=17, Criteria1:="0-15days"
  Else
    .Range("A1:R1").AutoFilter Field:=17, Criteria1:="0-15days"
  End If
End With
On Error Resume Next
Set rngArea = WS.Range(WS.Cells(2, 17), WS.Cells(WS.Cells(Rows.Count, 17).End(xlUp).Row, 17)).SpecialCells(xlCellTypeVisible)
If rngArea Is Nothing Then Exit Sub
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
For Each rngCell In rngArea
  If Len(WS.Cells(rngCell.Row, "T").Value) = 0 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, "C").Value & " ] "
    Msg = Msg & "of Vendor code " & WS.Cells(rngCell.Row, "D").Value & "  of Vendor Name: " & WS.Cells(rngCell.Row, "E").Value & vbCrLf & vbCrLf
    Msg = Msg & "Reason for Holding " & WS.Cells(rngCell.Row, "N").Value & vbCrLf & "Kindly attach the finance head approval for releasing the payment."
    
    Msg = Msg & Chr(13) & Chr(13) & "*************************************************************************** **********"
    Msg = Msg & Chr(13) & "If the Hold document is 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, "J").Value
      .CC = WS.Cells(rngCell.Row, "R").Value
      .BCC = ""
      .Subject = "Your Bills on HOLD"
      .Body = Msg
      .Send
    End With
    Set OutMail = Nothing
    WS.Cells(rngCell.Row, "T").Value = WS.Cells(rngCell.Row, "P").Value
  End If
Next rngCell
End Sub

Ciao,
Holger

Hi, Hari,

Originally Posted by hariexcel1987

It’s filtering the data but I am unable to send an E-mail to the recipient , actually it’s hanging could you please look into this

Originally Posted by HaHoBe

right now Iīm facing the fact that the macro that worked on the old file doesnīt create a mail on my system after I adapted it for the changes.

No, I canīt as I am on vacation right now and only know that an update on Windows has taken place but donīt know whatīs been changed there (and currently am on a very slow net next to beating bytes into the net).

Maybe I will be able to check this on the start of next week when Iīm back at work as I need a system with Outlook (and the laptop Iīm working on doesnīt do the job nor does it feed me with adequate information from the net in a reasonable time).

Ciao,
Holger

Hi, Hari,

the reason for Outlook not getting the mail might be the contents of Column R (have to fill manually). If you change the command .Send to .Display the mail will be displayed in Outlook. Or better you change the contents in Column R to valid e-mail addresses and try to run the macro again.

Ciao,
Holger