1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro to send mail from out look

Discussion in 'VBA Macros' started by Giriraj Desai, May 11, 2017.

  1. Giriraj Desai

    Giriraj Desai New Member

    Messages:
    21
    Hi team,

    Can you please help me ,I have macro to send out look mails from excel which is working however it is taking only for the 1st line and one more I have problem in the body I want as it is which I have attached in the mail
    like this I have many e mails id 's to send the mail .Could you please help me .

    thanks and regards
    Giriraj

    Attached Files:

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    Perhaps something like this:
    Code (vb):

    Sub SendEm()

        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
       
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        Set Mail_Object = CreateObject("Outlook.Application")
       
        For i = 11 To lr Step 6
            With Mail_Object.CreateItem(o)
                .Subject = Range("C" & i).Value
                .To = Range("B" & i).Value
                .Body = Range("D" & i).Value & vbCrLf & vbCrLf & Range("D" & i + 1) & " " & Range("E" & i + 1) & vbCrLf & Range("D" & i + 2) & " " & Range("E" & i + 2) & vbCrLf & Range("D" & i + 3) & " " & Range("E" & i + 3) & vbCrLf & Range("D" & i + 4) & " " & Range("E" & i + 4)
    '            .Send
               .Display 'disable display and enable send to send automatically
           End With
        Next i
       
        MsgBox "E-mail successfully sent", 64
    '    Application.DisplayAlerts = False
       Set Mail_Object = Nothing
       
    End Sub
     
    Attached

    Hope this helps

    Attached Files:

  3. Giriraj Desai

    Giriraj Desai New Member

    Messages:
    21
    Hi Costa,

    Thank you very much you made my day.

    Regards
    Giriraj
  4. Giriraj Desai

    Giriraj Desai New Member

    Messages:
    21
    Hi,

    Sorry one favour can we add at the end of the mail Signature .

    something like this

    Thanks and regards.
    XXXXXXXX.
  5. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    First of all, you are welcome ;)

    Sure,

    Code (vb):
    Sub SendEm()

        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
     
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        Set Mail_Object = CreateObject("Outlook.Application")
     
        For i = 11 To lr Step 6
            With Mail_Object.CreateItem(o)
                .Subject = Range("C" & i).Value
                .To = Range("B" & i).Value
                .Body = Range("D" & i).Value & vbCrLf & vbCrLf & Range("D" & i + 1) _
                    & " " & Range("E" & i + 1) & vbCrLf & Range("D" & i + 2) & " " _
                    & Range("E" & i + 2) & vbCrLf & Range("D" & i + 3) & " " & Range("E" & i + 3) _
                    & vbCrLf & Range("D" & i + 4) & " " & Range("E" & i + 4) & vbCrLf _
                    & vbCrLf & "Thanks and regards," & vbCrLf & "XXXXXXXX"
        '                .Send
               .Display 'disable display and enable send to send automatically
           End With
        Next i
     
        MsgBox "E-mail successfully sent", 64
    '    Application.DisplayAlerts = False
       Set Mail_Object = Nothing
     
    End Sub
     
    Attached

    Attached Files:

  6. Giriraj Desai

    Giriraj Desai New Member

    Messages:
    21
    Again Thank you very much for your valuable help.
  7. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    You are welcome :)

Share This Page