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
[TABLE="width: 945"]
<colgroup><col span="6"><col><col span="7"></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]BU[/TD]
[TD]Amount[/TD]
[TD]Currency[/TD]
[TD]Supplier Name[/TD]
[TD]Creation Date[/TD]
[TD]Invoice Date[/TD]
[TD]Status[/TD]
[TD]Email ID[/TD]
[TD]Pending From[/TD]
[TD]Lines[/TD]
[TD]Project[/TD]
[TD]Type[/TD]
[TD]PO #[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]AU[/TD]
[TD]434.50[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]8/26/19[/TD]
[TD]8/19/19[/TD]
[TD]INITIATED[/TD]
[TD]x@y.com[/TD]
[TD]9/4/19[/TD]
[TD]8[/TD]
[TD]XYZ[/TD]
[TD]PO[/TD]
[TD]PO NUMBER[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]MY[/TD]
[TD]187.00[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]5/9/19[/TD]
[TD]3/11/19[/TD]
[TD]INITIATED[/TD]
[TD]a@b.com[/TD]
[TD]9/10/19[/TD]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]Non-PO[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]IN[/TD]
[TD]187.00[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]5/9/19[/TD]
[TD]3/11/19[/TD]
[TD]INITIATED[/TD]
[TD]c@d.com[/TD]
[TD]9/10/19[/TD]
[TD]2[/TD]
[TD]DEF
[/TD]
[TD]Non-PO[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 945"]
<colgroup><col span="6"><col><col span="7"></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]BU[/TD]
[TD]Amount[/TD]
[TD]Currency[/TD]
[TD]Supplier Name[/TD]
[TD]Creation Date[/TD]
[TD]Invoice Date[/TD]
[TD]Status[/TD]
[TD]Email ID[/TD]
[TD]Pending From[/TD]
[TD]Lines[/TD]
[TD]Project[/TD]
[TD]Type[/TD]
[TD]PO #[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]AU[/TD]
[TD]434.50[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]8/26/19[/TD]
[TD]8/19/19[/TD]
[TD]INITIATED[/TD]
[TD]x@y.com[/TD]
[TD]9/4/19[/TD]
[TD]8[/TD]
[TD]XYZ[/TD]
[TD]PO[/TD]
[TD]PO NUMBER[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]MY[/TD]
[TD]187.00[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]5/9/19[/TD]
[TD]3/11/19[/TD]
[TD]INITIATED[/TD]
[TD]a@b.com[/TD]
[TD]9/10/19[/TD]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]Non-PO[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]IN[/TD]
[TD]187.00[/TD]
[TD]AUD[/TD]
[TD]Supplier Name[/TD]
[TD]5/9/19[/TD]
[TD]3/11/19[/TD]
[TD]INITIATED[/TD]
[TD]c@d.com[/TD]
[TD]9/10/19[/TD]
[TD]2[/TD]
[TD]DEF
[/TD]
[TD]Non-PO[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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