Help with vbNewLine for Email to Loop through values in Column A

[amitcohen]

Hi
I'm trying to create code for the Email Body that will contain more the just one row.
I'm using the & vbNewLine & but when it come to the place where I need to refer to the cell in column A, VBA editor gives me error.


Can you spot the problem?

Sub Mail_With_Loop()
Dim olApp As Outlook.Application
Dim olMail As MailItem

For i = 2 To 50 Step 1
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = Range("H1").Value
.Body = "Hello" & vbNewLine & [COLOR=Red]Range(A" & i &").Value[/COLOR] & vbNewLine & "Text line" & vbNewLine & "Another text line" & vbNewLine & "And another one here"

.Display
End With
Next i
Set olMail = Nothing

Set olApp = Nothing

End Sub

Thanks,
Amit

 

[Jon von der Heyden]

Shalom Amit

It should be:

Range("A" & i)

 

[amitcohen]

Hi Jon
Shalom
Thank you for the quick replay.

Well, it seems to be fine now, as VBA parse the value..
However its Yellow highlighting the line code:

[COLOR=Red]Set olMail = olApp.CreateItem(olMailItem)[/COLOR]

Am (more) confuse now..

Amit

 

[Michael M]

Hi Amit
Change these

Dim olApp As Outlook.Application
Dim olMail As MailItem

TO

Dim olApp As Object
Dim olMail As Object

 

[amitcohen]

Hi Michael,
Thanks, but changing the lines didn't help.


Have any idea how to go around it?

 

[Jon von der Heyden]

If it's late-binding you're after...

    Dim olApp As Object
    Dim olMail As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)

 

[Michael M]

Ok, try

Sub Mail_With_Loop()
Dim olApp As Object
Dim olMail As Object

For i = 2 To 50 Step 1
        Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
.To = Range("H1").Value
.Body = "Hello" & vbNewLine & Range("A" & i).Value & Chr(13) & "Text line" & Chr(13) & "Another text line" & Chr(13) & "And another one here"

.Display
End With
Next i
Set olMail = Nothing

Set olApp = Nothing

End Sub

 

[amitcohen]

Hi
This one is perfect!!

Problem solved
Thank you.

Amit

 

[Michael M]

Excellent....glad it worked.
Thanks for the feedback !