repeating my macro so it sends next line.

[Flemix]

I have done the code and it works only sending the first line.
A is email addresses
B Subject
C body

How can i make it repeat every line and send emails to all addresses in column with its own body in that is in column C.

Sub Email_From_Excel_Basic()

Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

' Now we build the emial.

emailItem.To = Range("A2").Value

emailItem.Subject = Range("B2").Value

emailItem.Body = Range("C2").Value

'Send the Email
emailItem.Send

End Sub

 

[taurean]

You need to add a loop to do this (I have not tested the code for working, I am assuming your posted code is working for you):

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object
    Dim emailItem As Object
    Dim rng As Range
   
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
   
    ' Now we build the email.
   
    For Each rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        emailItem.To = rng.Value
       
        emailItem.Subject = rng.Offset(0, 1).Value
       
        emailItem.Body = rng.Offset(0, 1).Value
       
        'Send the Email
        emailItem.Send
    Next rng
   
End Sub

 

[Flemix]

You need to add a loop to do this (I have not tested the code for working, I am assuming your posted code is working for you):

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object
    Dim emailItem As Object
    Dim rng As Range
  
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
  
    ' Now we build the email.
  
    For Each rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        emailItem.To = rng.Value
      
        emailItem.Subject = rng.Offset(0, 1).Value
      
        emailItem.Body = rng.Offset(0, 1).Value
      
        'Send the Email
        emailItem.Send
    Next rng
  
End Sub

thank you for the reply. It is coming up with error Rune time Error 1698430710 Method 'To' of object'_MailItem' failed. It did send the first email on the line 2 but did not send the next one cape up with this error

 

[Flemix]

You need to add a loop to do this (I have not tested the code for working, I am assuming your posted code is working for you):

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object
    Dim emailItem As Object
    Dim rng As Range
  
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
  
    ' Now we build the email.
  
    For Each rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        emailItem.To = rng.Value
      
        emailItem.Subject = rng.Offset(0, 1).Value
      
        emailItem.Body = rng.Offset(0, 1).Value
      
        'Send the Email
        emailItem.Send
    Next rng
  
End Sub

When i debug the error it stops at emailItem.To = rng.Value

 

[Flemix]

It is coming up with error Rune time Error 1698430710 Method 'To' of object'_MailItem' failed. It did send the first email on the line 2 but did not send the next one cape up with this error.
When i debug the error it stops at emailItem.To = rng.Value

It is also sending the first email but subject is now also the body.

I am attaching

the excel sheet
column A is the addresses that must be sent to
B is the subject for each email
C is the body for each email. Every one will be different.

 

[taurean]

I think I missed a step. EmailItem needs to be created in each loop. See edited code.

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object
    Dim emailItem As Object
    Dim rng As Range
   
    Set emailApplication = CreateObject("Outlook.Application")
    
   
    ' Now we build the email.
   
    For Each rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Set emailItem = emailApplication.CreateItem(0)

        emailItem.To = rng.Value
       
        emailItem.Subject = rng.Offset(0, 1).Value
       
        emailItem.Body = rng.Offset(0, 1).Value
       
        'Send the Email
        emailItem.Send
    Next rng
   
End Sub

 

[Flemix]

please help.

if you look at the excel sheet.
1st email must be to
A mark@protea with subject Protea account and body C with its own body as it is on sheet.

but the body shows the subject.

It is sending it to outlook now but the Body is wrong.
Can you help

 

[taurean]

Sorry

This line is wrong:

emailItem.Body = rng.Offset(0, 1).Value

It should be:

emailItem.Body = rng.Offset(0, 2).Value

 

[Flemix]

Sorry

This line is wrong:

emailItem.Body = rng.Offset(0, 1).Value

It should be:

emailItem.Body = rng.Offset(0, 2).Value

You are a star. One more thing. When i run the macro it opens the outlook app and then it comes up with "A program is trying to send an e-mail message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date'
And i have to click on Yes to send every email. Is there a workaround this so it go through automatically

 

[taurean]

You are a star. One more thing. When i run the macro it opens the outlook app and then it comes up with "A program is trying to send an e-mail message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date'
And i have to click on Yes to send every email. Is there a workaround this so it go through automatically

Great. I am not sure I can answer your second part regarding popup.