CODEWITHSUNDEEP
excelvbaoutlook
Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

Outlook mail with excel: Issue with body text and variable

I'm having trouble with getting my .body text to work.

I can get it to create the item in outlook. The "To:" and "Subject" is correct, but the body text is not.
I can either get it to include from "Hello" to "following service lines:" or include "For y = 2 to LR1" to Next y. I want it to include both. how do i connect the two parts? - Solved!

The If match function should match sht1.range("B" & y) with sht3.range("C" & i). Within the "y" loop, y goes from 2 to 49, while variable i should be constant until loop is done. Then it should move to next i and do it over again. This is not working since the body text includes all the rows i variable y. - Solved!

Sub Test()
Dim OutApp As Object
Dim OutMail As Object

Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Sht3 As Worksheet

Dim i As Long
Dim y As Long
Dim x As Long

Dim LR1 As Long
Dim LR2 As Long

Set Sht1 = ThisWorkbook.Sheets("Sheet1")
Set Sht2 = ThisWorkbook.Sheets("Sheet2")
Set Sht3 = ThisWorkbook.Sheets("Sheet3")

LR1 = Sht1.Cells(Sht1.Rows.Count, "B").End(xlUp).Row
LR2 = Sht3.Cells(Sht3.Rows.Count, "C").End(xlUp).Row

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo Cleanup

For i = 2 To LR2

If Not IsEmpty(Sht3.Range("C" & i)) Then

Sht3.Activate
Sht3.Range("C" & i).Select

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next

        With OutMail
            .To = Sht3.Range("C" & i).Offset(ColumnOffset:=1)
            .Subject = "OE input sheet " & Sht3.Range("C" & i) & ": Service Delivered = NO"
            .body = "Hello " & ActiveCell.Offset(ColumnOffset:=-1).Value & "." _
                & vbNewLine & vbNewLine & _
                "We have noticed, that you have indicated Service Deliverd = NO, while the service line still contains a value. " _
                & vbNewLine & vbNewLine & _
                "We are referring to the following service lines:" _

                For y = 2 To LR1
                Sht1.Activate
                Sht1.Range("B" & y).Select

                    If Sht1.Range("B" & y) = Sht3.Range("C" & i) Then
                        .body = .body & vbNewLine & ActiveCell.Offset(ColumnOffset:=1).Value
                    End If

                Next y
            .Save
        End With

        On Error GoTo 0
        Set OutMail = Nothing
    End If

Next i

Cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Thanks in advance

Upvotes: 0

Views: 731

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

The .body property of the mailItem does not append additional infomation into the body, it entirely REPLACES the information in the body.

So when you set it the first time with the "Hello" portion, the email body contains this information. When you set it again in your loop, it now only contains the information you have set it to in the loop.

Update the line in your loop to this:

    .body = .body & vbNewLine & ActiveCell.Offset(ColumnOffset:=1).Value

and it will append the new information to the email body.

Upvotes: 1

Related Questions