• 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...

  • 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

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
 

Attachments

  • Book1.xlsm
    19.6 KB · Views: 4
Hi,

Perhaps something like this:
Code:
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
 

Attachments

  • Book1.xlsm
    20.8 KB · Views: 7
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,

Code:
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
 

Attachments

  • Book1.xlsm
    21.1 KB · Views: 8
Back
Top