send email from excel to multiple recipients

send email notification from excel to multiple recipients
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 will be REJECTED

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

Regards,
Team

Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.

Hi, hariexcel1987,

Here i am but still i am getting error messge

No doubt: 5 For Loops with only 2 Next, rng not being set, Variable Z being used, but neither dimmed nor filled, same goes for Q, the following variables aren´t filled: D, F, p.

For a startup have a look at this:

Option Explicit

Sub Notify()
Dim WS As Worksheet, rngArea As Range, rngCell As Range
Dim OutApp As Object, OutMail As Object
Dim Msg As String, i As Long
Dim NotificationNecessary As Boolean

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
'  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" & WS.Cells(rngCell.Row, "A") & " " '<--- this is just a guess from my side
  Msg = Msg & "having Invoice No" & WS.Cells(rngCell.Row, "D").Value
  
'!!! need to change code from here on accroding to sample above
'!!! the offset will only deliver empty cells from your example. If data is lined in rows
'!!! there´s no use of going down 2 rows, especially when there is only one line of data
  Msg = Msg & "of Vendor Name " & F.Offset(2, 6)
  Msg = Msg & "Reason for Holding " & F.Offset(2, 16)
  
'Only 1 line of data with no "X" in them, so what stands this code for`?
  For i = 4 To 13
    If WS.Cells(rngCell.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 = 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

The code for Ending Outlook is missing, I would probably use GetObject first with a boolean Variable for not ending it and on an error for doing so use CreateObject since to my knowledge Outlook can only be run in one instance on a computer.

Ciao,
Holger

Hi, Hari ,

the variable F isn´t filled with any information for a range and thus may be the cause of the run-time error:

'!!! need to change code from here on according to sample above
'!!! the offset will only deliver empty cells from your example. If data is lined in rows
'!!! there´s no use of going down 2 rows, especially when there is only one line of data
  Msg = Msg & "of Vendor Name " & F.Offset(2, 6)
  Msg = Msg & "Reason for Holding " & F.Offset(2, 16)

Change the code to read (and check if Column P is the place to put in the second line)

  Msg = Msg & "of Vendor Name " & WS.Cells(rngCell.Row, "F").Value
  Msg = Msg & "Reason for Holding " & WS.Cells(rngCell.Row, "P").Value

On Debugging no error were thrown up which doesn´t mean that the code will run (sorry don´t have Outlook installed on this Computer, only Office 2010HSE).

CIao.
Holger

Hi, Hari,

due to the If checking if notification was necessary an email could only get send if that value was true. Please try this altered code which put the mail into my Outgoing mails:

Sub Notify()
Dim WS As Worksheet, rngArea As Range, rngCell As Range
Dim OutApp As Object, OutMail As Object
Dim Msg As String, i As Long
Dim NotificationNecessary As Boolean

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
'  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" & WS.Cells(rngCell.Row, "A") & " " '<--- this is just a guess from my side
  Msg = Msg & "having Invoice No" & WS.Cells(rngCell.Row, "D").Value
  
'!!! need to change code from here on accroding to sample above
'!!! the offset will only deliver empty cells from your example. If data is lined in rows
'!!! there´s no use of going down 2 rows, especially when there is only one line of data
  Msg = Msg & "of Vendor Name " & WS.Cells(rngCell.Row, "F").Value
  Msg = Msg & "Reason for Holding " & WS.Cells(rngCell.Row, "P").Value
  
'Only 1 line of data with no "X" in them, so what stands this code for`?
  For i = 4 To 13
    If WS.Cells(rngCell.Row, i) = "X" Then
      Msg = Msg & " -" & WS.Cells(1, i) & Chr(13)
      NotificationNecessary = True
    End If
  Next i
  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"
  End If
    
    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
Next rngCell
End Sub

And I think you should work a little bit on extra spaces in the text.

Ciao,
Holger

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

ROn de Bruin has an article about that

Hi, hariexcel1987,

is there any reason why there isn´t much of code in the sample (in fact just two brackets not filled with an code in Sheet "DISC"?

Use an Autofiölter on Column P in "Hold-Data", run the range of visible cells and have a Case Select for the text to be shown in the mail depending on the value from Column P - that´s the basic idea right now so that you will have to adapt the time range for the three macros but not the basic code to deliver the mails. If you prefer you could as well compose three nearly identical macros for each period, or use textfiles to hold the individual passages for the mail. You could as well step through all cells on Data-Hiold and compare P to the values for that period.

Ciao,
Holger

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,

display option is working fine but when I made it to .send all the excel files are hanging and it’s not letting me to close also , so please look into this,

I explained the reason for that in my last post, you don´t indicate that anything has been changed in your file (or at least you didn´t mention that).

I think we can delete filter option because I don’t want it to filter the data , simple I have to send the data if the condition satisfies

Are you taking about 10 sample data or 100.000? What´s wrong with the Autofilter which could be switched back with just one command at the end of the macro but will certainly save a lot of time in conjunction with SpecialCells as maybe only a part of all the data needs to be looked at.

And maybe you are kind enough to inform me about what we need to program concerning the sending of the mails. Do you want to send a mail each time the criteria satisfies?

Ciao,
Holger