Sending email with attachment (formed from embedded excel) using VBA/Macro

[gautam82grover]

Hi,

I have been a regular visitor to this forum and had found solution to most of my questions. But the one mentioned, i could never find.
Here is my problem.

I need to use excel code, to mail certain recipients with an attachment. This attachment is an excel embedded in one of the worksheets (Lets say worksheet 7)

The code that i am using currently:

Sub Mail_Macro()
Dim counter As Integer
Dim LastMailRow As Integer
Dim Str As String

LastMailRow = Sheets("TMList").UsedRange.Rows.Count
counter = 0


For i = 3 To LastMailRow
If Sheets("TMList").Cells(i, 5).Value = Sheets("TMList").Cells(1, 2).Value And Sheets("TMList").Cells(i, 6).Value = "" Then
ESubject = Sheets("TMList").Cells(i, 3)
SendTo = Sheets("TMList").Cells(i, 2)
Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
Str = Sheets("TMList").Cells(2, 11) & " " & Sheets("TMList").Cells(2, 8)
With Itm
.Subject = ESubject
.To = SendTo
.CC = "gautam82grover@hcl.com"
.Body = Str
'.Attachments = ???? 'I dont know how to do this
.send
End With
Set App = Nothing
Set Itm = Nothing
Sheets("TMList").Cells(i, 6).Value = "Mail sent"
counter = counter + 1
End If
Next
Sheets("TMList").Cells(3, 11).Value = counter
Set survey = Nothing
End Sub

 

[Norie]

You need the full path and name of the file you want to attach.

 

[JP2112]

In addition to what Norie suggested, you also need to write that line like this:

.Attachments.Add "filename.xls"