Dear All,
I have this excel data where am trying to send individual email along with the data from each row associated to that email id. Below is the format
<colgroup><col span="6"><col><col span="7"></colgroup><tbody>
</tbody>
The body of the email should be
Hi,
The below listed invoice is pending for your kind approval
BU:
Number:
Invoice Date:
Amount:
Supplier Name:
Currency:
Pending From:
Regards,
Keerthi
I tried the below code but not working for some reason. Can you please help
Sub Send_Email()
Dim rng As Range
For Each rng In Range("I1:I5")
Call mymacro(rng)
Next rng
End Sub
Private Sub mymacro(rng As Range)
Dim OutApp As Object
Dim OutMail As Object
Dim Cell As Range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = rng.Value
.CC = ""
.BCC = ""
.Subject = "URGENT - Approval Pending"
.Body = "Hi," & vbNewLine = "The below listed invoice is pending for your kind approval." & _
vbNewLine = "BU: " & Cells(Cell.Row, "B").Value
.Display
'.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks
I have this excel data where am trying to send individual email along with the data from each row associated to that email id. Below is the format
Number | BU | Amount | Currency | Supplier Name | Creation Date | Invoice Date | Status | Email ID | Pending From | Lines | Project | Type | PO # |
123 | AU | 434.50 | AUD | Supplier Name | 8/26/19 | 8/19/19 | INITIATED | x@y.com | 9/4/19 | 8 | XYZ | PO | PO NUMBER |
456 | MY | 187.00 | AUD | Supplier Name | 5/9/19 | 3/11/19 | INITIATED | a@b.com | 9/10/19 | 2 | ABC | Non-PO | |
789 | IN | 187.00 | AUD | Supplier Name | 5/9/19 | 3/11/19 | INITIATED | c@d.com | 9/10/19 | 2 | DEF | Non-PO |
<colgroup><col span="6"><col><col span="7"></colgroup><tbody>
</tbody>
The body of the email should be
Hi,
The below listed invoice is pending for your kind approval
BU:
Number:
Invoice Date:
Amount:
Supplier Name:
Currency:
Pending From:
Regards,
Keerthi
I tried the below code but not working for some reason. Can you please help
Sub Send_Email()
Dim rng As Range
For Each rng In Range("I1:I5")
Call mymacro(rng)
Next rng
End Sub
Private Sub mymacro(rng As Range)
Dim OutApp As Object
Dim OutMail As Object
Dim Cell As Range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = rng.Value
.CC = ""
.BCC = ""
.Subject = "URGENT - Approval Pending"
.Body = "Hi," & vbNewLine = "The below listed invoice is pending for your kind approval." & _
vbNewLine = "BU: " & Cells(Cell.Row, "B").Value
.Display
'.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks