To all who's been looking to attach files to an email and want to include into the body of the email the filenames of all attached files, I managed to solve my own question as I could not locate anywhere on the web on how to do it.
Contract = Sheets("Prefs").Range("Y5").Value
Set Sh = Sheets("Quick Search")
Set OutApp = CreateObject("Outlook.Application")
Set rng = Sh.Range("AQ4:AQ33")
Set Rn = Workbooks("Speedmail.xls").Worksheets("Local").Range("W1")
If Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.createitem(0)
With OutMail
.Subject = Contract
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
With Workbooks("Drawing Register.xls").Worksheets("Local")
.Range("A" & FileCell.Offset(0, -20) + 2 & ":T" & FileCell.Offset(0, -20) + 2).Copy Workbooks("Speedmail.xls").Worksheets("Local").Range("A" & Rn)
End With
strbody = strbody & vbNewLine & FileCell.Offset(0, 3)
Rn = Rn + 1
End If
End If
Next FileCell
Workbooks("Speedmail.xls").Close SaveChanges:=True
.Attachments.Add ThisWorkbook.Path & "\" & "Speedmail.xls"
.body = "Hi," & vbNewLine & vbNewLine & "Add your email greeting here." & vbNewLine & strbody & vbNewLine & vbNewLine & "Kind regards" & vbNewLine & vbNewLine & Application.UserName
.display
End With
Set OutMail = Nothing
End If
Set OutApp = Nothing
The items in bold are the items modified between both posts of this query.
Enjoy
Bookmarks