VBA Macro to send mail from out look

[Giriraj Desai]

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

 

[PCosta87]

Hi,

Perhaps something like this:

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

 

[Giriraj Desai]

Hi Costa,

Thank you very much you made my day.

Regards
Giriraj

 

[Giriraj Desai]

Hi,

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

something like this

Thanks and regards.
XXXXXXXX.

 

[PCosta87]

First of all, you are welcome

Hi,

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

something like this

Thanks and regards.
XXXXXXXX.

Sure,

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

 

[Giriraj Desai]

Again Thank you very much for your valuable help.

 

[PCosta87]

You are welcome