Results 1 to 2 of 2

Thread: Multiple emails with each row data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple emails with each row data

    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

    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

    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

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple emails with each row data

    You have a list of items to include in the body, but you only have one reference in your sample. I'm assuming you know how to add the rest.
    You didn't say what part wasn't working, but I'm assuming it's the body. Two things I see - the use of vbNewLine and the cell reference.
    Try this instead:

    Code:
        .Body = "Hi," & vbCr & _
            "The below listed invoice is pending for your kind approval." & vbCr & _
            "BU: " & Cells(rng.Row, "B").Value
    The vbCR don't have to be on the line i show them - could be on the next. That's just personal style. Nor do you have to actually start text after a vbCR on a new line.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •