Excel how to add in my email body the value of a certain cell.

[Florida1510]

Hi folks quick question.

How do I insert an excel field adding it to the body of my email. In the below example I'm trying to insert cell E3 into the third from the bottom line of code highlighted in Red. It should say APN = 123.567 but it's saying APN = sh.Cells (cell.Row, 4). Any help would be appreciated Thanks in advance.


Sub senEmailsToMultiplePersonsWithMutlpleAttachments()

Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)

'path/file names are entered in the columns F:M in each row
Set rng = sh.Cells(cell.Row, 1).Range("F1:M1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.TO = sh.Cells(cell.Row, 1).Value
.CC = sh.Cells(cell.Row, 2).Value
.Subject = sh.Cells(cell.Row, 3).Value
.htmlbody = "Hi All <br>" _
& "<br>Its been found that the below APN has been inactive in system from more than 6 months. Please confirm if you are or in future will be using this APN, if not it will be moved out of system accordingly.<br>" _
& "<br>APN = sh.Cells (cell.Row, 4)<br>" _
& "<br>Account Name.<br>" _
& "<br>Account ID.<br>" _

 

[cmowla]

That line should be written like this:

& "<br>APN =" & sh.Cells(cell.Row, 4).value & "<br>"

 

[Florida1510]

That line should be written like this:

& "<br>APN =" & sh.Cells(cell.Row, 4).value & "<br>"

cmowla,

Thank you Thank you Thank you I truly appreciate the help. Have a great evening.

 

[Florida1510]

cmowla,

I do have one more question the code runs fine up until the line you assisted me with however when I tried to duplicate what you did for this line:
& "<br>APN =" & sh.Cells(cell.Row, 4).value & "<br>" I'm now getting a Run time error 438 - Object doesn't support this property of method. Any thought on the last three lines on (bolded) on what I'm doing wrong. Thanks in advance.


Sub senEmailsToMultiplePersonsWithMutlpleAttachments()

Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)

'path/file names are entered in the columns G:M in each row
Set rng = sh.Cells(cell.Row, 1).Range("G1:M1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.TO = sh.Cells(cell.Row, 1).Value
.CC = sh.Cells(cell.Row, 2).Value
.Subject = sh.Cells(cell.Row, 3).Value
.htmlbody = "Hi All <br>" _
& "<br>Its been found that the below APN has been inactive in our system from more than 6 months. Please confirm if you are or in future will be using this APN, if not it will be moved out of system accordingly.<br>" _
& "<br>APN =" & sh.Cells(cell.Row, 4).Value & "<br>" _
& "<br>Account Name =" & .sh.Cells(cell.Row, 5).Value & "<br>" _
& "<br>Account ID =" & .sh.Cells(cell.Row, 6).Value & "<br>" _
& Signature

 

[Florida1510]

Disregard I figure it out. Thanks again.